mysql中的sql_mode模式实例详解

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

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

?

1 2 3 4 5 6

mysql> select @@sql_mode; + ----------------------------------------------------------------+ | @@sql_mode                           | + ----------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | + ----------------------------------------------------------------+

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

 

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

 

1 ANSI模式

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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)

2 STRICT_TRANS_TABLES模式

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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)

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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)

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

?

1 2 3 4 5 6

mysql> set @@sql_mode=TRADITIONAL; Query OK, 0 rows affected (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,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 1 row in set (0.00 sec)

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

最后：

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

vi /etc/my.cnf

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

?

1	sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

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

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

改模式之前的操作

?

1 2 3 4 5 6 7 8 9 10

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

此时的sql_mode:

?

1 2 3 4 5 6 7

mysql> select @@sql_mode; + ----------------------------------------------------------------+ | @@sql_mode                           | + ----------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | + ----------------------------------------------------------------+ 1 row in set (0.00 sec)

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

?

1 2 3 4 5 6 7 8 9 10 11

mysql> set global sql_mode= 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY' ; Query OK, 0 rows affected (0.00 sec) mysql> select @@sql_mode; + ----------------------------------------------------------------+ | @@sql_mode                           | + ----------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | + ----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> exit Bye

再次进入

?

1 2 3 4 5 6 7

mysql> select @@sql_mode; + -----------------------------------------------------------------------------------+ | @@sql_mode | + -----------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | + -----------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

mysql> select * from employee group by post;  //只能查看post ERROR 1055 (42000): 't1.employee.id' isn 't in GROUP BY mysql> select post from employee group by post; +-----------------------------------------+ | post                  | +-----------------------------------------+ | operation                | | sale                  | | teacher                 | | 老男孩驻沙河办事处外交大使       | +-----------------------------------------+ 4 rows in set (0.00 sec) mysql> select id,post from employee group by post; ERROR 1055 (42000): ' t1.employee.id ' isn' t in GROUP BY mysql> select name ,post from employee group by post, name ; //根据 group by 后面的选择查看 + ------------+-----------------------------------------+ | name | post | + ------------+-----------------------------------------+ | 张野 | operation | | 程咬金 | operation | | 程咬铁 | operation | | 程咬铜 | operation | | 程咬银 | operation | | 丁丁 | sale | | 丫丫 | sale | | 星星 | sale | | 格格 | sale | | 歪歪 | sale | | alex | teacher | | jingliyang | teacher | | jinxin | teacher | | liwenzhou | teacher | | wupeiqi | teacher | | xiaomage | teacher | | yuanhao | teacher | | egon | 老男孩驻沙河办事处外交大使 | + ------------+-----------------------------------------+ 18 rows in set (0.00 sec)

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

原文链接：https://www.cnblogs.com/mmyy-blog/p/9626326.html