MySQL中的sql_mode

sql_mode 是 MySQL 数据库中的一个参数，用于设置 SQL 执行的模式。它定义了 MySQL 在执行 SQL 语句时的行为和规则。通过设置不同的 sql_mode 值，可以改变 MySQL 的默认行为，从而提供更严格或更宽松的 SQL 语义。

sql_mode 可以影响以下方面：

NULL 值处理：控制 MySQL 对待 NULL 值的方式，如是否允许插入 NULL 值、NULL 值的比较规则等。

严格模式：控制 MySQL 是否执行严格的数据校验，如插入的值是否符合列定义、是否允许插入不完整的日期或日期时间等。

零值处理：控制 MySQL 对待零值的方式，如是否允许插入零值、零值的比较规则等。

日期处理：控制 MySQL 对日期的处理方式，如是否允许插入无效的日期、日期的比较规则等。

字符集处理：控制 MySQL 如何处理字符集和排序规则，如是否区分大小写、是否允许存储非 Unicode 字符等。

自增列：控制 MySQL 对自增列的处理方式，如插入时是否需要指定自增列的值。

sql_mode作用

1. 数据完整性和一致性：sql_mode 可以用于控制数据的插入、更新和比较规则，从而提高数据的完整性和一致性。例如，可以设置 STRICT_ALL_TABLES 模式，禁止插入不符合列定义的值，或者设置 ONLY_FULL_GROUP_BY 模式，要求在 GROUP BY 查询中的 SELECT 列必须是 GROUP BY 列或聚合函数。
2. 兼容性：sql_mode 允许调整 MySQL 的行为，使其更符合特定 SQL 标准或与其他数据库的兼容性要求。通过设置适当的 sql_mode 值，可以使应用程序在不同的数据库管理系统之间更易迁移。
3. 强制严格模式：通过启用严格模式，sql_mode 可以提高数据的一致性和准确性。在严格模式下，MySQL 会执行更严格的数据验证，如禁止插入截断的数据、禁止插入无效的日期等。
4. 错误处理：sql_mode 在错误处理方面也起到一定的作用。通过设置适当的 sql_mode 值，可以使 MySQL 在遇到潜在问题时更严格地报错，从而提供更好的错误反馈和调试信息。
5. 字符集处理：sql_mode 可以影响 MySQL 对字符集和排序规则的处理。通过设置适当的 sql_mode 值，可以控制字符集的比较行为，例如是否区分大小写、是否允许存储非 Unicode 字符等

sql_mode报错类型
当执行的 SQL 语句与当前的 sql_mode 不兼容时，MySQL 会根据设置的模式报告相应的错误。具体的错误类型取决于执行的语句和 sql_mode 的配置。以下是一些可能的错误类型和示例：

1. 插入 NULL 值错误：如果 sql_mode 中设置了不允许插入 NULL 值，而你尝试将 NULL 值插入到禁止 NULL 的列中时，会报错。示例：Column 'column_name' cannot be null
2. 严格模式错误：当 sql_mode 中启用了严格模式，而你尝试插入不符合列定义的值时，会报错。示例：Data truncation: Incorrect value 'value' for column 'column_name' at row 1
3. 零值错误：如果 sql_mode 中设置了不允许插入零值，而你尝试将零值插入到禁止零值的列中时，会报错。示例：Incorrect integer value: '0' for column 'column_name' at row 1
4. 日期错误：当 sql_mode 中设置了严格的日期处理模式，而你尝试插入无效的日期值时，会报错。示例：Incorrect date value: '2023-02-30' for column 'column_name' at row 1
5. 字符集错误：如果 sql_mode 中设置了严格的字符集处理模式，而你尝试插入非 Unicode 字符或违反排序规则的字符时，会报错。示例：Illegal mix of collations

这只是一些可能的错误类型和示例，实际的错误取决于具体的 sql_mode 配置和执行的 SQL 语句。如果遇到错误，可以根据错误提示来调整 sql_mode 的设置或修改 SQL 语句以符合当前的模式要求。

sql_mode参数和规则
1）ONLY_FULL_GROUP_BY：出现在SELECT语句、HAVING条件和ORDER BY语句中的列，必须是GROUP BY的列或者依赖于GROUP BY列的函数列。

2）NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

3）STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制。

4）NO_ZERO_IN_DATE：这个模式影响了是否允许日期中的月份和日包含0。如果开启此模式，2016-01-00是不允许的，但是0000-02-01是允许的。

5）NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期。

6）ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时，MySQL返回NULL。

7）NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户。

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

9）PIPES_AS_CONCAT：将”||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似。

10）ANSI_QUOTES：启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符。

修改sql_mode
为了避免上述sql_mode报错，需要修改sql_mode参数。

查看sql_mode配置

select @@sql_mode;
select @@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

临时修改

SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';# 需要重新连接mysql生效
永久修改

# 配置写入到my.cnf的[mysqld]下然后重启mysql
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

开发前建议先制定好规则，然后遵守这个规则，sql写的不行就去优化，别来回来去改sql_mode