MySQL SQL模式介绍

﹏ヽ暗。殇╰゛Y 2024-04-18 01:58 3阅读 0赞

MySQL服务器能够在运行在不同的SQL模式下，并且能在不同客户端下设置不同的SQL模式，可以通过sql\_mode系统参数来设定SQL模式。  
 SQL模式会影响MySQL支持的SQL语法和数据的验证。  
  
MariaDB \[test\]> select @@sql\_mode;  
\+--------------------------------------------+  
| @@sql\_mode                                 |  
\+--------------------------------------------+  
| NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION |  
\+--------------------------------------------+  
1 row in set (0.08 sec)  
  
 NO\_AUTO\_CREATE\_USER  
 防止通过GRANT语句创建没有密码的数据库账户。在通过GRANT创建用户时，必须在IDENTIFIED BY后指定非空的密码。建议使用CREATE USER语句创建数据库账户，尽量避免使用GRANT语句来创建数据库账户。  
  
 NO\_ENGINE\_SUBSTITUTION  
 当执行CREATE TABLE 或 ALTER TABLE语句，指定了一个不支持或没有编译的存储引擎时，控制如何自动替换默认的存储引擎。  
 当NO\_ENGINE\_SUBSTITUTION参数没有启用时，对于CREATE TABLE语句，如果指定的存储引擎不可用，会使用默认的存储，且会产生一个警告。对于ALTER TABLE语句，会产生一个警告，且表的存储引擎转化失败。  
 当NO\_ENGINE\_SUBSTITUTION参数启用时，如果指定的存储引擎不可用，会发生报错，且表不会创建或更改成功。  
  
**--最重要的SQL模式**  
  
 ANSI  
 这个模式改变SQL的语法和行为以更接近标准的SQL。  
  
 STRICT\_TRANS\_TABLES  
 如果值不能插入到事务表中，则退出这条语句；对于非事务表，如果插入或更新的值发生在一行的SQL或多行语句的第一行中，则退出语句。  
  
 TRADITIONAL  
 当插入一个错误的值到一列中时，MySQL会报错而不是警告。  
  
  
**--SQL模式的结合**  
  
 有一些特殊的SQL模式是一些SQL结合在一起的缩写  
  
 例如，ANSI是REAL\_AS\_FLOAT, PIPES\_AS\_CONCAT, ANSI\_QUOTES, IGNORE\_SPACE,ONLY\_FULL\_GROUP\_BY（MySQL 5.7.5）模式的结合；  
 DB2是PIPES\_AS\_CONCAT, ANSI\_QUOTES, IGNORE\_SPACE, NO\_KEY\_OPTIONS, NO\_TABLE\_OPTIONS, NO\_FIELD\_OPTIONS模式的结合。  
  
**--严格SQL模式**  
  
 严格模式控制MySQL如何处理数据改变SQL语句（例如INSERT或UPDATE）中无效或丢失的值。插入或更新的值无效有许多原因，例如，有可能因为字段的数据类型，或值超过了字段的最大范围值。对于一个非空字段，当要插入的一个值为空，且没有默认值，这是值丢失。严格模式也会影响DDL语句，例如CREATE TABLE。  
  
 如果严格模式没有生效，对于无效或丢失的值，MySQL会插入调整的值。在严格模式下，可以通过INSERT IGNORE或UPDATE IGNORE语句来跳过错误。  
  
 在严格模式下，对于类似SELECT这样不改变数据的语句，无效的值会产生一个警告，而不是错误。在严格模式下，对于插入或更新超过字段最大长度的值，会产生一个错误；而在非严格模式下，会产生一个警告，并将截断后的值插入或更新到表中。  
  
 严格模式不会影响是否外键验证。可以通过foreign\_key\_checks参数来验证外键。  
  
 MariaDB \[test\]> show variables like '%foreign%';  
 \+--------------------+-------+  
 | Variable\_name      | Value |  
 \+--------------------+-------+  
 | foreign\_key\_checks | ON    |  
 \+--------------------+-------+  
 1 row in set (0.00 sec)  
  
  
 \--示例①  
 严格SQL模式  
  
