SQL Mode
Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. For example, you can also use modes to make sure you're SQL behaves the same or similarly on both Oracle and MySQL, or to make sure MySQL is behaving the same way on different operating systems.
The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY (Links to an external site.), STRICT_TRANS_TABLES (Links to an external site.), NO_ZERO_IN_DATE (Links to an external site.),NO_ZERO_DATE (Links to an external site.), ERROR_FOR_DIVISION_BY_ZERO (Links to an external site.), NO_AUTO_CREATE_USER (Links to an external site.), and NO_ENGINE_SUBSTITUTION (Links to an external site.).
If you're curious, you can click on the MODE names which are links to MySQL documentation pages describing each mode. However, you won't be responsible for knowing specifics about those modes so only look if you're curious or have some other need.
The MySQL server administrator can change/set the mode globally in the server configuration. This is necessary if you have a specific requirement and want all users of the database to have the same non-default behavior.
However, if you want to enforce a specific mode just for your session, you can do that as follows:
SET GLOBAL sql_mode = 'modes'; # modes= comma delimited set of desired modes/behaviors
I've provided a link to the MySQL Modes in the Resources section of this module. You may need to refer to it but reading it is optional.
It's important to note that any procedure or funciton will execute with the sql_mode that was defined when the function was created.
In genral you won't need to alter the sql_mode, but there may be occasions when you want your SQL to act more "ANSI" like, perhaps for compatibility with Oracle or SQL Server.
Here's an explanation of strict mode (Links to an external site.) form the MySQL Documentation:
Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT (Links to an external site.) or UPDATE (Links to an external site.). A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For aNULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE (Links to an external site.).
MySQL服务器能够工作在不同的SQL模式下，并能够针
对不同的客户端以不同的方式应用这些模式。这样，应用程序就能对服务器进行量身定制以满足自己的需求。这类模式定义了MySQL应支持的SQL语法，以及
应该在数据上执行何种确认检查。这样，就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。
可以使用“--sql-mode="modes"”选项，
通过启动mysqld模式来设置默认的SQL模式。从MySQL4.1开始，也能在启动之后，使用SET [ SESSION | GLOBAL ]
sql_mode="modes" 语句，通过设置sql_mode变量来改变模式。
通常在Linux下安装玩MySQL后，默认mysql-
mode值是空的，在这种情形下MySQL指定的是一种不严格的检查，例如日期字段可以插入“ 0000-00-00
00:00:00”这样的值，还有如果要插入的字段长度超过列定义的长度，那么MySQL不会终止操作，而是会自动截断后面的字符继续插入，如下例：
我们会发现插入的字符被自动截断了，但是如果我们本意希望如果长度超过限制就报错，那么我们就可以设置sql_mode为STRICT_TRANS_TABLES，如下：
这样我们再执行同样的操作，MySQL就会告诉我们插入的值太长，操作被终止，如下：
| 参数 | 含义及示例 |
| ALLOW_INVALID_DATES |
允许无效的日期插入到数据库(无效的日期插入时候会有warning，但仍能插入，但是日期会变成0000-00-00这种无效的数据) >set session sql_mode='ALLOW_INVALID_DATES'; >insert into new(`d`) VALUES('201'); Query OK, 1 row affected, 1 warning (0.01sec) 提示插入成功并有一个警告 该模式仅对DATE和DATETIME类型有效，而对TIMESTAMP无效，因为TIMESTAMP总是要求一个合法的输入。 |
| ANSI_QUOTES |
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符。 >set session sql_mode='ANSI_QUOTES'; >insert into teachersvalues(22,'lee',12,"M"); ERROR1054 (42S22): Unknown column 'M' in 'field list' 用了双引号的话，直接报错了 |
| ERROR_FOR_DIVISION_BY_ZERO |
在INSERT或UPDATE过程中， 该模式未启用的话，如果数据被零除，最终结果插入NULL并且不提示warnings 该模式启用的话，如果数据被零除，最终结果插入NULL并且会提示warnings >set session sql_mode='STRICT_TRANS_TABLES'; >insert into t1 VALUES(5/0); Query OK, 1 row affected (0.00 sec) 插入时候没有警告，结果是个NULL
>set session sql_mode='ERROR_FOR_DIVISION_BY_ZERO'; >insert into t1 VALUES(6/0); QueryOK, 1 row affected, 1 warning (0.01 sec) 插入时候有警告，结果是个NULL |
| HIGH_NOT_PRECEDENCE |
设置优先级关系【主要为了兼容老版本的MySQL】 > SET sql_mode = ''; l> SELECT NOT 1 BETWEEN -5 AND 5; 结果为0 > SET sql_mode = 'HIGH_NOT_PRECEDENCE'; > SELECT NOT 1 BETWEEN -5 AND 5; 结果为1 |
| IGNORE_SPACE |
函数名和括号“(”之间有空格。除了增加一些烦恼，这个选项好像没有任何好处，要访问保存为关键字的数据库、表或列名，用户必须引用该选项。 例如某个表中有user这一列，而MySQL数据库中又有user这个函数， user会被解释为函数，如果想要选择user这一列，则需要引用。 >createtable `partition` (i int(3)); 对于表名或者其它与保留关键字的地方或者内置函数同名称的话，，必须加反引号，不然MySQL报错。 |
| NO_AUTO_CREATE_USER |
禁止GRANT创建密码为空的用户。 > SET sql_mode = 'NO_AUTO_CREATE_USER'; > grant all on *.* to 'lirl'@'%'; ERROR 1133 (42000): Can't find any matchingrow in the user table >grant all on *.* to 'lirl2'@'%'identified by '123456'; QueryOK, 0 rows affected (0.00 sec) |
| NO_AUTO_VALUE_ON_ZERO |
该选项影响列为自增长的插入。 在默认设置下，插入0或NULL代表生成下一个自增长值。 如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。 |
| NO_BACKSLASH_ESCAPES |
反斜杠“\”作为普通字符而非转义符 >set sql_mode=''; >select '\\'; +---+ | \ | +---+ | \ | +---+ >SET sql_mode = 'NO_BACKSLASH_ESCAPES'; >select '\\'; +----+ | \\ | +----+ | \\| +----+ |
| NO_DIR_IN_CREATE |
在创建表时忽视所有INDEX DIRECTORY和DATA DIRECTORY的选项。 > set sql_mode=''; > create table t1 (`age` char(3)) DATADIRECTORY="/tmp" INDEX DIRECTORY="/tmp"; 说明：此时查看/tmp下面会有相关的ibd表空间文件
> set sql_mode='NO_DIR_IN_CREATE'; > create table t2 (`age` char(3)) DATADIRECTORY="/tmp" INDEX DIRECTORY="/tmp"; 说明：此时查看 /tmp下面找不到相关的ibd表空间文件，表文件还是原来的datadir下面。 |
|
NO_ENGINE_SUBSTITUTION 不使用默认引擎替代 |
设置这个参数后，如果需要的存储引擎被禁用或未编译，那么抛出错误。 不设置这个参数的话，找不到指定的存储引擎的话，会用默认的存储引擎替代，但会有warning提示。 >set sql_mode=''; > create table t4 (`age` int(3))ENGINE=Inff; > show create table t4\G *************************** 1. row*************************** Table: t4 Create Table: CREATE TABLE `t4` ( `age` int(3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 上面我们随便用了个不存在的存储引擎建表时候也没报错，只要有个warning提示，系统自带用默认的存储引擎替代了。
> set sql_mode='NO_ENGINE_SUBSTITUTION'; > create table t4 (`age` int(3))ENGINE=Inff; ERROR 1286 (42000): Unknown storage engine'Inff' 显式设置了sql_mode='NO_ENGINE_SUBSTITUTION'后，建表时候指定一个不存在的存储引擎后，就直接报错了。 |
| NO_FIELD_OPTIONS | |
| NO_KEY_OPTIONS | |
| NO_TABLE_OPTIONS | |
| NO_UNSIGNED_SUBTRACTION | |
| NO_ZERO_DATE |
启用的话，表示允许0000-00-00这种日期的插入，但是会有warnings 不启用的话，表示允许0000-00-00这种日期的插入，不会有warnings
如果启动NO_ZERO_DATE模时,还启用了strict模式，则0000-00-00这种不允许插入, 显式使用IGNORE参数的话，才能插入成功。 > setsql_mode='NO_ZERO_DATE,.STRICT_TRANS_TABLES'; > insert into tb2 VALUES('0000-00-00'); 这样插入会报错的 >INSERT IGNORE INTO tb1 VALUES('0000-00-00'); # 加了IGNORE后才能插入成功 |
| NO_ZERO_IN_DATE |
> set sql_mode='NO_ZERO_IN_DATE'; 允许插2015-00-02、2015-03-00 这种非法日期，但是实际上插入的是0000-00-00。插入2013-02-01这种正常的数据是没问题的。 > set sql_mode=''; >insert into tb2 VALUES('2010-00-02'); 允许这种格式的日期插入 |
| ONLY_FULL_GROUP_BY | 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。 |
| PAD_CHAR_TO_FULL_LENGTH | |
| PIPES_AS_CONCAT | 将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似。 |
| REAL_AS_FLOAT |
实数是浮点数的同义词。【默认情况下，MySQL把实数当double同义词】 double 8字节 float 4字节 real8字节 |
| STRICT_ALL_TABLES | 严格模式 |
| STRICT_TRANS_TABLES | 严格模式 |
