MySQL严格模式下的验证机制

阅读更多：MySQL 教程

简介

MySQL是一个非常流行的关系型数据库管理系统，它能够适应各种应用场景，从小型应用到大型数据仓库。案例中提到了MySQL的严格模式，那么MySQL在严格模式下会进行哪些验证呢？这就是本文的主题。

MySQL的严格模式

MySQL的严格模式是一种配置选项，它可以提高MySQL的验证机制。在严格模式下，MySQL会对一些操作数据的语句进行额外的检查，以保证数据完整性。如果操作不合规则，MySQL会拒绝执行。

严格模式下MySQL提供以下的措施：

对于非法的日期和时间值，MySQL不会主动进行类型转换，而是返回一个错误给调用者。比如”2021-02-31″是一个非法的日期，一般情况下MySQL会转换成”2021-03-03″；
在插入数据时，如果违反了约束条件，MySQL会拒绝插入数据。例如一个表表明了年龄需要在0到100之间，但是插入了一个150岁的人；
在内部遇到了错误，MySQL会报告错误给调用者；
在分组查询时，没有被分组的列会被显示为NULL。

MySQL的严格模式可以以三种方式启用：

在配置文件中设置sql_mode=STRICT_ALL_TABLES；
在命令行中设置--sql-mode=STRICT_ALL_TABLES；
在MySQL客户端中使用SET sql_mode=STRICT_ALL_TABLES命令来设置。

有了严格模式，MySQL会提供额外的保障，但是也带来了一些限制。在实际应用中，可以根据具体情况来选择是否开启严格模式。

严格模式的验证机制举例

下面我们来看一下MySQL在严格模式下的验证机制具体是怎样的，以及如何应对这些验证机制，这里我们给出以下几个验证机制的例子。

1. 插入与更新数据时的验证

在严格模式下，MySQL会对数据类型进行强制匹配，如果数据类型不匹配，MySQL会返回一个错误。

将字符串型的”hello world”插入到整形字段中的语句：

insert into test_table(id,name) values(12,'hello world');

执行上面的sql语句，MySQL会返回下面的错误：

ERROR 1366 (HY000): Incorrect integer value: 'hello world' for column 'id' at row 1

这是因为字符串”hello world”不能插入到整型的id字段中。

2. 非法数据的插入

还是之前那个例子，在使用MySQL的非严格模式时，MySQL不会对”150″这样的非法数据进行验证，而是进行了类型转换并插入表中。

mysql> CREATE TABLE students(id INT PRIMARY KEY,name VARCHAR(20),age INT CHECK(age<100));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into students(id,name,age) values(3,'zhangsan',150);
Query OK, 1 row affected, 1 warning (0.09 sec)

mysql> select * from students;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | zhangsan |  150 |
+----+----------+------+

然而在严格模式下，MySQL会拒绝插入这样的数据，并返回错误。

SET sql_mode='STRICT_ALL_TABLES';
mysql> insert into students(id,name,age) values(4,'lisi',150);
ERROR 4025 (23000): CONSTRAINT `students_chk_1`failed for `sandbox`.`students`.`age` check(age<100)

这是因为严格模式下MySQL会按照定义的约束进行严格验证，保证插入的数据满足要求。在上面的例子中，由于年龄大于100，导致插入失败。

3. 聚合函数的使用

在严格模式下，聚合函数必须对所有列进行分组，否则MySQL会返回一个错误。

CREATE TABLE student_scores (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    class VARCHAR(20),
    score FLOAT
);
INSERT INTO student_scores VALUES (1,'Tom','One',80);
INSERT INTO student_scores VALUES (2,'Mike','Two',90);
INSERT INTO student_scores VALUES (3,'Mary','One',85);
INSERT INTO student_scores VALUES (4,'Smith','One',75);

我们在使用SUM函数时，对所有列进行分组，可以得到正确结果。

mysql> SELECT class, SUM(score) FROM student_scores GROUP BY class;
+-------+-----------+
| class | SUM(score)|
+-------+-----------+
| One   |       240 |
| Two   |        90 |
+-------+-----------+

但如果我们把class列去掉，MySQL会返回错误。

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

错误信息显示了必须对所有列进行分组，否则MySQL会拒绝执行。