MariaDB \[test\]> select @@sql\_mode;  
\+--------------------------------------------+  
| @@sql\_mode                                 |  
\+--------------------------------------------+  
| NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION |  
\+--------------------------------------------+  
1 row in set (0.08 sec)  
  
 MariaDB \[test\]> create table t (name varchar(20), email varchar(40));  
 Query OK, 0 rows affected (0.21 sec)  
  
 MariaDB \[test\]> insert into t values('1234567890000000000000000000','fire beijing @ hotmail.com');  
 Query OK, 1 row affected, 1 warning (0.00 sec)  
  
 MariaDB \[test\]> show warnings;  
 \+---------+------+-------------------------------------------+  
 | Level   | Code | Message                                   |  
 \+---------+------+-------------------------------------------+  
 | Warning | 1265 | Data truncated for column 'name' at row 1 |  
 \+---------+------+-------------------------------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> select \* from t;  
 \+----------------------+----------------------------+  
 | name                 | email                      |  
 \+----------------------+----------------------------+  
 | 12345678900000000000 | fire beijing @ hotmail.com |  
 \+----------------------+----------------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> set session sql\_mode='STRICT\_TRANS\_TABLES';  
 Query OK, 0 rows affected (0.00 sec)  
  
 MariaDB \[test\]> select @@sql\_mode;  
 \+---------------------+  
 | @@sql\_mode          |  
 \+---------------------+  
 | STRICT\_TRANS\_TABLES |  
 \+---------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> insert into t values('1234567890000000000000000000','fire beijing @ hotmail.com');  
 ERROR 1406 (22001): Data too long for column 'name' at row 1  
  
  
 \--示例②  
 ANSI模式和传统模式的区别  
  
 MariaDB \[test\]> set session sql\_mode='ANSI';  
 Query OK, 0 rows affected (0.00 sec)  
  
 MariaDB \[test\]> select @@sql\_mode;  
 \+-------------------------------------------------------------+  
 | @@sql\_mode                                                  |  
 \+-------------------------------------------------------------+  
 | REAL\_AS\_FLOAT,PIPES\_AS\_CONCAT,ANSI\_QUOTES,IGNORE\_SPACE,ANSI |  
 \+-------------------------------------------------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> drop table t;  
 Query OK, 0 rows affected (0.13 sec)  
  
 MariaDB \[test\]> create table t(d datetime);  
 Query OK, 0 rows affected (0.21 sec)  
  
 MariaDB \[test\]> insert into t values('2007-04-31');  
 Query OK, 1 row affected, 1 warning (0.01 sec)  
  
 MariaDB \[test\]> select \* from t;  
 \+---------------------+  
 | d                   |  
 \+---------------------+  
 | 0000-00-00 00:00:00 |  
 \+---------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> set session sql\_mode='TRADITIONAL';  
 Query OK, 0 rows affected (0.00 sec)  
  
 MariaDB \[test\]> select @@sql\_mode;  
 \+------------------------------------------------------------------------------------------------------------------------------------------------------+  
 | @@sql\_mode                                                                                                                                           |  
 \+------------------------------------------------------------------------------------------------------------------------------------------------------+  
 | STRICT\_TRANS\_TABLES,STRICT\_ALL\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,TRADITIONAL,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION |  
 \+------------------------------------------------------------------------------------------------------------------------------------------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> insert into t values('2007-04-31');  
 ERROR 1292 (22007): Incorrect datetime value: '2007-04-31' for column 'd' at row 1  
  
 \--示例③  
 ANSI模式和传统模式的对比  
  
 MariaDB \[test\]> set sql\_mode='ANSI';  
 Query OK, 0 rows affected (0.00 sec)  
  
 MariaDB \[test\]> drop table t;  
 Query OK, 0 rows affected (0.11 sec)  
  
 MariaDB \[test\]> create table t(i int);  
 Query OK, 0 rows affected (0.45 sec)  
  
 MariaDB \[test\]> insert into t values(9%0);  
 Query OK, 1 row affected (0.08 sec)  
  
 MariaDB \[test\]> select \* from t;  
 \+------+  
 | i    |  
 \+------+  
 | NULL |  
 \+------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> set session sql\_mode='TRADITIONAL';  
 Query OK, 0 rows affected (0.00 sec)  
  
 MariaDB \[test\]> insert into t values(9%0);  
 ERROR 1365 (22012): Division by 0  
  
 \--示例④  
 使反斜线称为普通字符  
  
 MariaDB \[test\]> set sql\_mode='ansi';  
 Query OK, 0 rows affected (0.00 sec)  
  
 MariaDB \[test\]> select @@global.sql\_mode;  
 \+--------------------------------------------+  
 | @@global.sql\_mode                          |  
 \+--------------------------------------------+  
 | NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION |  
 \+--------------------------------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> select @@session.sql\_mode;  
 \+-------------------------------------------------------------+  
 | @@session.sql\_mode                                          |  
 \+-------------------------------------------------------------+  
 | REAL\_AS\_FLOAT,PIPES\_AS\_CONCAT,ANSI\_QUOTES,IGNORE\_SPACE,ANSI |  
 \+-------------------------------------------------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> select @@sql\_mode;  
 \+-------------------------------------------------------------+  
 | @@sql\_mode                                                  |  
 \+-------------------------------------------------------------+  
 | REAL\_AS\_FLOAT,PIPES\_AS\_CONCAT,ANSI\_QUOTES,IGNORE\_SPACE,ANSI |  
 \+-------------------------------------------------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> drop table t;  
 Query OK, 0 rows affected (0.14 sec)  
  
 MariaDB \[test\]> create table t (context varchar(20));  
 Query OK, 0 rows affected (0.17 sec)  
  
 MariaDB \[test\]> insert into t values('\\beijing');  
 Query OK, 1 row affected (0.07 sec)  
  
 MariaDB \[test\]> select \* from t;  
 \+---------+  
 | context |  
 \+---------+  
 |eijing |  
 \+---------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> insert into t values('\\\\beijing');  
 Query OK, 1 row affected (0.11 sec)  
  
 MariaDB \[test\]> select \* from t;  
 \+----------+  
 | context  |  
 \+----------+  
 |eijing  |  
 | \\beijing |  
 \+----------+  
 2 rows in set (0.00 sec)  
  
 MariaDB \[test\]> set sql\_mode='REAL\_AS\_FLOAT,PIPES\_AS\_CONCAT,ANSI\_QUOTES,IGNORE\_SPACE,ANSI,NO\_BACKSLASH\_ESCAPES';  
 Query OK, 0 rows affected (0.00 sec)  
  
 MariaDB \[test\]> insert into t values('\\\\beijing');  
 Query OK, 1 row affected (0.00 sec)  
  
 MariaDB \[test\]> select \* from t;  
 \+-----------+  
 | context   |  
 \+-----------+  
 |eijing   |  
 | \\beijing  |  
 | \\\\beijing |  
 \+-----------+  
 3 rows in set (0.00 sec)  
  
 \--示例⑤  
 ANSI模式包含了PIPES\_AS\_CONCAT模式，会将||视为连接操作符  
  
 MariaDB \[test\]> set sql\_mode='ansi';  
 Query OK, 0 rows affected (0.00 sec)  
  
 MariaDB \[test\]> select @@session.sql\_mode;  
 \+-------------------------------------------------------------+  
 | @@session.sql\_mode                                          |  
 \+-------------------------------------------------------------+  
 | REAL\_AS\_FLOAT,PIPES\_AS\_CONCAT,ANSI\_QUOTES,IGNORE\_SPACE,ANSI |  
 \+-------------------------------------------------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> select 'beijing'||'2008';  
 \+-------------------+  
 | 'beijing'||'2008' |  
 \+-------------------+  
 | beijing2008       |  
 \+-------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> set sql\_mode='NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION';  
 Query OK, 0 rows affected (0.00 sec)  
  
 MariaDB \[test\]> select 'beijing'||'2008';  
 \+-------------------+  
 | 'beijing'||'2008' |  
 \+-------------------+  
 |                 1 |  
 \+-------------------+  
 1 row in set, 2 warnings (0.02 sec)  
  
 MariaDB \[test\]> show warnings;  
 \+---------+------+----------------------------------------------+  
 | Level   | Code | Message                                      |  
 \+---------+------+----------------------------------------------+  
 | Warning | 1292 | Truncated incorrect INTEGER value: 'beijing' |  
 | Warning | 1292 | Truncated incorrect DOUBLE value: 'beijing'  |  
 \+---------+------+----------------------------------------------+  
 2 rows in set (0.00 sec)  
  
 \--示例⑥  
 在将MySQL数据库迁移到其他数据时，可以设置sql\_mode为NO\_TABLE\_OPTIONS模式，这样可以去掉engine关键字  
  
 MariaDB \[test\]> show create table t;  
 \+-------+----------------------------------------------------------------------------------------------+  
 | Table | Create Table                                                                                 |  
 \+-------+----------------------------------------------------------------------------------------------+  
 | t     | CREATE TABLE \`t\` (  
   \`context\` varchar(20) DEFAULT NULL  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |  
 \+-------+----------------------------------------------------------------------------------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> set session sql\_mode='NO\_TABLE\_OPTIONS';  
 Query OK, 0 rows affected (0.00 sec)  
  
 MariaDB \[test\]> show create table t;  
 \+-------+-----------------------------------------------------------+  
 | Table | Create Table                                              |  
 \+-------+-----------------------------------------------------------+  
 | t     | CREATE TABLE \`t\` (  
   \`context\` varchar(20) DEFAULT NULL  
 ) |  
 \+-------+-----------------------------------------------------------+  
 1 row in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2121424/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2121424/