什么是 ALLOW_INVALID_DATES SQL 模式的用途？

MySQL 是一种开放源代码的关系型数据库管理系统，可以使用 SQL 语言来查询、插入、更新和删除数据库中的数据。在 MySQL 中，SQL 模式定义了对应 SQL 语句应该如何执行的规则。其中，ALLOW_INVALID_DATES SQL 模式允许在 DATE、DATETIME 和 TIMESTAMP 数据类型中插入无效日期。

在正常情况下，MySQL 会尝试检查插入的日期是否符合日期格式，并在遇到无效日期时抛出错误。但是，如果启用 ALLOW_INVALID_DATES SQL 模式，则会忽略这些错误，并将无效日期作为特殊值“0000-00-00 00:00:00”存储在数据库中。

这个功能的使用场景可能会比较少，但是如果您的业务需要存储一些无法识别的日期，或者需要处理旧系统中存在的日期数据，ALLOW_INVALID_DATES 就是非常有用的。下面，我们将一步一步地探索 ALLOW_INVALID_DATES SQL 模式的用途。

阅读更多：MySQL 教程

为什么要使用 ALLOW_INVALID_DATES SQL 模式？

考虑一个场景：假设您正在开发一个应用程序，需要为用户存储一些日期信息。在大多数情况下，这些日期数据都是合法的，比如用户的生日、注册时间等等。但是，有时候用户可能会输入一些非法的日期，比如“2022年2月31日”，或者一些旧的日期数据可能并不符合当前的日期格式要求。

如果您启用了 MySQL 默认的 SQL 模式，那么在插入无效日期时会抛出错误，并使得插入失败。而使用 ALLOW_INVALID_DATES SQL 模式，即使插入无效日期，也不会导致插入失败，而是将其转换成特定的无效日期值。

下面是一个使用 INSERT 语句插入无效日期的例子：

SET sql_mode = 'ALLOW_INVALID_DATES';

INSERT INTO `test` (`id`, `name`, `birthday`)
VALUES (1, '张三', '2022-02-31');

在这个例子中，我们首先设置了当前的 SQL 模式为 ALLOW_INVALID_DATES，然后尝试将一个无效日期（“2022-02-31”）插入数据库中。使用默认的 SQL 模式，这个操作会失败并抛出一个错误。但是，由于我们启用了 ALLOW_INVALID_DATES SQL 模式，这个操作并不会失败。查询数据库可以看到以下结果：

SELECT * FROM `test`;

+----+------+---------------------+
| id | name | birthday            |
+----+------+---------------------+
| 1  | 张三 | 0000-00-00 00:00:00 |
+----+------+---------------------+

插入无效日期后，MySQL 将其存储为特定的无效日期值（“0000-00-00 00:00:00”）。

如何启用 ALLOW_INVALID_DATES SQL 模式？

默认情况下，MySQL 使用 STRICT_TRANS_TABLES SQL 模式，该模式对于日期数据类型的信仰非常严格，并会抛出错误。要启用 ALLOW_INVALID_DATES SQL 模式，可以采取以下两种方法之一：

方法一：使用 SET 语句暂时更改 SQL 模式

您可以使用 SET 语句临时更改 SQL 模式，语法如下：

SET sql_mode = 'ALLOW_INVALID_DATES';

使用这种方法更改 SQL 模式对当前连接有效，断开连接后会恢复为原来的模式。使用这种方法可以在数据库中插入无效日期，并以“0000-00-00 00:00:00”格式存储。

方法二：在 MySQL 配置文件中永久更改 SQL 模式使用这种方法可以永久更改 MySQL 的默认 SQL 模式。具体步骤如下：

打开 MySQL 配置文件（my.cnf 或 my.ini）。
在文件中找到 [mysqld] 段，并添加以下行：

sql_mode = ALLOW_INVALID_DATES

保存文件并重新启动 MySQL 服务。

修改配置文件后，MySQL 将使用 ALLOW_INVALID_DATES SQL 模式作为默认 SQL 模式。这意味着在插入无效日期时不会抛出错误，而是将其存储为特殊的无效日期值。

如何检查 ALLOW_INVALID_DATES 是否启用？

要检查 MySQL 是否启用 ALLOW_INVALID_DATES SQL 模式，可以使用以下命令：

SELECT @@global.sql_mode;

此命令将返回当前的全局 SQL 模式。

如果返回结果中包含 ALLOW_INVALID_DATES，则说明启用了 ALLOW_INVALID_DATES SQL 模式。例如：

+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,ALLOW_INVALID_DATES   |
+-----------------------------------------------------------------------------------------------------------------------+

ALLOW_INVALID_DATES SQL 模式的注意事项

虽然 ALLOW_INVALID_DATES SQL 模式可以让我们插入无效日期并存储为特殊值，但在实际使用中还是需要注意一些事项：

存储为特殊值的日期不会在任何情况下比有效日期更好，它可能会导致后续查询和计算出现意外的结果，所以尽量避免插入无效日期。
在 ALLOW_INVALID_DATES SQL 模式下，插入无效日期并不会导致失败，但在特定的 MySQL 版本和环境中可能会有意外的行为。
在 ALLOW_INVALID_DATES SQL 模式下，插入 DATE、DATETIME 和 TIMESTAMP 数据类型的无效日期都会被存储为“0000-00-00 00:00:00”。但在其他数据库中，无效日期的存储方式可能有所不同，需要根据具体情况进行处理。