查看默认的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.01 sec)

设置模式

SET [SESSION|GLOBAL] sql_mode=‘modes’,其中SESSION选项表示只在本次连接中生效;而GLOBAL选项表示在本次连接中并不生效,而对于新的连接则生效,这种方法在MySQL 4.1开始有效。另外,也可以通过使用“–sql-mode=“modes””选项,在MySQL启动时设置sql_mode

SQL 模式功能

  • 验证日期的合法性.例如:
mysql> create table t_mode(t datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> set session sql_mode='ansi'
    -> ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> insert into t_mode values('2007-04-32');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t_mode;
+---------------------+
| t                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode='traditional';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_mode values('2007-04-32');
ERROR 1292 (22007): Incorrect datetime value: '2007-04-32' for column 't' at row 1
mysql>
  • 在insert或update过程中, 如果sql mode处于traditional模式, mod(x,0)就会产生错误. 这是因为traditional也属于严格模式.在非严格模式下mod(x,0)返回的结果是NULL.

  • 启用NO_BACKSLASH_ESCAPES: 使反斜线成为普通字符. 在导入数据时, 如果数据中含有反斜线, 那么启用NO_BACKSLASH_ESCAPES模式可以保证数据的正确性.

  • 启用PIPES_AS_CONCAT: 将|视为字符串连接操作符.在Oracle中, |是字符串连接操作符, 所以在其他数据库中含有|操作符的SQL在MySQL中将无法执行, 为了解决这个问题, MySQL提供了PIPES_AS_CONCAT模式.

  • 在MySQL与其他异构数据库之间有数据迁移的需求, 就可以利用sql mode来帮助迁移.

MySQL SQL-MODE