简介

MySQL 服务器可以在不同的SQL模式下运行, 不同的SQL模式会影响MySQL支持的SQL语法以及它执行的数据验证检查。不同版本的MySQL默认的SQL模式也可能不同。 当前服务的SQL模式是由系统变量 sql_mode 决定的。

在客户端, 可以通过 select @@session.sql_mode 语句查看当前连接的sql_mode ,通过 select @@global.sql_mode 语句查看全局的 sql_mode。 比如:

mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.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 的设置跟通常的系统变量一样,有多种设置方式。
第一种是可以在配置文件里面设置sql_mode项, 如:

vim /etc/my.cnf
………………
[mysqld_safe]
sql_mode = ''
………………

也可以在启动时加入配置项 --sql_mode='' 来设置;

这样的设置在服务器启动时就生效,影响是 global 级别的。

而在MySQL服务启动之后,也可以通过set global|session sql_mode='SQL模式'来设置,如:

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

mysql> select @@global.sql_mode;       
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

sql_mode 的值由constants.SQLMode类提供,如果要设置多个模式,要将这些模式用逗号","分隔开。 所以, 这里的SQL也可以使用 list_add() 和 list_drop() 函数,如:

mysql> select @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//drop
mysql> set @@session.sql_mode=sys.list_drop(@@session.sql_mode, 'ONLY_FULL_GROUP_BY'); 
Query OK, 0 rows affected (0.01 sec)

mysql> select @@session.sql_mode;                                                     +----------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                 |
+----------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


//add
mysql> set @@session.sql_mode=sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY'); Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果要想清除所有的 sql_mode ,只需要将 sql_mode 设置为空字符串就可以, 就是上面演示的 “set session|global sql_mode='';” 语句。

示例

MySQL5.7之后的版本, 包括MySQL8.0, sql_mode 并无太大差异, 这里以MySQL8.0.12版本的 SQL 模式为切入点,介绍几种常见的 sql_mode。 另外如果没有特殊说明,下面提到的 sql_mode 在 MySQL5.7 及之后的其他版本中也适用。

mysql> select @@global.sql_mode;        
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ONLY_FULL_GROUP_BY

在 SQL-92和更早的版本中,ONLY_FULL_GROUP_BY 模式定义,“对于 GROUP BY 语句, SQL 中 SELECT 的列、HAVING 条件中的列、ORDER BY 的列,除了使用聚合函数的列之外, 都必须在 GROUP BY 中,否则就是非法的 SQL”。

在 MySQL5.7 之前的版本, MySQL扩展SQL标准,默认不开启 ONLY_FULL_GROUP_BY 模式, 所以在之前即使不符合上述描述, SQL 依然可以执行。

比如在5.6中:

//此模式默认关闭。
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.44    |
+-----------+
1 row in set (0.00 sec)

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

mysql> 

