mysql中的sql_mode模式实例详解

本文实例讲述了mysql中的sql_mode模式。分享给大家供大家参考，具体如下：

mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法，数据校验等！我们可以通过以下方式查看当前数据库使用的sql_mode：

1. mysql> select @@sql_mode;
2. +----------------------------------------------------------------+
3. | @@sql_mode |
4. +----------------------------------------------------------------+
5. | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
6. +----------------------------------------------------------------+

mysql5.0以上版本支持三种sql_mode模式

1 ANSI模式

在ANSI模式下，当我们插入数据时，未满足列长度要求时，数据同样会插入成功，但是对超出列长度的字段进行截断，同时报告warning警告。

1. mysql> set @@sql_mode=ANSI;
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> create table test(name varchar(4), pass varchar(4));
4. Query OK, 0 rows affected (0.03 sec)
5. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
6. Query OK, 2 rows affected, 2 warnings (0.02 sec)
7. Records: 2 Duplicates: 0 Warnings: 2
8. mysql> show warnings;
9. +---------+------+-------------------------------------------+
10. | Level | Code | Message |
11. +---------+------+-------------------------------------------+
12. | Warning | 1265 | Data truncated for column 'name' at row 1 |
13. | Warning | 1265 | Data truncated for column 'pass' at row 1 |
14. +---------+------+-------------------------------------------+
15. 2 rows in set (0.00 sec)
16. mysql> select * from test;
17. +------+------+
18. | name | pass |
19. +------+------+
20. | aaaa | aaaa |
21. | bbbb | bbbb |
22. +------+------+
23. 2 rows in set (0.00 sec)
24.  

2 STRICT_TRANS_TABLES模式

在STRICT_TRANS_TABLES模式下，当我们插入数据时，mysql会严格的进行数据的校验，当发现插入列值未满足要求，直接报告error错误，保证了错误数据无法插入到数据库中。

1. mysql> set @@sql_mode=STRICT_TRANS_TABLES;
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> create table test(name varchar(4), pass varchar(4));
4. Query OK, 0 rows affected (0.02 sec)
5. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
6. ERROR 1406 (22001): Data too long for column 'name' at row 1
7. mysql> show errors;
8. +-------+------+------------------------------------------+
9. | Level | Code | Message |
10. +-------+------+------------------------------------------+
11. | Error | 1406 | Data too long for column 'name' at row 1 |
12. +-------+------+------------------------------------------+
13. 1 row in set (0.00 sec)
14. mysql> select * from test;
15. Empty set (0.00 sec)
16.  

3 TRADITIONAL模式，初看结果是不是一样

1. mysql> set @@sql_mode=TRADITIONAL;
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> create table test(name varchar(4), pass varchar(4));
4. Query OK, 0 rows affected (0.02 sec)
5. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
6. ERROR 1406 (22001): Data too long for column 'name' at row 1
7. mysql> show errors;
8. +-------+------+------------------------------------------+
9. | Level | Code | Message |
10. +-------+------+------------------------------------------+
11. | Error | 1406 | Data too long for column 'name' at row 1 |
12. +-------+------+------------------------------------------+
13. 1 row in set (0.00 sec)
14. mysql> select * from test;
15. Empty set (0.00 sec)
16.  

但是，可以看看设置后的情况

1. mysql> set @@sql_mode=TRADITIONAL;
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> select @@sql_mode\G
4. *************************** 1. row ***************************
5. @@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
6. 1 row in set (0.00 sec)
7.  

在TRADITIONAL模式下，对所有的事务存储引擎，非事务存储引擎检查，日期类型中的月和日部分不能包含0，不能有0这样的日期(0000-00-00),数据不能除0，禁止grant自动创建新用户等一些校验。

最后：

set @@只是在sessions级别设置的，要想所有的都生效，还是要设置配置文件

vi /etc/my.cnf

在[mysqld]下面添加如下列：

1. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
2.  

#NO_ENGINE_SUBSTITUTION对于不存在的引擎就报错，不加的话，指定不支持的引擎时指定默认的innodb

另外：sql_mode还有一个配置ONLY_FULL_GROUP_BY，这个表示采用group by帅选数据的时候只能查看新组内信息

改模式之前的操作

1. mysql> select * from employee group by post;
2. +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
3. | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
4. +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
5. | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
6. | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
7. | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
8. | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
9. +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
10. 4 rows in set (0.00 sec)
11.  

此时的sql_mode:

1. mysql> select @@sql_mode;
2. +----------------------------------------------------------------+
3. | @@sql_mode |
4. +----------------------------------------------------------------+
5. | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
6. +----------------------------------------------------------------+
7. 1 row in set (0.00 sec)
8.  

修改一下，退出再进入才会生效

1. mysql> set globalsql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> select @@sql_mode;
4. +----------------------------------------------------------------+
5. | @@sql_mode |
6. +----------------------------------------------------------------+
7. | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
8. +----------------------------------------------------------------+
9. 1 row in set (0.00 sec)
10. mysql> exit
11. Bye
12.  

再次进入

1. mysql> select @@sql_mode;
2. +-----------------------------------------------------------------------------------+
3. | @@sql_mode |
4. +-----------------------------------------------------------------------------------+
5. |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
6. +-----------------------------------------------------------------------------------+
7. 1 row in set (0.00 sec)
8.  

下面查看修改后的查看结果

1. mysql> select * from employee group by post; //只能查看post
2. ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
3. mysql> select post from employee group by post;
4. +-----------------------------------------+
5. | post |
6. +-----------------------------------------+
7. | operation |
8. | sale |
9. | teacher |
10. | 老男孩驻沙河办事处外交大使 |
11. +-----------------------------------------+
12. 4 rows in set (0.00 sec)
13. mysql> select id,post from employee group by post;
14. ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
15. mysql> select name,post from employee group by post,name; //根据group by 后面的选择查看
16. +------------+-----------------------------------------+
17. | name | post |
18. +------------+-----------------------------------------+
19. | 张野 | operation |
20. | 程咬金 | operation |
21. | 程咬铁 | operation |
22. | 程咬铜 | operation |
23. | 程咬银 | operation |
24. | 丁丁 | sale |
25. | 丫丫 | sale |
26. | 星星 | sale |
27. | 格格 | sale |
28. | 歪歪 | sale |
29. | alex | teacher |
30. | jingliyang | teacher |
31. | jinxin | teacher |
32. | liwenzhou | teacher |
33. | wupeiqi | teacher |
34. | xiaomage | teacher |
35. | yuanhao | teacher |
36. | egon | 老男孩驻沙河办事处外交大使 |
37. +------------+-----------------------------------------+
38. 18 rows in set (0.00 sec)
39.  

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。