MySQL中sql_mode参数

1.sql_mode

是一组mysql支持的基本语法及校验规则
查询当前系统sql_mode的设置:

mysql> select @@sql_mode;
±--------------------------------------------------------------------------------------------+
| @@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 |
±----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

全局和会话的概念:

• 全局就是影响整个数据库
• 会话就是影响当前连接的会话,如果会话终止,设置的参数值失效.

静态参数和动态参数:

• 静态参数:只有数据库进行重启才会生效(数据库安装时就要确定好)
• 动态参数:在数据库运行过程中可以随时修改

补充系统变量@@sql_mode

• 在实际工作中，在MySQL中写SQL语句，首先应该设置系统变量@@sql_mode。如果不设置，一些操作会导致非法数据进入到表中，表现出和ORACLE等其它数据库完全不同的行为，为了和ORACLE保持一致，可以在数据库的配置文件my.cnf中添加如下配置(也可以在命令行查看和设置sql_mode变量)（10项）：

  • [mysqld]
    sql_mode=‘STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY, NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, PIPES_AS_CONCAT, ANSI_QUOTES’
    各项的含义如下：

    • STRICT_TRANS_TABLES：
      在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制。必须设置，以后各项可能依赖于该项的设置
    • NO_ENGINE_SUBSTITUTION：
      如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
    • ONLY_FULL_GROUP_BY：
      对于GROUP BY操作，如果在SELECT中出现的单独的列，没有在GROUP BY子句中出现，那么这个SQL是不合法的
    • NO_AUTO_VALUE_ON_ZERO：
      该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了
    • NO_ZERO_IN_DATE：
      在严格模式下，不允许日期和月份为零
    • NO_ZERO_DATE：
      设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
    • ERROR_FOR_DIVISION_BY_ZERO：
      在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
    • NO_AUTO_CREATE_USER：
      禁止GRANT语句创建密码为空的用户
    • PIPES_AS_CONCAT：
      将“||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
    • ANSI_QUOTES：
      启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

  • 查看全局的以及当前会话的sql_mode设置

    • SELECT @@global.sql_mode,@@session.sql_mode;

  • 设置全局的以及当前会话的sql_mode设置：

    • SET @@global.sql_mode=‘STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,no_zero_date,no_zero_in_date,PIPES_AS_CONCAT’;
    • SET @@sql_mode=‘STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,no_zero_date,no_zero_in_date,PIPES_AS_CONCAT’;

  • 注：全局设置影响从那时起新连接的所有客户端，设置SESSION变量只影响当前的客户端

2.sql_mode的常用值举例

Mysql存储引擎的概念:

• Innodb存储引擎(oltp系统,在线交易的)
• Myisam存储引擎(非实时交易)

STRICT_TRANS_TABLES:

• 对于InnoDB引擎来说,当设置sql_mode有该值时,当发现插入数据无法正常插入,会报错,并且回滚所有的数据.(假如一个插入操作往数据表中插入10行数据,但是在第5行数据时,数据不能正常插入,此时会终止插入操作,并且回滚已成功插入的数据.)

• 对于myisam存储引擎:当插入数据是第一行无法插入数据时,报错并且回滚插入数据
  当插入的数据不是第一行无法插入时,此时mysql数据库会将无法插入的值转换为近似值或者发生隐式类型转换进行插入,并且不会报错.

STRICT_ALL_TABLES:

• 对于InnoDB存储引擎:和STRICT_TRANS_TABLES作用一致
• 对于myisam存储引擎:当插入不是第一行报错时,会将报错之前的数据保留,终止之后的插 入操作

NO_ENGINE_SUBSTITUTION:

当存储引擎被禁止或者未解析时,当使用到存储引擎的时候会报错

ONLY_FULL_GROUP_BY:

当select子句中出现的单独列没有出现在group by 子句中,此时会报错

NO_AUTO_CREATE_USER：

禁止创建密码为空的用户.