MySQL的SQL_MODE

231次阅读

共计 3786 个字符，预计需要花费 10 分钟才能阅读完成。

在 MySQL 下 MySQL 所使用的模型 SQL_MODE 对 MySQL 来说至关重要，SQL_MODE 即用来定义字符超出的操作和模仿别的数据库的类型，平时通过合理的设置可以 MySQL 能够应用于各种场景下，但是如果设置错误 MySQL 的 SQL_MODE 也会带来很大的影响，在这里就简要的介绍下 MySQL 下的 SQL_MODE，MySQL 的 SQL_MODE 变量时针对 session 的平时建议设置全局，在设置完后，开启一个新的 session 后所指定的 SQL_MODE 就生效了，同大多数的 MySQL 变量一样 SQL_MODE 仅限于本次 MySQL 进程，所以在设置完 SQL_MODE 的全局变量后需要把设置的值写入 my.cnf 或 my.ini 中使得下一次如果重启 MySQL 后 SQL_MODE 生效，查看 MySQL 当前所使用的 SQL_MODE 很简单：
mysql> SET GLOBAL sql_mode = ‘NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_VALUE_ON_ZERO’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

[root@localhost ~]# vim /etc/my.cnf #在设置完后需要记得在 my.cnf 或 my.ini 中修改
…略…
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_VALUE_ON_ZERO
…略…而 MySQL 的 SQL_MODE 如下：
REAL_AS_FLOAT: 将 REAL 视为 FLOAT 的同义词，而不是 DOUBLE 的同义词
PIPES_AS_CONCAT: 将 ’||’ 视为字符串连接操作符（+）(同 CONCAT())，而不视为 OR
ANSI_QUOTES: 使用 ANSI 标准，即在 '”‘ 视为识别符引号(‘`’ 反引号字符)
IGNORE_SPACE: 忽略空格，即在使用函数时可以在函数名与'(‘ 之间可以有空格，不建议使用这种模式
ONLY_FULL_GROUP_BY:group 规则，即 select 的列都要在 group 中, 或者本身是聚合列(SUM,AVG,MAX,MIN) 才行，建议加上，这样在平时就不会出现很多没有意义错误的 group by 结果查询出来
NO_UNSIGNED_SUBTRACTION: 数值无符号化，即可以在 int 的长度不够的情况下把负值当成普通字符，不建议使用，在 int 不够的情况下可以改成 bigint 就可以解决
NO_DIR_IN_CREATE: 在创建表时忽视所有 INDEX DIRECTORY 和 DATA DIRECTORY 的选项，该选项对从复制服务器有用
NO_KEY_OPTIONS: 不要在 SHOW CREATE TABLE 的输出中打印 MySQL 专用索引选项，该模式在可移植模式（portability mode）下用于 mysqldump
NO_TABLE_OPTIONS: 不要在 SHOW CREATE TABLE 的输出中打印 MySQL 专用表引选项（例如 ENGINE），该模式在可移植模式（portability mode）下用于 mysqldump
NO_FIELD_OPTIONS: 不要在 SHOW CREATE TABLE 的输出中打印 MySQL 专用列选项，该模式在可移植模式（portability mode）下用于 mysqldump
NO_AUTO_VALUE_ON_ZERO: 使用 MySQL 的自增字段时，MySQL 的自增计数器从 0 开始自增，比较如果没有加默认是从 1 开始自增的，如果插入和建表语句中有从 0 开始会从 MySQL 中随机插入自增值，这个模式建议开启
NO_BACKSLASH_ESCAPES: 反斜杠 ’\’ 作为普通字符而非转义符
STRICT_TRANS_TABLES: 仅对支持事务的表的严格模式
STRICT_ALL_TABLES: 对所有表使用严格模式
NO_ZERO_IN_DATE: 在严格模式下，不允许日期和月份为零。如“2011-00-01”和“2011-01-00”这样的格式是不允许的
NO_ZERO_DATE: 在严格模式下，不可以插入形如“0000-00-00 00:00:00”的非法日期
ALLOW_INVALID_DATES: 合法日期检查
ERROR_FOR_DIVISION_BY_ZERO: 在 INSERT 或 UPDATE 过程中，如果数据被零除(或 MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，那么数据被零除时 MySQL 返回 NULL。如果用到 INSERT IGNORE 或 UPDATE IGNORE 中，MySQL 生成被零除警告，但操作结果为 NULL
HIGH_NOT_PRECEDENCE: 操作符的优先顺序是表达式
NO_ENGINE_SUBSTITUTION: 如果需要的存储引擎被禁用或未编译，可以防止自动替换存储引擎
PAD_CHAR_TO_FULL_LENGTH: 对于 CHAR 类型字段，不要截断空洞数据(值为 0x20 的数据)
POSTGRESQL: 使用 PostgreSQL 中的标准模式，同 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,POSTGRESQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
Oracle：使用 Oracle 中的标准模式，同 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
MSSQL: 使用 msSQL 中的标准模式，同 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
DB2: 使用 DB2 中的标准模式，同 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,DB2,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
MAXDB: 使用 MaxDB 中的标准模式，同 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
MYSQL323: 使用 MySQL323 的标准模式，同 NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCE
MYSQL40: 使用 MySQL323 的标准模式，同 NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCE
ANSI: 使用 ANSI 的标准模式，同 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI
TRADITIONAL: 使用传统模式，同 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 总之，在平时需要正确的设置合理的 SQL_MODE 才能使 MySQL 到达我们所需要的效果，平如果没有设置，在 MySQL 下会有很多不合理的 SQL 被放行，在生产环境中是不被允许的，在生产环境中如果没有特殊要求的建议用以下模式：