Chinaunix首页 | 论坛 | 博客

对你说sunshasha.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

sss0213

  • 博客访问： 2310051
  • 博文数量： 473
  • 博客积分： 12252
  • 博客等级： 上将
  • 技术积分： 4307
  • 用 户 组： 普通用户
  • 注册时间： 2007-10-12 10:02
文章分类

全部博文（473）

文章存档

2012年（8）

2011年（63）

2010年（73）

2009年（231）

2008年（98）

我的朋友
最近访客
推荐博文
相关博文
详解mysql的sql_mode模式

分类： Mysql/postgreSQL

2012-06-21 13:42:46

1. sql_mode模式
  mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法，数据校验等！我们可以通过以下方式查看当前数据库使用的sql_mode：
Java代码 复制代码 收藏代码
  1. mysql> select @@sql_mode;   
  2. +----------------------------------------------------------------+   
  3. | @@sql_mode                                                     |   
  4. +----------------------------------------------------------------+   
  5. | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |   
  6. +----------------------------------------------------------------+  
mysql> select @@sql_mode; +----------------------------------------------------------------+ | @@sql_mode | +----------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +----------------------------------------------------------------+
或者通过查看系统变量方式:
Java代码 复制代码 收藏代码
  1. mysql> show variables like 'sql_mode%'\G;   
  2. *************************** 1. row ***************************   
  3. Variable_name: sql_mode   
  4.         Value: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  
mysql> show variables like 'sql_mode%'\G; *************************** 1. row *************************** Variable_name: sql_mode Value: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

mysql5.0以上版本支持三种sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。
ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。
STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。

1.2 ANSI模式
Java代码 复制代码 收藏代码
  1. mysql> set @@sql_mode=ANSI;   
  2. Query OK, 0 rows affected (0.00 sec)   
  3.   
  4. mysql> create table test(name varchar(4), pass varchar(4));   
  5. Query OK, 0 rows affected (0.03 sec)   
  6.   
  7. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');   
  8. Query OK, 2 rows affected, 2 warnings (0.02 sec)   
  9. Records: 2  Duplicates: 0  Warnings: 2  
  10.   
  11. mysql> show warnings;   
  12. +---------+------+-------------------------------------------+   
  13. | Level   | Code | Message                                   |   
  14. +---------+------+-------------------------------------------+   
  15. | Warning | 1265 | Data truncated for column 'name' at row 1 |   
  16. | Warning | 1265 | Data truncated for column 'pass' at row 1 |   
  17. +---------+------+-------------------------------------------+   
  18. 2 rows in set (0.00 sec)   
  19.   
  20. mysql> select * from test;   
  21. +------+------+   
  22. | name | pass |   
  23. +------+------+   
  24. | aaaa | aaaa |   
  25. | bbbb | bbbb |   
  26. +------+------+   
  27. 2 rows in set (0.00 sec)  
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)
我们可以看到，在ANSI模式下，当我们插入数据时，未满足列长度要求时，数据同样会插入成功，但是对超出列长度的字段进行截断，同时报告warning警告。

1.3 STRICT_TRANS_TABLES模式
Java代码 复制代码 收藏代码
  1. mysql> set @@sql_mode=STRICT_TRANS_TABLES;   
  2. Query OK, 0 rows affected (0.00 sec)   
  3.   
  4. mysql> create table test(name varchar(4), pass varchar(4));   
  5. Query OK, 0 rows affected (0.02 sec)   
  6.   
  7. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');   
  8. ERROR 1406 (22001): Data too long for column 'name' at row 1  
  9.   
  10. mysql> show errors;   
  11. +-------+------+------------------------------------------+   
  12. | Level | Code | Message                                  |   
  13. +-------+------+------------------------------------------+   
  14. | Error | 1406 | Data too long for column 'name' at row 1 |   
  15. +-------+------+------------------------------------------+   
  16. 1 row in set (0.00 sec)   
  17.   
  18. mysql> select * from test;   
  19. Empty set (0.00 sec)  
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)
我们可以看到，在STRICT_TRANS_TABLES模式下，当我们插入数据时，mysql会严格的进行数据的校验，当发现插入列值未满足要求，直接报告error错误，保证了错误数据无法插入到数据库中。

1.3 TRADITIONAL模式
Java代码 复制代码 收藏代码
  1. mysql> set @@sql_mode=TRADITIONAL;   
  2. Query OK, 0 rows affected (0.00 sec)   
  3.   
  4. mysql> create table test(name varchar(4), pass varchar(4));   
  5. Query OK, 0 rows affected (0.02 sec)   
  6.   
  7. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');   
  8. ERROR 1406 (22001): Data too long for column 'name' at row 1  
  9.   
  10. mysql> show errors;   
  11. +-------+------+------------------------------------------+   
  12. | Level | Code | Message                                  |   
  13. +-------+------+------------------------------------------+   
  14. | Error | 1406 | Data too long for column 'name' at row 1 |   
  15. +-------+------+------------------------------------------+   
  16. 1 row in set (0.00 sec)   
  17.   
  18. mysql> select * from test;   
  19. Empty set (0.00 sec)  
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)
TRADITIONAL模式与STRICT_TRANS_TABLES模式执行的结果，在这种情况下一致。
Java代码 复制代码 收藏代码
  1. mysql> select @@sql_mode\G;   
  2. *************************** 1. row ***************************   
  3. @@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,E   
  4. RROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER   
  5. 1 row in set (0.00 sec)  
mysql> select @@sql_mode\G; *************************** 1. row *************************** @@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,E RROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER 1 row in set (0.00 sec)
看一下TRADITIONAL模式，我们发现在TRADITIONAL模式下，对所有的事务存储引擎，非事务存储引擎检查，日期类型中的月和日部分不能包含0，不能有0这样的日期(0000-00-00),数据不能除0，禁止grant自动创建新用户等一些校验。

注意：我们这里设置的sql_mode都是session级别的。另外，可以直接修改my.ini文件，找到sql_mode，然后设置新的模式即可！
阅读(10666) | 评论(0) | 转发(1) |
0

上一篇：mysql查询区分大小写

下一篇：没有了

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6