Skip to content

21. SQL Mode

MySQL 可以运行不同的 SQL Mode（SQL 模式）下。

SQL Mode 定义了MySQL 应支持的 SQL 语法、数据校验等，这样可以更容易地在不同的环境中使用 MySQL。

SQL Mode 常用来解决下面几类问题：

  • 通过设置 SQL Mode，可以完成不同严格程度的数据校验，有效地保障数据准确性。
  • 通过设置 SQL Mode 为 ANSI 模式，来保证大多数 SQL 符合标准的 SQL 语法,这样应用在不同数据库之间进行迁移时，则不需要对业务 SQL 进行较大的修改。
  • 在不同数据库之间进行数据迁移之前， 通过设置 SQL Mode 可以使 MySQL 上的数据更方便地迁移到目标数据库中。

1. 设置mode⚓

默认的SQL mode是没有设置的。

  • 在服务器启动的时候设置：
    • 在命令行使用--sql-mode="mode"选项
    • 在my.cnf（Unix）或my.ini（Windows）中设置sql-mode="mode"
    • 要显式清除SQL模式，请在命令行上使用--sql-mode ="“”"将其设置为空字符串，或在选项文件中使用sql-mode =""将其设置为空字符串。
  • 在运行时改变设置使用：
-- 设置全局的mode，在新连接中生效，本次不生效
SET GLOBAL sql_mode = 'modes';
-- 设置当前连接的mode
SET SESSION sql_mode = 'modes';

注意：

  • 强烈建议在创建使用用户定义分区的表后永远不要更改SQL模式。
  • 在主从模式中使用同一种mode。

2. 查看当前设置⚓

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

3. SQL Mode 的常见功能⚓

  1. 效验日期数据合法性
  2. 在 INSERT 或 UPDATE 过程中， 如果 SQL MODE 处于 严格模式， 运行 MOD(X，0)会产生错误。在非严格模式下 MOD(X，0)返回的结果是 NULL，所以在含有 MOD 的运算中要根据实际情况设定好 sql_mode。
  3. 启用 NO_BACKSLASH_ESCAPES 模式，使反斜线成为普通字符。可以保证数据的正确性。
  4. 启用 PIPES_AS_CONCAT 模式，将||视为字符串连接操作符，在 Oracle 等数据库中， ||被视为字符串的连接操作符。

4. 常用的 SQL Mode⚓

SQL Mode 说明
ANSI 等同于 REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE组合模式，这种模式使语法和行为更符合标准的 SQL
STRICT_TRANS_TABLES 适用于事务表和非事务表，它是严格模式，不允许非法日期，也不允许超过字段长度的值插入字段中，对于插入不正确的值给出错误而不是警告
TRADITIONAL 等同于 STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION 组合模式， 所以它也是严格模式， 对于插入不正确的值是给出错误而不是警告。 可以应用在事务表和非事务表， 用在事务表时， 只要出现错误就会立即回滚

5. SQL Mode 在迁移中的使用⚓

MySQL 提供了很多数据库的组合模式名称， 例如 “ORACLE” 、 “DB2” 等，在异构数据库之间迁移数据时可以尝试使用这些模式来导出适合于目标数据库格式的数据。

在数据迁移过程中， 可以设置 SQL Mode 为 NO_TABLE_OPTIONS 模式， 这样将去掉建表时的选项（engine、default charset等），获得通用的建表脚本。