MySQL中的sql_mode参数

AsiaYe

发布于 2019-11-06 16:42:14

1.5K0

发布于 2019-11-06 16:42:14

文章被收录于专栏：DBA随笔

MySQL中的sql_mode参数

sql_mode参数详解

首先我们看看mysql中默认的sql_mode的值是什么：

root@localhost :(none)09:25:15>select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我们可以看到，sql_mode一共有7个值，分别是

only_full_group_by：

对于group by聚合操作，如果在select中的列，没有在group by中出现，那么将认为这个sql是不合法的，因为列不在group by从句中。这里我们通过一个例子来看：

root@localhost :DBAs09:31:38>select * from t5;
+----+-----+
| id | nam |
+----+-----+
|  1 | yyz |
|  2 | yyz |
|  3 | yyz |
|  4 | yyz |
|  5 | yyz |
|  6 | yyz |
+----+-----+
6 rows in set (0.00 sec)

root@localhost :DBAs09::>select * from t5 group by nam;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'DBAs.t5.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
root@localhost :DBAs09:31:54>select nam from t5 group by nam;
+-----+
| nam |
+-----+
| yyz |
+-----+
1 row in set (0.00 sec)

我们可以看到，当我们select*的时候，包含id和nam两列，sql_mode参数的存在不允许我们对个结果中的nam列进行group by，但是当我们select nam的时候，就可以进行group by操作。当我们去除

only_full_group_by这个选项的时候，可以发现：

root@localhost :DBAs09:35:08>SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0.00 sec)

root@localhost :DBAs09:35:19>select * from t5 group by nam;
+----+-----+
| id | nam |
+----+-----+
|  1 | yyz |
+----+-----+
1 row in set (0.00 sec)

现在对于这种select * group by 操作，已经可以执行了。

strict_trans_tables：

在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制。在STRICT_TRANS_TABLES模式下，插入数据时，mysql会严格的进行数据的校验，当发现插入列值未满足要求，直接报告error错误，保证了错误数据无法插入到数据库中。

root@localhost :DBAs09:53:10>insert into t5 (nam) values ('111111111111111111111');
ERROR 1406 (22001): Data too long for column 'nam' at row 1
root@localhost :DBAs09:53:16>select @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost :DBAs09::>select @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode                                                            |
+-----------------------------------------------------------------------+
| ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost :DBAs09::>insert into t5 (nam) values ('111111111111111111111');
Query OK, 1 row affected, 1 warning (0.03 sec)

no_zero_in_date：

在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'1900-00-00'。在非严格模式，可以接受该日期，但会生成警告。举例子如下;

root@localhost :DBAs09:40:47>insert into t5 (tbl_date) values ('1980-00-00');
ERROR 1292 (22007): Incorrect date value: '1980-00-00' for column 'tbl_date' at row 1
root@localhost :DBAs09:40:54>select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost :DBAs09::>SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE',''));
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost :DBAs09:41:31>insert into t5 (tbl_date) values ('1980-00-00');
Query OK, 1 row affected (0.02 sec)

no_zero_date:

在严格模式，不要将 '0000-00-00'做为合法日期，如果设置的话，直接报错。在非严格模式，可以接受该日期，但会生成警告。举例如下：

root@localhost :DBAs09:41:35>insert into t5 (tbl_date) values ('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'tbl_date' at row 1
root@localhost :DBAs09:43:58>^C
root@localhost :DBAs09:44:23>SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE',''));
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost :DBAs09:45:25>insert into t5 (tbl_date) values ('0000-00-00');
Query OK, 1 row affected (0.01 sec)

no_auto_create_user

防止GRANT自动创建新用户，除非还指定了密码。当我们没有指定密码的时候，如果存在no_auto_create_user选项，则不能通过grant来创建没有密码的用户。

root@localhost :DBAs09:56:32>grant select on *.* to yeyz@'127.0.0.1';
ERROR 1133 (42000): Can't find any matching row in the user table
root@localhost :DBAs09:56:57>select @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode                                                            |
+-----------------------------------------------------------------------+
| ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost :DBAs09::>SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_AUTO_CREATE_USER',''));
Query OK, 0 rows affected, 2 warnings (0.00 sec)

root@localhost :DBAs09:57:52>grant select on *.* to yeyz@'127.0.0.1';
Query OK, 0 rows affected, 1 warning (0.02 sec)

no_engine_substitution:

如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。

root@localhost :DBAs10:07:42>create table test(id int) ENGINE=FEDERATED;
ERROR 1286 (42000): Unknown storage engine 'FEDERATED'
root@localhost :DBAs10:07:45>SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ENGINE_SUBSTITUTION',''));
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost :DBAs10:08:14>create table test(id int) ENGINE=FEDERATED;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

root@localhost :DBAs10:08:16>show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1286 | Unknown storage engine 'FEDERATED'           |
| Warning | 1266 | Using storage engine InnoDB for table 'test' |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

error_for_division_by_zero：

在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-02-25，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server