mysql sqlmode设置

基础概念

MySQL的sql_mode是一个系统变量，它定义了MySQL服务器应支持的SQL语法和数据校验规则。sql_mode可以确保数据的完整性和一致性，同时也可以影响服务器的行为。

相关优势

1. 数据完整性：通过设置严格的sql_mode，可以确保插入的数据符合预期的格式和类型。
2. 一致性：在不同的数据库服务器之间保持一致的SQL行为。
3. 安全性：某些sql_mode设置可以防止SQL注入攻击。

类型

MySQL的sql_mode可以设置为多种模式，包括但不限于：

• ONLY_FULL_GROUP_BY：要求SELECT语句中的GROUP BY子句必须包含SELECT列表中的所有非聚合列。
• NO_ZERO_IN_DATE, NO_ZERO_DATE：禁止插入零日期（如'0000-00-00'）。
• STRICT_TRANS_TABLES, STRICT_ALL_TABLES：在事务表或所有表中启用严格模式，插入非法值时会引发错误。
• ERROR_FOR_DIVISION_BY_ZERO：除以零时产生错误。
• NO_AUTO_CREATE_USER：禁止创建密码为空的用户。
• NO_ENGINE_SUBSTITUTION：如果指定的存储引擎不可用，则产生错误，而不是使用默认引擎。

应用场景

1. 开发环境：在开发环境中，通常会设置较严格的sql_mode，以确保代码质量和数据完整性。
2. 生产环境：在生产环境中，根据业务需求和安全策略，可能会调整sql_mode以平衡性能和安全性。

常见问题及解决方法

问题：为什么设置了ONLY_FULL_GROUP_BY后，某些查询会失败？

原因：ONLY_FULL_GROUP_BY模式要求SELECT列表中的所有非聚合列必须在GROUP BY子句中出现。如果查询不满足这个条件，就会失败。

解决方法：

1. 修改查询，确保SELECT列表中的所有非聚合列都在GROUP BY子句中。
2. 如果不需要严格模式，可以临时禁用ONLY_FULL_GROUP_BY。

-- 查看当前的sql_mode
SHOW VARIABLES LIKE 'sql_mode';

-- 临时禁用ONLY_FULL_GROUP_BY
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

问题：为什么插入零日期会失败？

原因：NO_ZERO_IN_DATE和NO_ZERO_DATE模式禁止插入零日期。

解决方法：

1. 修改插入的数据，避免插入零日期。
2. 如果需要插入零日期，可以临时禁用这些模式。

-- 临时禁用NO_ZERO_IN_DATE和NO_ZERO_DATE
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE',''));
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE',''));

参考链接

• MySQL官方文档 - sql_mode

通过以上内容，你应该对MySQL的sql_mode有了全面的了解，包括其基础概念、优势、类型、应用场景以及常见问题的解决方法。