MySQL默认的sql mode怎么设置

引言

MySQL是一个广泛使用的关系型数据库管理系统，它提供了丰富的功能和灵活的配置选项。其中，SQL模式（SQL Mode）是MySQL中一个非常重要的配置项，它决定了MySQL在执行SQL语句时的行为。通过设置SQL模式，可以控制MySQL的严格性、兼容性以及其他一些行为。本文将详细介绍MySQL默认的SQL模式如何设置，以及如何根据需求调整SQL模式。

什么是SQL模式

SQL模式是MySQL中一组用于控制SQL语句执行行为的设置。它决定了MySQL如何处理数据、执行查询以及处理错误。SQL模式可以影响以下几个方面：

1. 数据验证：SQL模式可以控制MySQL在插入或更新数据时是否进行严格的验证。例如，是否允许插入无效的日期或时间值。
2. 语法兼容性：SQL模式可以控制MySQL是否兼容其他数据库系统的SQL语法。例如，是否允许使用ANSI SQL语法。
3. 错误处理：SQL模式可以控制MySQL在遇到错误时的行为。例如，是否在遇到错误时继续执行SQL语句。
4. 性能优化：SQL模式可以影响MySQL的查询优化行为。例如，是否启用某些优化选项。

MySQL默认的SQL模式

在MySQL 5.7及更高版本中，默认的SQL模式是ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION。这些模式的组合提供了较为严格的SQL执行环境，以确保数据的完整性和一致性。

默认SQL模式的详细解释

1. ONLY_FULL_GROUP_BY：该模式要求在使用GROUP BY子句时，SELECT列表中的每一列都必须在GROUP BY子句中明确列出，或者是聚合函数的参数。这可以防止在分组查询中出现不明确的列引用。

2. STRICT_TRANS_TABLES：该模式在事务表中启用严格模式。在严格模式下，MySQL会拒绝插入或更新无效的数据（例如，无效的日期或时间值），而不是将其转换为默认值。

3. NO_ZERO_IN_DATE：该模式禁止在日期中使用零值。例如，0000-00-00被视为无效日期。

4. NO_ZERO_DATE：该模式禁止使用零日期（0000-00-00）。如果尝试插入或更新为零日期的数据，MySQL将拒绝该操作。

5. ERROR_FOR_DIVISION_BY_ZERO：该模式在遇到除以零的错误时，MySQL会抛出错误，而不是返回NULL。

6. NO_AUTO_CREATE_USER：该模式禁止在GRANT语句中自动创建用户。如果用户不存在，MySQL将抛出错误。

7. NO_ENGINE_SUBSTITUTION：该模式禁止在创建表时自动替换存储引擎。如果指定的存储引擎不可用，MySQL将抛出错误。

如何查看当前的SQL模式

在MySQL中，可以使用以下命令查看当前的SQL模式：

SELECT @@sql_mode;

该命令将返回当前会话的SQL模式设置。如果要查看全局的SQL模式设置，可以使用以下命令：

SELECT @@global.sql_mode;

如何设置SQL模式

在MySQL中，可以通过以下几种方式设置SQL模式：

1. 在MySQL配置文件中设置

可以在MySQL的配置文件（通常是my.cnf或my.ini）中设置SQL模式。在[mysqld]部分添加或修改sql_mode选项即可。例如：

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

修改配置文件后，需要重启MySQL服务以使更改生效。

2. 在MySQL命令行中设置

可以在MySQL命令行中使用SET语句设置SQL模式。例如：

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

该设置仅对当前会话有效。如果要设置全局的SQL模式，可以使用以下命令：

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

3. 在应用程序中设置

在应用程序中，可以通过执行SQL语句来设置SQL模式。例如，在PHP中可以使用以下代码：

$mysqli = new mysqli("localhost", "user", "password", "database");
$mysqli->query("SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'");

如何根据需求调整SQL模式

根据不同的应用场景和需求，可能需要调整SQL模式。以下是一些常见的场景和建议：

1. 严格模式与非严格模式

如果希望MySQL在执行SQL语句时进行严格的验证，可以使用STRICT_TRANS_TABLES或STRICT_ALL_TABLES模式。这些模式会拒绝插入或更新无效的数据，而不是将其转换为默认值。

如果希望MySQL在执行SQL语句时更加宽松，可以禁用严格模式。例如：

SET sql_mode = '';

2. 兼容性模式

如果希望MySQL兼容其他数据库系统的SQL语法，可以使用ANSI模式。该模式启用了一些与ANSI SQL兼容的选项。例如：

SET sql_mode = 'ANSI';

3. 分组查询模式

如果希望在分组查询中允许不明确的列引用，可以禁用ONLY_FULL_GROUP_BY模式。例如：

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';

4. 零日期模式

如果希望允许使用零日期（0000-00-00），可以禁用NO_ZERO_DATE和NO_ZERO_IN_DATE模式。例如：

SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

常见问题与解决方案

1. 如何恢复默认的SQL模式

如果希望恢复MySQL的默认SQL模式，可以使用以下命令：

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

2. 如何禁用所有SQL模式

如果希望禁用所有SQL模式，可以使用以下命令：

SET sql_mode = '';

3. 如何查看当前会话的SQL模式

可以使用以下命令查看当前会话的SQL模式：

SELECT @@sql_mode;

4. 如何查看全局的SQL模式

可以使用以下命令查看全局的SQL模式：

SELECT @@global.sql_mode;

结论

SQL模式是MySQL中一个非常重要的配置项，它决定了MySQL在执行SQL语句时的行为。通过合理设置SQL模式，可以确保数据的完整性和一致性，同时也可以提高SQL语句的执行效率。本文详细介绍了MySQL默认的SQL模式如何设置，以及如何根据需求调整SQL模式。希望本文能帮助读者更好地理解和配置MySQL的SQL模式。