mysql之sql_mode

mysql之sql_mode

功臣不能全身而退,嬴政何颜立于天下! ------- 秦始皇

1. mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法，数据校验等！我们可以通过以下方式查看当前数据库使用的sql_mode：
   SELECT @@global.sql_mode;
   SELECT @@sql_mode;
2. mysql5.0以上版本支持三种sql_mode模式

模式	介绍
ANSI模式	宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
TRADITIONAL模式	严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。
STRICT_TRANS_TABLES模式	严格模式，进行数据的严格校验，错误数据不能插入，报error错误。

3. ANSI模式 ：

```php
mysql> set @@sql_mode=ANSI;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> create table test(name varchar(4), pass varchar(4));  
Query OK, 0 rows affected (0.03 sec)  
  
mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');  
Query OK, 2 rows affected, 2 warnings (0.02 sec)  
Records: 2  Duplicates: 0  Warnings: 2  
  
mysql> show warnings;  
+---------+------+-------------------------------------------+  
| Level   | Code | Message                                   |  
+---------+------+-------------------------------------------+  
| Warning | 1265 | Data truncated for column 'name' at row 1 |  
| Warning | 1265 | Data truncated for column 'pass' at row 1 |  
+---------+------+-------------------------------------------+  
2 rows in set (0.00 sec)  
  
mysql> select * from test;  
+------+------+  
| name | pass |  
+------+------+  
| aaaa | aaaa |  
| bbbb | bbbb |  
+------+------+  
2 rows in set (0.00 sec)

4. STRICT_TRANS_TABLES模式
	

```puppet
mysql> set @@sql_mode=STRICT_TRANS_TABLES;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> create table test(name varchar(4), pass varchar(4));  
Query OK, 0 rows affected (0.02 sec)  
  
mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');  
ERROR 1406 (22001): Data too long for column 'name' at row 1  
  
mysql> show errors;  
+-------+------+------------------------------------------+  
| Level | Code | Message                                  |  
+-------+------+------------------------------------------+  
| Error | 1406 | Data too long for column 'name' at row 1 |  
+-------+------+------------------------------------------+  
1 row in set (0.00 sec)  
  
mysql> select * from test;  
Empty set (0.00 sec)

5. TRADITIONAL模式
   ·

mysql> set @@sql_mode=TRADITIONAL;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> create table test(name varchar(4), pass varchar(4));  
Query OK, 0 rows affected (0.02 sec)  
  
mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');  
ERROR 1406 (22001): Data too long for column 'name' at row 1  
  
mysql> show errors;  
+-------+------+------------------------------------------+  
| Level | Code | Message                                  |  
+-------+------+------------------------------------------+  
| Error | 1406 | Data too long for column 'name' at row 1 |  
+-------+------+------------------------------------------+  
1 row in set (0.00 sec)  
  
mysql> select * from test;  
Empty set (0.00 sec)

6. sql_mode常用值

值	介绍
ONLY_FULL_GROUP_BY	对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO	该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
STRICT_TRANS_TABLES	在该模式下，如果一个值不能插入到一个事务中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE	在严格模式下，不允许日期和月份为零
NO_ZERO_DATE	设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
ERROR_FOR_DIVISION_BY_ZERO	在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时Mysql返回NULL
NO_AUTO_CREATE_USER	禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
PIPES_AS_CONCAT	将"
ANSI_QUOTES	启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

7. 具体的命令使用
   SELECT version();
   SELECT @@sql_mode;
   SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,’’));
   SET @@global.sql_mode = ‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
   SELECT @@global.sql_mode;
   SET sql_mode = (SELECT REPLACE(@@global.sql_mode,‘ONLY_FULL_GROUP_BY’,’’))