MySQL8 sql_mode简单介绍

启用了 STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES 则进入严格模式，这两个模式的效果有差别，详见第二节中的描述。严格模式下对DML中存在无效或缺失值将报错，除非使用 insert ignore 或者 update ignore语法。select中的无效值会返回warning。

严格模式适用于以下语句

ALTER TABLE
CREATE TABLE
CREATE TABLE ... SELECT
DELETE (both single table and multiple table)
INSERT
LOAD DATA
LOAD XML
SELECT SLEEP()
UPDATE (both single table and multiple table)
注意：对于存过和函数，如果程序是在严格模式生效时定义的，则调用该函数时上述语句将按严格模式执行，无论当前是否禁用严格模式；反之亦然
mysql> set sql_mode="STRICT_TRANS_TABLES";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE p_mode(p1 INT)
    -> BEGIN
    ->   insert into ta values(p1);
    -> END;
delimiter ;
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> create table ta(id int unsigned);
Query OK, 0 rows affected (0.06 sec)
mysql> call p_mode(5);
Query OK, 1 row affected (0.01 sec)
mysql> select * from ta;
+------+
| id   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)
mysql> call p_mode(-5);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
#禁用严格模式后依然报错
mysql> set sql_mode="";
Query OK, 0 rows affected (0.00 sec)
mysql> call p_mode(-5);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
-------------------------------------
在非严格模式下创建PROCEDURE并执行,插入负数到无符号整型会返回warning 并插入0
mysql> drop PROCEDURE p_mode;
Query OK, 0 rows affected (0.01 sec)
mysql> set sql_mode="";
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE p_mode(p1 INT)
    -> BEGIN
    ->   insert into ta values(p1);
    -> END;
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> truncate table ta;
Query OK, 0 rows affected (0.07 sec)
mysql> call p_mode(-5);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from ta;
+------+
| id   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
#设置严格模式后，再次执行 返回warnings并插入0。这时直接执行insert语句会报错
mysql> set sql_mode="STRICT_TRANS_TABLES";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> call p_mode(-5);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'id' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into ta values(-5);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
#创建时sql_mode可以在information_schema.routines视图中查询
mysql> select ROUTINE_NAME,ROUTINE_TYPE,SQL_MODE from information_schema.routines where routine_name='p_mode';    
+--------------+--------------+-----------------------------------------------------------------------------+
| ROUTINE_NAME | ROUTINE_TYPE | SQL_MODE                                                                    |
+--------------+--------------+-----------------------------------------------------------------------------+
| p_mode       | PROCEDURE    | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO |
+--------------+--------------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
支持ignore关键字的语句包括
CREATE TABLE … SELECT： 如果插入到由 SELECT 生成的表中时，键值冲突的hang被丢弃。
DELETE: 删除过程中忽略错误
INSERT：唯一键冲突的行被丢弃，非法或缺失值会修改为最接近的有效值（LOAD DATA、LOAD XML）
UPDATE：不会更新导致唯一键冲突的行，非法或缺失值会修改为最接近的有效值；
#对于找不到与给定值匹配的分区的分区表INSERT语句，IGNORE将返回warning且不会成功插入该行数据，但不影响其他行的插入
mysql> create table t_par(id int)
    -> partition by range(id)(
    -> partition p1 values less than(0),
    -> partition p2 values less than(10) );
Query OK, 0 rows affected (0.16 sec)
mysql> insert into t_par values(11);
ERROR 1526 (HY000): Table has no partition for value 11
mysql> insert  into t_par values(11),(9);      
ERROR 1526 (HY000): Table has no partition for value 11
------------------------------
mysql> insert ignore into t_par values(11);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------+
| Level   | Code | Message                             |
+---------+------+-------------------------------------+
| Warning | 1526 | Table has no partition for value 11 |
+---------+------+-------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t_par;
Empty set (0.00 sec)
mysql> insert ignore into t_par values(11),(9);
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 1
mysql> select * from t_par;
+------+
| id   |
+------+
|    9 |