//新建一张表,并插入10条数据:
CREATE TABLE `user_score` (
  `id` int(10) NOT NULL,
  `username` varchar(20) NOT NULL DEFAULT '',
  `age` int(8) DEFAULT NULL,
  `class` varchar(10) DEFAULT NULL,
  `score` int(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user_score`
VALUES
    ( 1, 'Lili', 13, 'Chinese', 78 ),
    ( 2, 'Jhon', 14, 'physical', 60 ),
    ( 3, 'Bob', 17, 'math', 73 ),
    ( 4, 'Bob', 17, 'Chinese', 98 ),
    ( 5, 'Lili', 13, 'math', 88 ),
    ( 6, 'Bob', 17, 'physical', 37 ),
    ( 7, 'Jhon', 14, 'math', 59 ),
    ( 8, 'Lili', 13, 'physical', 77 ),
    ( 9, 'Jhon', 14, 'Chinese', 74 ),
    ( 10, 'Cindy', 11, 'math', 92 );
    

默认情况下, 以下 SQL 可以执行, 但是当开启此模式, SQL 检测会报错:

//默认模式下
mysql> select id,username from user_score group by username;
+----+----------+
| id | username |
+----+----------+
|  3 | Bob      |
| 10 | Cindy    |
|  2 | Jhon     |
|  1 | Lili     |
+----+----------+
4 rows in set (0.02 sec)
mysql> select id,username from user_score group by username order by id;
+----+----------+
| id | username |
+----+----------+
|  1 | Lili     |
|  2 | Jhon     |
|  3 | Bob      |
| 10 | Cindy    |
+----+----------+
4 rows in set (0.00 sec)

mysql> select id,username from user_score group by username having id < 5;
+----+----------+
| id | username |
+----+----------+
|  3 | Bob      |
|  2 | Jhon     |
|  1 | Lili     |
+----+----------+
3 rows in set (0.00 sec)

//开启之后
mysql> set session sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.02 sec)

mysql> select id,username from user_score group by username;
ERROR 1055 (42000): 'test.user_score.id' isn't in GROUP BY

mysql> select username from user_score group by username order by id;   
ERROR 1055 (42000): 'test.user_score.id' isn't in GROUP BY

mysql> select id,username from user_score group by username having id < 5; 
ERROR 1055 (42000): 'test.user_score.id' isn't in GROUP BY

SQL:1999 版本的SQL标准对上述限制做了优化, 如果上述的列在功能上依赖于 GROUP BY 列, 这种情况就是合法的。 原文档是 "SQL:1999 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers." 。 这里不是很理解, 我主观地认为,当聚合列是主键或者unique index 时, 满足这种 “functionally dependent on” 的关系。欢迎各位解惑。

比如说下面这条SQL, GROUP BY 的列 id 是主键,所以id能确定是unique的:

//在5.6中开启 ONLY_FULL_GROUP_BY 模式 时非法:
mysql> select id,username from user_score group by id;
ERROR 1055 (42000): 'test.user_score.username' isn't in GROUP BY

//在8.0中开启 ONLY_FULL_GROUP_BY 模式 时合法:
mysql> select id,username from user_score group by id;
+----+----------+
| id | username |
+----+----------+
|  1 | Lili     |
|  2 | Jhon     |
|  3 | Bob      |
|  4 | Bob      |
|  5 | Lili     |
|  6 | Bob      |
|  7 | Jhon     |
|  8 | Lili     |
|  9 | Jhon     |
| 10 | Cindy    |
+----+----------+
10 rows in set (0.00 sec)

而 MySQL5.7 开始遵守了 SQL:1999 ,同时默认开启了 ONLY_FULL_GROUP_BY 模式,也就是说之前默认能执行的group by的sql,升级到5.7之后,如果说不满足上面说的“功能上依赖”关系, 查询时就会报错。

比如上面的 "select id,username from user_score group by username ;" 语句, 同样在默认配置的情况下,因为id和聚合列username不存在这种关系, 在 MySQL5.7 之后的版本中会报错:

//默认配置下
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

mysql> select id,username from user_score group by username;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user_score.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> 

想避免升级之后的这种报错, 有两种方式。

一个就是显式的关闭 ONLY_FULL_GROUP_BY 模式,关闭方式在上文提到过,不明确的可以翻到上面 设置 章节看一下。 这里演示通过修改系统变量的方式,比如:

mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
//之前报非法的sql就可以执行了
mysql> select id,username from user_score group by username;
+----+----------+
| id | username |
+----+----------+
|  1 | Lili     |
|  2 | Jhon     |
|  3 | Bob      |
| 10 | Cindy    |
+----+----------+
4 rows in set (0.00 sec)

另一个方式就是使用 ANY_VALUE() 函数,
MySQL文档中描述, 如果列既不在聚合函数中,也不再 GROUP BY 中, 使用 ANY_VALUE() 函数,可以让 ONLY_FULL_GROUP_BY 模式不检查此列,效果跟关闭此模式一样。 另外ANY_VALUE() 不是聚合函数,只是用来抑制非确定的测试,如果能够确定你所选的非聚合列不受此影响, 它就非常有用,比如每个人的年龄一定是惟一的 :

mysql> set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select any_value(age) as age,username from user_score group by username;     
+------+----------+
| age  | username |
+------+----------+
|   13 | Lili     |
|   14 | Jhon     |
|   17 | Bob      |
|   11 | Cindy    |
+------+----------+
4 rows in set (0.00 sec)

但需要注意的是, 当使用上述两种方式避开了 ONLY_FULL_GROUP_BY 模式的检测, 查询结果是具有非确定性的,因为通过业务我能明确知道每个学生的年龄字段一定是唯一确定的,所以我可以这样去查。 但是同样针对上表,如果业务场景是要查询各科成绩最高的学生的名字:

mysql> select * from user_score;
+----+----------+------+----------+-------+
| id | username | age  | class    | score |
+----+----------+------+----------+-------+
|  1 | Lili     |   13 | Chinese  |    78 |
|  2 | Jhon     |   14 | physical |    60 |
|  3 | Bob      |   17 | math     |    73 |
|  4 | Bob      |   17 | Chinese  |    98 |
|  5 | Lili     |   13 | math     |    88 |
|  6 | Bob      |   17 | physical |    37 |
|  7 | Jhon     |   14 | math     |    59 |
|  8 | Lili     |   13 | physical |    77 |
|  9 | Jhon     |   14 | Chinese  |    74 |
| 10 | Cindy    |   11 | math     |    92 |
+----+----------+------+----------+-------+
10 rows in set (0.00 sec)

mysql> select any_value(id) as id, any_value(username) as username, class, max(score) from user_score group by class;  
+----+----------+----------+------------+
| id | username | class    | max(score) |
+----+----------+----------+------------+
|  1 | Lili     | Chinese  |         98 |
|  2 | Jhon     | physical |         77 |
|  3 | Bob      | math     |         92 |
+----+----------+----------+------------+
3 rows in set (0.00 sec)

使用ANY_VALUE()绕开 ONLY_FULL_GROUP_BY 模式检测,这个sql不会报错并有返回, 但是这个查询结果明显是不符合预期的, 因为username是任意一条记录的username, 并不是 max(score) 的username。

而要解决这个业务场景,绕开 ONLY_FULL_GROUP_BY 模式检测不是理智的选择。 这里可以使用派生表:

mysql> select id,username,class,score, (select max(score) from user_score where class=us.class) as mscore
    ->  from user_score as us having score=mscore;
+----+----------+----------+-------+------+
| id | username | class    | score | mscore   |
+----+----------+----------+-------+------+
|  4 | Bob      | Chinese  |    98 |   98 |
|  8 | Lili     | physical |    77 |   77 |
| 10 | Cindy    | math     |    92 |   92 |
+----+----------+----------+-------+------+
3 rows in set (0.00 sec)

或者使用连接查询:


mysql> select us.id,us.username,us.class,us.score,ms.mscore from user_score as us, (select max(score) as mscore from user_score group by class) as ms where  us.score=ms.mscore;
+----+----------+----------+-------+--------+
| id | username | class    | score | mscore |
+----+----------+----------+-------+--------+
|  4 | Bob      | Chinese  |    98 |     98 |
|  8 | Lili     | physical |    77 |     77 |
| 10 | Cindy    | math     |    92 |     92 |
+----+----------+----------+-------+--------+
3 rows in set (0.00 sec)

这样的查询结果才是符合预期的。

STRICT_TRANS_TABLES

STRICT_TRANS_TABLES 为事务引擎启用严格模式,常见的场景就是对无效数据的约束。在 5.6 以及之后的版本中, STRICT_TRANS_TABLES 都是默认开启的。

对于事务引擎, 当发现 SQL 中存在无效数据时,会回滚事务并返回一个报错。比如:

CREATE TABLE `user_info` (
`id` int(10) NOT NULL,
`username` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> insert into user_info value (3, '1234567890'),(4,12345678901);
ERROR 1406 (22001): Data too long for column 'username' at row 2
mysql> select * from user_info;
Empty set (0.00 sec)

同时对非事务引擎, 此模式也有一定程度的影响, 这种影响体现在批量插入或更新语句中, 如果第一行有无效数据,则中止执行, 如果不是第一行,因为前面的数据已插入, 所以后面无效的数据会被特殊处理, SQL 执行也不会报错,而是返回一个 warning。比如:

CREATE TABLE `user_info_m` (
`id` int(10) NOT NULL,
`username` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyiSAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)

//不合法的数据不在第一行,会特殊处理执行,返回warning。
mysql> insert into user_info_m value (3, '1234567890'),(4,12345678901); 
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from user_info_m;
+----+------------+
| id | username   |
+----+------------+
|  3 | 1234567890 |
|  4 | 1234567890 |
+----+------------+
2 rows in set (0.00 sec)

//不合法数据在第一行直接返回错误
mysql> insert into user_info_m value (5, '12345678901'),(6,1234567890);
ERROR 1406 (22001): Data too long for column 'username' at row 1

还有一个 STRICT_ALL_TABLES 模式, 这个与 STRICT_TRANS_TABLES 不同的地方就是针对上面说的非事务引擎的表执行批量操作时, 即使无效数据不在第一行, 也会得到一个报错,但是要注意的是,这里虽然得到一个报错, 但无效数据之前的操作还是已经完成了, 可能会存在数据不一致的情况。 比如

mysql> set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';     
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user_info_m value (5, '1234567890'),(6,12345678901); 
ERROR 1406 (22001): Data too long for column 'username' at row 2
//可以看到 insert 语句返回 error,但是id=5那条记录因为数合法的,所以已经先插入进去了。
mysql> select * from user_info_m;
+----+------------+
| id | username   |
+----+------------+
|  3 | 1234567890 |
|  4 | 1234567890 |
|  5 | 1234567890 |
+----+------------+
3 rows in set (0.00 sec)

mysql> 

所以建议这种对数据合法性的检测, 放在业务代码层去判断, 不应该依赖 MySQL 的语法检测。

在开启严格模式下, 可能会得到的报错场景有很多种,主体包含以下列表中的:

ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED

比如 "ER_DATA_TOO_LONG" ,就是上面演示到的。
默认开启严格模式时,数据长度超过了字段预留的长度,报此错误信息。

mysql> select * from user_info;
Empty set (0.00 sec)

mysql> insert into user_info value (1,'12345678901');       
ERROR 1406 (22001): Data too long for column 'username' at row 1
mysql> select * from user_info;
Empty set (0.00 sec)

当关闭严格模式时:

mysql> set @@session.sql_mode=sys.list_drop(@@session.sql_mode, 'STRICT_TRANS_TABLES');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into user_info value (1,'12345678901');                                 Query OK, 1 row affected, 1 warning (0.09 sec)

//默认保留了最大长度
mysql> select * from user_info;
+----+------------+
| id | username   |
+----+------------+
|  1 | 1234567890 |
+----+------------+
1 row in set (0.00 sec)

其他场景的演示会单独汇总到一个附件,就不在这里占用太多空间了。

在上面我关闭严格模式的时候, 可以看到 query 返回了一个 warning:

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这个warning是说 "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO" 要跟严格模式配合使用, 并且在未来会合并到严格模式中去。 上面看到MySQL5.7之后的版本是默认开启这几个模式以及严格模式的, 我们仅关闭了严格模式, 所以才有此警告信息产生。

那么这几个模式这里也可以一道看下。

NO_ZERO_DATE

NO_ZERO_DATE 模式会影响服务器是否允许 '0000-00-00'作为有效日期 。

MySQL 文档中描述 "NO_ZERO_DATE 已弃用。NO_ZERO_DATE 不是严格模式的一部分,但应与严格模式一起使用,默认情况下启用。如果 NO_ZERO_DATE 启用但未启用严格模式,则会发出警告, 反之亦然。

因为NO_ZERO_DATE已弃用,它将在未来的MySQL版本中作为单独的模式名称删除,其效果包含在严格SQL模式的效果中。 ",这也是上面我们得到的警告信息的官方解释。

这里可以做如下测试:

CREATE TABLE `user_date` (
`id` int(10) NOT NULL,
`username` varchar(10) NOT NULL DEFAULT '',
`login_time`  timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

严格模式开启时:

//默认开启 NO_ZERO_DATE 模式
mysql> insert into user_date value (1, 'Jhon', '0000-00-00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00' for column 'login_time' at row 1

//关闭 NO_ZERO_DATE 模式
mysql> set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into user_date value (1, 'Jhon', '0000-00-00');                         Query OK, 1 row affected (0.04 sec)

严格模式关闭时

//关闭严格模式开启 NO_ZERO_DATE 会有warning, 执行会成功,也返回一个warning
mysql> set session sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into user_date value (2, 'Cindy', '0000-00-00');                        Query OK, 1 row affected, 1 warning (0.06 sec)

//同时关闭 NO_ZERO_DATE 模式
mysql> insert into user_date value (3, 'Smith', '0000-00-00');                        Query OK, 1 row affected (0.03 sec)

所以 NO_ZERO_DATE 模式关闭的情况下,不论是否开启严格模式, '0000-00-00' 格式的日期都可以插入。只有配合严格模式一起使用, '0000-00-00' 格式的日期才会报错。

此模式不影响查询,即使是在关闭此模式期间插入的非法日期, 在开启严格模式以及此模式之后,查询也不会报错:

mysql> select @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from user_date;
+----+----------+---------------------+
| id | username | login_time          |
+----+----------+---------------------+
|  1 | Jhon     | 0000-00-00 00:00:00 |
|  2 | Cindy    | 0000-00-00 00:00:00 |
|  3 | Smith    | 0000-00-00 00:00:00 |
+----+----------+---------------------+
3 rows in set (0.01 sec)

NO_ZERO_IN_DATE

NO_ZERO_IN_DATE 模式与 NO_ZERO_DATE 不同的地方在于 , NO_ZERO_DATE 模式限制了'0000-00-00' 格式的日期。NO_ZERO_IN_DATE 限制的是 年份为非零,但是月份或者日期为0的日期, 比如 '2019-00-01' 或者 '2019-01-00' 。 其他地方与 NO_ZERO_DATE 一致。 未来也会被合并到严格模式中去。

ERROR_FOR_DIVISION_BY_ZERO

ERROR_FOR_DIVISION_BY_ZERO 模式跟它的字面意思一样 , 影响的是 除以 0 的操作。 此模式同样需要跟严格模式配合使用。

如果未开启严格模式, 仅开启此模式,除以0会产生一个警告, 并将结果置为 NULL。

CREATE TABLE `user_age` (
`id` int(10) NOT NULL,
`username` varchar(10) NOT NULL DEFAULT '',
`age`  int(8)  NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> set session sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into user_age value (1, 'Cindy', MOD(id,0));
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> select * from user_age;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | Cindy    | NULL |
+----+----------+------+
1 row in set (0.00 sec)

mysql> insert into user_age value (2, 'Jhon', MOD(id,0));      
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> show warning;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1
mysql> select * from user_age;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | Cindy    | NULL |
|  2 | Jhon     | NULL |
+----+----------+------+
2 rows in set (0.00 sec)

如果未开启严格模式, 也未开启 ERROR_FOR_DIVISION_BY_ZERO 模式, 除以0 会直接置为 NULL, 不会有警告。

mysql> set session sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into user_age value (3, 'Tina', MOD(id,0));     
Query OK, 1 row affected (0.06 sec)

mysql> select * from user_age;                                                        +----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | Cindy    | NULL |
|  2 | Jhon     | NULL |
|  3 | Tina     | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

如果开启了严格模式, 此模式开启时除以0会产生 error ;此模式关闭同样不受影响, 结果会直接置为 NULL。

//默认都开启时
mysql> select @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into user_age value (4, 'Bob', MOD(id,0));     
ERROR 1365 (22012): Division by 0

//关闭 ERROR_FOR_DIVISION_BY_ZERO 模式

mysql> set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into user_age value (5, 'Ogenes', MOD(id,0));                           Query OK, 1 row affected (0.01 sec)

mysql> select * from user_age;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | Cindy    | NULL |
|  2 | Jhon     | NULL |
|  3 | Tina     | NULL |
|  5 | Ogenes   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

跟 NO_ZERO_DATE 模式一样, 从 MySQL5.6.17 版本开始, 不推荐单独使用 ERROR_FOR_DIVISION_BY_ZERO 模式, 此模式在以后的版本也会合并到严格模式中去。

查询时除以0结果会做 NULL 处理,但是开启此模式和严格模式会得到1个warning:

mysql> select *, MOD(id,0) as mid from user_age;
+----+----------+------+------+
| id | username | age  | mid  |
+----+----------+------+------+
|  1 | Cindy    | NULL | NULL |
|  2 | Jhon     | NULL | NULL |
|  3 | Tina     | NULL | NULL |
|  5 | Ogenes   | NULL | NULL |
+----+----------+------+------+
4 rows in set, 4 warnings (0.00 sec)

关闭此模式就不会有warning:

mysql> set session sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select *, MOD(id,0) as mid from user_age;                                      +----+----------+------+------+
| id | username | age  | mid  |
+----+----------+------+------+
|  1 | Cindy    | NULL | NULL |
|  2 | Jhon     | NULL | NULL |
|  3 | Tina     | NULL | NULL |
|  5 | Ogenes   | NULL | NULL |
+----+----------+------+------+
4 rows in set (0.00 sec)

参考资料:

MySQL :: MySQL 8.0参考手册:: 5.1.11服务器SQL模式

MySQL :: MySQL Connector / Python开发人员指南:: 10.2.47 MySQLConnection.sql_mode属性

MySQL :: MySQL 8.0参考手册:: 11.7数据类型默认值

MySQL :: MySQL 8.0 Reference Manual :: 12.20.3 MySQL Handling of GROUP BY

MySQL :: MySQL 8.0 Reference Manual :: 1.8.3.3 Constraints on Invalid Data

标签: none

添加新评论