mysql中的sql_mode模式实例详解

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

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

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警告。

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错误，保证了错误数据无法插入到数据库中。

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模式，初看结果是不是一样

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)

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

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]下面添加如下列：

sql_mode=no_engine_substitution,strict_trans_tables

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

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

改模式之前的操作

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:

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                           |
+----------------------------------------------------------------+
| strict_trans_tables,no_auto_create_user,no_engine_substitution |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

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

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

再次进入

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)

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

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)