MySQL的sql_mode说明及设置

sql_model：定义MySQL对约束的响应行为。

sql_model 常用来解决下面几类问题

       (1) 通过设置sql mode, 可以完成不同严格程度的数据校验，有效地保障数据准备性。

　　(2) 通过设置sql model 为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql 进行较大的修改。

　　(3) 在不同数据库之间进行数据迁移之前，通过设置SQL Mode 可以使MySQL 上的数据更方便地迁移到目标数据库中。

sql_mode合理设置

       sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题。

sql_mode值的含义

ONLY_FULL_GROUP_BY	对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中
STRICT_TRANS_TABLES	在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制
NO_ZERO_IN_DATE	在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。
NO_ZERO_DATE	在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告
ERROR_FOR_DIVISION_BY_ZERO	在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL。
NO_AUTO_CREATE_USER	防止GRANT自动创建新用户，除非还指定了密码。
NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。

sql_mode设置和修改 

• 会话修改：

  • mysql> set [session] sql_model='xx_mode'

  • mysql> set @@session.sql_mode='xx_mode'

  • PS：只在当前会话生效

• 全局修改：需要有权限，并且不会立即生效，对以后新建的会话生效（从全局继承的）

  • mysql> set global sql_mode='xx_mode'

  • mysql> set @@global.sql_mode='xx_mode'

  • PS：MySQL重启后失效

• 配置修改：永远生效：

  • eg：vi /etc/my.cnf，在[mysqld]下添加sql_mode='xx'，然后重启数据库

• 常用mode：（阿里服务器默认是：strict_trans_tables）

  • traditional：使用传统模型，不允许对非法值做插入操作

  • strict_trans_tables：对所有支持事物类型的表做严格约束

  • strict_all_tables：对所有表做严格约束

  • 查询当前设置：select @@sql_mode

转载于:https://my.oschina.net/izu/blog/3012533