MySQL sql_mode应该如何指定
sql_mode是个很容易被忽视的变量，在5.6之前默认为空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境一般将这个值设置为严格模式。
sql_mode分类及各值定义
分类 | sql_mode | 说明 | 启用 | 不启用 |
|---|---|---|---|---|
数据检查类 | NO_ENGINE_SUBSTITUTION | 指定ENGINE时，需要的存储引擎被禁用或不存在，该如何处理 | 直接报错 | Warning转成默认存储引擎 |
STRICT_TRANS_TABLES | INSERT、UPDATE出现少值或无效值该如何处理 | 直接报错 | Warning超限类型值被截断非空约束允许空 | |
NO_ZERO_DATE | 在严格模式，认为日期'0000-00-00'非法 | - | - | |
NO_ZERO_IN_DATE | - | - | ||
ERROR_FOR_DIVISION_BY_ZERO | 在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误 | - | - | |
SQL语法支持类 | ONLY_FULL_GROUP_BY | 对于GROUP BY聚合操作，如果在SELECT中的列、HAVING或者ORDER BY子句的列，没有在GROUP BY中出现，那么这个SQL是不合法的 | 直接报错 | 允许 |
ANSI_QUOTES | 启用ANSI_QUOTES 后，不能用双引号来引用字符串，因为它被解释为识别符，作用与 ` 一样 | 直接报错 | 允许 | |
PIPES_AS_CONCAT | 将||视为字符串的连接操作符而非 或 运算符 | - | - | |
NO_TABLE_OPTIONS | 使用SHOW CREATE TABLE时不会输出MySQL特有的语法部分，如 ENGINE。 | - | - | |
NO_AUTO_CREATE_USER | GRANT语法不能创建用户，除非还指定了密码。 | 直接报错 | 允许 |
两种组合模式：
ANSI | REAL_AS_FLOATPIPES_AS_CONCATANSI_QUOTESIGNORE_SPACEANSI | 宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。 |
|---|---|---|
TRADITIONAL | STRICT_TRANS_TABLESSTRICT_ALL_TABLESNO_ZERO_IN_DATENO_ZERO_DATEERROR_FOR_DIVISION_BY_ZEROTRADITIONALNO_AUTO_CREATE_USERNO_ENGINE_SUBSTITUTION | 严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。日期类型中的月和日部分不能包含0，不能有0这样的日期(0000-00-00)，数据不能除0，禁止grant自动创建新用户等一些校验。 |
一、测试sql_mode=''
(root@localhost) [(none)]>select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+1. 测试创建不存在的表引擎
(root@localhost) [test]>CREATE TABLE `test_4` (
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(20) DEFAULT NULL,
-> `addr` varchar(10) DEFAULT NULL,
-> `sex` varchar(2) DEFAULT NULL
-> ) ENGINE=ssssss DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.00 sec)可以创建成功，但是有warning，不存在ssssss存储引擎，采用默认InnoDB引擎。
(root@localhost) [test]>show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1286 | Unknown storage engine 'ssssss' |
| Warning | 1266 | Using storage engine InnoDB for table 'test_4' |
+---------+------+------------------------------------------------+查看表结构，存储引擎为innodb引擎。
(root@localhost) [test]>show create table test_4\G
*************************** 1. row ***************************
Table: test_4
Create Table: CREATE TABLE `test_4` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`addr` varchar(10) DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)2. 测试insert值超过指定字符类型长度
(root@localhost) [test]>insert into test_4(id,name,addr,sex) values(1,'aa','aa','aaaaaaaaaa');
Query OK, 1 row affected, 1 warning (0.00 sec)可以插入，但是有warning。
(root@localhost) [test]>show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+查看数据，超长字段值被截断。
(root@localhost) [test]>select * from test_4;
+------+------+------+------+
| id | name | addr | sex |
+------+------+------+------+
| 1 | aa | aa | aa |
+------+------+------+------+
1 row in set (0.00 sec)二、测试sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
1. 建表，不存在ssssss存储引擎，直接报错
(root@localhost) [test]>CREATE TABLE `test_5` (
-> `id` int(11) DEFAULT NULL,
-> `name` varchar(20) DEFAULT NULL,
-> `addr` varchar(10) NOT NULL,
-> `sex` varchar(2) DEFAULT NULL
-> ) ENGINE=ssssss DEFAULT CHARSET=utf8;
ERROR 1286 (42000): Unknown storage engine 'ssssss'2.测试insert值超过字符类型长度，直接报错
(root@localhost) [test]>insert into test_4(id,name,addr,sex) values(2,'aa','aa','cccccccc');
ERROR 1406 (22001): Data too long for column 'sex' at row 13. 测试插入非空字段不带值，直接报错
(root@localhost) [test] >insert into test_4(id) values(6);
ERROR 1364 (HY000): Field 'addr' doesn't have a default value三、测试sql_mode='ONLY_FULL_GROUP_BY'
(root@localhost) [test] >set @@sql_mode=ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test] >select @@sql_mode;
+--------------------+
| @@sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
(root@localhost) [test] >select email from test1 group by name;
ERROR 1055 (42000): 'test.test1.email' isn't in GROUP BY对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中。
四、测试sql_mode='NO_AUTO_CREATE_USER'
(root@localhost) [(none)] >set sql_mode=NO_AUTO_CREATE_USER;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)] >select @@sql_mode;
+---------------------+
| @@sql_mode |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.00 sec)
(root@localhost) [(none)] >grant select on test.* to 'test_user'@'%';
ERROR 1133 (42000): Can't find any matching row in the user table
(root@localhost) [(none)] >grant select on test.* to 'test_user'@'%' identified by "123456";
Query OK, 0 rows affected (0.00 sec)grant...on操作不带密码的话会直接报错，带密码则可以创建。
五、总结
sql_mode设置哪种类型需要根据业务使用场景来定：
比如insert时，超过字段长度，业务设计不允许截断插入，而是直接失败，那么就需要指定STRICT_TRANS_TABLES模式。而有些业务则是相反，允许截断插入，则不需要指定STRICT_TRANS_TABLES模式。
一般使用模式：
sql_mode='NO_ENGINE_SUBSTITUTION'
或
sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
或
sql_mode='TRADITIONAL'