Mysql之SQL Mode用法详解

什么是SQL Mode?

SQL Mode是MySQL数据库中的一个参数，它用来决定哪些操作是合法的，哪些操作是非法的。SQL Mode是MySQL的一个特性，一个相同版本的MySQL可能会有不同的SQL Mode。

SQL Mode的作用

SQL Mode的作用主要有以下几点：

• 限制从Mysql5.7.4版本增强型SQL模式中删除的旧SQL特性的使用，以接近ANSI SQL标准。使开发者更加规范化地使用SQL语言编写代码，防止因为特性缺失而导致不可预知的错误。
• 限制MySQL执行时的隐式转换，避免数据丢失、误差等结果。
• 详细抛出在开发和调试时通常看不到的警告和错误信息，让开发人员能够更好地了解自己的代码是否存在问题。
• 采用更严格的语法，有助于保持数据的一致性和安全性。

SQL Mode的用法

SQL Mode的用法分为以下两种方式：

手动设置SQL Mode

SET sql_mode = 'modes';

modes是SQL Mode的模式参数，表示可以设置多个模式参数组成的字符串，下面是一个示例：

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

配置my.cnf文件设置SQL Mode

my.cnf文件是MySQL服务器的配置文件，可以通过修改my.cnf文件中的参数，来设置MySQL的SQL Mode。

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

SQL Mode示例说明

以下示例演示了关闭ONLY_FULL_GROUP_BY模式的作用：

假设有以下数据表：

CREATE TABLE users(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL
);

INSERT INTO users(name, age) VALUES ('Alice', 17);

使用以下查询语句，会提示错误：

SELECT name FROM users GROUP BY age;

提示错误信息：

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'test.users.name' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible with 
sql_mode=only_full_group_by

因为在ONLY_FULL_GROUP_BY使用下，GROUP BY必须包含所有的查询字段，这里的name字段没有被包含进GROUP BY中。关闭ONLY_FULL_GROUP_BY模式，可以不必需要GROUP BY所有的查询字段。使用以下语句关闭ONLY_FULL_GROUP_BY模式：

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

再次执行查询语句：

SELECT name FROM users GROUP BY age;

现在可以查询到结果了，输出：

+-------+
| name  |
+-------+
| Alice |
+-------+

以下示例演示NO_ZERO_DATE模式的作用：

假设有如下数据表：

CREATE TABLE stall(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  product_date DATE NOT NULL
);

插入一条数据：

INSERT INTO stall(name, product_date) VALUES('虫草花', '0000-00-00');

执行查询操作：

SELECT * FROM stall;

输出内容：

+----+--------+--------------+
| id | name   | product_date |
+----+--------+--------------+
|  1 | 虫草花 | 0000-00-00   |
+----+--------+--------------+

由于product_date字段插入了0000-00-00的日期，正常情况下应该是出现错误的，但由于NO_ZERO_DATE模式没有开启，不会提示错误。

现在开启NO_ZERO_DATE模式：

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE';

再次执行查询操作：

SELECT * FROM stall;

此时输出错误信息：

ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'product_date' at row 1

因为NO_ZERO_DATE模式会检测并禁止0000-00-00的日期，在插入该日期时，会提示错误信息。