因为sql_mode设置的不同可能会出现有些SQL在一个实例可以执行，在另一个实例却不能执行。下面就记录下不同sql_mode的特点：
查看sql_mode
SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;
版本<=5.6.5 支持以下sql_mode
ALLOW_INVALID_DATES | |
ANSI_QUOTES | |
ERROR_FOR_DIVISION_BY_ZERO | |
HIGH_NOT_PRECEDENCE | |
IGNORE_SPACE | |
NO_AUTO_CREATE_USER | |
NO_AUTO_VALUE_ON_ZERO | |
NO_BACKSLASH_ESCAPES | |
NO_DIR_IN_CREATE | |
NO_ENGINE_SUBSTITUTION | |
NO_FIELD_OPTIONS | |
NO_KEY_OPTIONS | |
NO_TABLE_OPTIONS | |
NO_UNSIGNED_SUBTRACTION | |
NO_ZERO_DATE | |
NO_ZERO_IN_DATE | |
ONLY_FULL_GROUP_BY | |
PAD_CHAR_TO_FULL_LENGTH | |
PIPES_AS_CONCAT | |
REAL_AS_FLOAT | |
STRICT_ALL_TABLES | |
STRICT_TRANS_TABLES |
>5.6.5
ALLOW_INVALID_DATES | |
ANSI_QUOTES | |
ERROR_FOR_DIVISION_BY_ZERO | |
HIGH_NOT_PRECEDENCE | |
IGNORE_SPACE | |
NO_AUTO_CREATE_USER | |
NO_AUTO_VALUE_ON_ZERO | |
NO_BACKSLASH_ESCAPES | |
NO_DIR_IN_CREATE | |
NO_ENGINE_SUBSTITUTION | |
NO_FIELD_OPTIONS | |
NO_KEY_OPTIONS | |
NO_TABLE_OPTIONS | |
NO_UNSIGNED_SUBTRACTION | |
NO_ZERO_DATE | |
NO_ZERO_IN_DATE | |
ONLY_FULL_GROUP_BY | |
PAD_CHAR_TO_FULL_LENGTH | |
PIPES_AS_CONCAT | |
REAL_AS_FLOAT | |
STRICT_ALL_TABLES | |
STRICT_TRANS_TABLES |
复合sql_mode
ANSI REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.
Equivalent to
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.
一般情况，月份范围是1-12，日范围是1-31，如果在mysql中插入正常范围以外的日期会怎么样呢？这取决于数据库实例设置的sql_mode。
ALLOW_INVALID_DATES 表示可以插入非法的日期，在对日期进行插入的时候不进行合法检验。但是对timestamp的合法性还是要进行检验的。
create table t_sql_mode (
id int not null primary key auto_increment, tdate date , tdatetime datetime default now(), ttimestamp timestamp default now()
) engine=innodb;
root@localhost [test]>set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>insert into t_sql_mode(tdate) values('2014-02-31');
Query OK, 1 row affected, 1 warning (0.00 sec)
root@localhost [test]>insert into t_sql_mode(tdate,tdatetime,ttimestamp) values('2014-02-31','2016-19-90 00:00:00','2016-19-90 00:00:00');
Query OK, 1 row affected, 3 warnings (0.00 sec)
root@localhost [test]>show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'tdate' at row 1 |
| Warning | 1265 | Data truncated for column 'tdatetime' at row 1 |
| Warning | 1265 | Data truncated for column 'ttimestamp' at row 1 |
+---------+------+-------------------------------------------------+
3 rows in set (0.00 sec)
root@localhost [test]>select * from t_sql_mode;
+----+------------+---------------------+---------------------+
| id | tdate | tdatetime | ttimestamp |
+----+------------+---------------------+---------------------+
| 2 | 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 3 | 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+------------+---------------------+---------------------+
root@localhost [test]>set sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>insert into t_sql_mode(tdate) values('2014-02-31');
ERROR 1292 (22007): Incorrect date value: '2014-02-31' for column 'tdate' at row 1
root@localhost [test]>set sql_mode='STRICT_TRANS_TABLES,ALLOW_INVALID_DATES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [test]>insert into t_sql_mode(tdate,tdatetime,ttimestamp) values('2014-02-31','2016-19-90 00:00:00','2016-19-90 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '2016-19-90 00:00:00' for column 'tdatetime' at row 1
root@localhost [test]>insert into t_sql_mode(tdate,tdatetime,ttimestamp) values('2014-02-31','2016-09-20 00:00:00','2016-09-20 00:00:00');
Query OK, 1 row affected (0.01 sec)
root@localhost [test]>select * from t_sql_mode;
+----+------------+---------------------+---------------------+
| id | tdate | tdatetime | ttimestamp |
+----+------------+---------------------+---------------------+
| 2 | 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 3 | 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 4 | 2014-02-31 | 2016-09-20 00:00:00 | 2016-09-20 00:00:00 |
+----+------------+---------------------+---------------------+
3 rows in set (0.00 sec)
root@localhost [test]>
mysql5.7.14 & mariadb10.0.21
如果sql_mode为空，允许插入任何数值的日期值，但是会有warning，并且将非法日期转成0000或者0000-00-00 00:00:00
如果为严格模式，且没有ALLOW_INVALID_DATES，不允许插入任何非法日期值，
如果为严格模式，且指定ALLOW_INVALID_DATES，允许插入非法的date数值，但是不允许插入非法的datetime和timestamp。
这中模式下，mysql会将双引号作为标示符，类似于常用的 "`" ,不能将双引号用来引用字符串，请看例子：
root@localhost [test]>set sql_mode='';
root@localhost [test]>select `id`,`tdate` from t_sql_mode;
+----+------------+
| id | tdate |
+----+------------+
| 2 | 0000-00-00 |
| 3 | 0000-00-00 |
| 4 | 2014-02-31 |
+----+------------+
root@localhost [test]>select "id","tdate" from t_sql_mode;
+----+-------+
| id | tdate |
+----+-------+
| id | tdate |
| id | tdate |
| id | tdate |
+----+-------+
3 rows in set (0.00 sec)
root@localhost [test]>select 'id','tdate' from t_sql_mode;
+----+-------+
| id | tdate |
+----+-------+
| id | tdate |
| id | tdate |
| id | tdate |
+----+-------+
3 rows in set (0.00 sec)
root@localhost [test]>set sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>select `id`,`tdate` from t_sql_mode;
+----+------------+
| id | tdate |
+----+------------+
| 2 | 0000-00-00 |
| 3 | 0000-00-00 |
| 4 | 2014-02-31 |
+----+------------+
3 rows in set (0.00 sec)
root@localhost [test]>select 'id','tdate' from t_sql_mode;
+----+-------+
| id | tdate |
+----+-------+
| id | tdate |
| id | tdate |
| id | tdate |
+----+-------+
3 rows in set (0.00 sec)
root@localhost [test]>select "id","tdate" from t_sql_mode;
+----+------------+
| id | tdate |
+----+------------+
| 2 | 0000-00-00 |
| 3 | 0000-00-00 |
| 4 | 2014-02-31 |
+----+------------+
3 rows in set (0.00 sec)
控制在除0时候的动作，
严格模式
insert，update类
select 类
严格模式|insert，update类
严格模式 ERROR_FOR_DIVISION_BY_ZERO 除0，返回null，没有warning
非严格模式，ERROR_FOR_DIVISION_BY_ZERO 除0，返回null，提示warning
严格模式， ERROR_FOR_DIVISION_BY_ZERO 除0，会返回错误，但是如果使用了INSERT IGNORE and UPDATE IGNORE 返回null，提示warning
CREATE TABLE `division_zero` (
`z` int(11) DEFAULT NULL
) ENGINE=InnoDB
root@localhost [test]>show variables like 'sql_mode';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| sql_mode | STRICT_ALL_TABLES |
+---------------+-------------------+
1 row in set (0.00 sec)
root@localhost [test]>insert into division_zero values(1/0);
Query OK, 1 row affected (0.02 sec)
root@localhost [test]>select * from division_zero;
+------+
| z |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
root@localhost [test]>set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [test]>insert into division_zero values(1/0);
Query OK, 1 row affected, 1 warning (0.02 sec)
root@localhost [test]>show warnings;
+---------+------+---------------+
| Level | Code | Message |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)
root@localhost [test]>select * from division_zero;
+------+
| z |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.00 sec)
root@localhost [test]>set sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [test]>insert into division_zero values(1/0);
ERROR 1365 (22012): Division by 0
root@localhost [test]>
root@localhost [test]>insert ignore into division_zero values(1/0),(2);
Query OK, 2 rows affected, 1 warning (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 1
root@localhost [test]>show warnings;
+---------+------+---------------+
| Level | Code | Message |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)
root@localhost [test]>select * from division_zero;
+------+
| z |
+------+
| NULL |
| NULL |
| NULL |
| 2 |
+------+
4 rows in set (0.00 sec)
root@localhost [test]>
运行select，都会返回null，如果ERROR_FOR_DIVISION_BY_ZERO 会提示warning，没有则只返回null，没有waring。
不管是什么模式。
root@localhost [test]>set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [test]>select 12/0;
+------+
| 12/0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
root@localhost [test]>set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [test]>select 1/0;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
root@localhost [test]>set sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [test]>select 1/0;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)
root@localhost [test]>show warnings;
+---------+------+---------------+
| Level | Code | Message |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)
控制not 运算符的优先级。
默认情况not是最后进行运算的，例如NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c).但是在一些老版本中NOT a BETWEEN b AND c are parsed as (NOT a) BETWEEN b AND c).
root@localhost [test]>set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>select not 1 between -5 and 5;
+------------------------+
| not 1 between -5 and 5 |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)
root@localhost [test]>set sql_mode='HIGH_NOT_PRECEDENCE';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>select not 1 between -5 and 5;
+------------------------+
| not 1 between -5 and 5 |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
root@localhost [test]>
允许函数名和'('之间有空格，这会将内建的方法和存储过程名作为保留字。这就造成，使用跟方法名一样的标示符时必须使用特殊的符号包含，比如`.
count()是内建函数，所以使用count作为表名的时候就会出错。
root@localhost [test]>set sql_mode='IGNORE_SPACE';
root@localhost [test]>create table count ( i int);
ERROR 1064 (42000): You have an error in your SQL syntax;
root@localhost [test]>create table `count` ( i int);
Query OK, 0 rows affected (0.03 sec)
root@localhost [test]>select count (*) from test; #这个count后面有一个空格的
+-----------+
| count (*) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
root@localhost [test]>drop table count;
root@localhost [test]>set sql_mode='';
root@localhost [test]>create table count( i int); ＃这个count后面没有空格
ERROR 1064 (42000): You have an error in your SQL syntax;
root@localhost [test]>create table count ( i int); ＃这个count后面有空格
Query OK, 0 rows affected (0.02 sec)
root@localhost [test]>select count (*) from test;
ERROR 1064 (42000): You have an error in your SQL syntax;
root@localhost [test]>select count(*) from test;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00
不允许grant语句自动创建没有密码的账户。
5.7之后，grant不再能创建用户。
root@localhost [test]>set sql_mode='';
root@localhost [test]>grant select on test.* to 'test1'@'%';
root@localhost [test]>select user,host,authentication_string from mysql.user;
+-----------+-----------+-------------------------------------------+
| user | host | authentication_string |
+-----------+-----------+-------------------------------------------+
| root | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root | % | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| test | % | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| test1 | % | |
+-----------+-----------+-------------------------------------------+
root@localhost [test]>set sql_mode='NO_AUTO_CREATE_USER';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [test]>grant select on test.* to 'test2'@'%';
ERROR 1133 (42000): Can't find any matching row in the user table
root@localhost [test]>grant select on test.* to 'test2'@'%' identified by 'mysql';
Query OK, 0 rows affected, 1 warning (0.02 sec)
root@localhost [test]>select user,host,authentication_string from mysql.user;
+-----------+-----------+-------------------------------------------+
| user | host | authentication_string |
+-----------+-----------+-------------------------------------------+
| root | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root | % | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| test | % | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| test1 | % | |
| test2 | % | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+-----------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)
对于自增列，插入null和0都会自增，如果就是想插入0,可以将sql_mode设置成这个模式，但是不建议插入0.因为mysqldump导出导入的时候，可能就会将0视为自自增. 可以在导入前设置NO_AUTO_VALUE_ON_ZERO。不过现在mysqldump中都会有以下信息：
开始：
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
结尾：
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
create table test_autoinc (id int auto_increment primary key,score int);
root@localhost [test]>insert into test_atuoinc values(0,100);
root@localhost [test]>select * from test_autoinc;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
+----+-------+
1 row in set (0.01 sec)
root@localhost [test]>set sql_mode='NO_AUTO_VALUE_ON_ZERO';
root@localhost [test]>insert into test_autoinc values(0,50);
root@localhost [test]>select * from test_autoinc;
+----+-------+
| id | score |
+----+-------+
| 0 | 50 |
| 1 | 100 |
+----+-------+
2 rows in set (0.00 sec)
root@localhost [test]>insert into test_autoinc values(null,80);
root@localhost [test]>select * from test_autoinc;
+----+-------+
| id | score |
+----+-------+
| 0 | 50 |
| 1 | 100 |
| 2 | 80 |
+----+-------+
3 rows in set (0.00 sec)
在字符串中,反斜杠跟普通字符一样，没有特殊意义。
root@localhost [test]>set sql_mode='';
root@localhost [test]>select 'test\ntest';
+-----------+
| test
test |
+-----------+
| test
test |
+-----------+
1 row in set (0.00 sec)
root@localhost [test]>set sql_mode='NO_BACKSLASH_ESCAPES';
root@localhost [test]>select 'test\ntest';
+------------+
| test\ntest |
+------------+
| test\ntest |
+------------+
1 row in set (0.00 sec)
忽略建表时候指定的INDEX DIRECTORY and DATA DIRECTORY。这个在主从复制中的从库比较有用。
控制create table和alter table指定的引擎不可用或没有编译进的时候的表现。默认mode包含NO_ENGINE_SUBSTITUTION
禁用NO_ENGINE_SUBSTITUTION create table 的时候如果指定引擎不可用，就会使用默认引擎，并提示warning，对于alter，会提示warning，引擎不会改变。
如果引擎不可用，create和alter都会报错
root@localhost [test]>set sql_mode='';
root@localhost [test]>create table test_engine (i int ) engine=FEDERATED;
root@localhost [test]>show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1286 | Unknown storage engine 'FEDERATED' |
| Warning | 1266 | Using storage engine InnoDB for table 'test_engine' |
+---------+------+-----------------------------------------------------+
2 rows in set (0.00 sec)
root@localhost [test]>set sql_mode='NO_ENGINE_SUBSTITUTION ';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>create table test_engine2 (i int ) engine=FEDERATED;
ERROR 1286 (42000): Unknown storage engine 'FEDERATED'
root@localhost [test]>
oracle 的sql_mode设置
Do not print MySQL-specific column options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.
oracle 的sql_mode设置
Do not print MySQL-specific index options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.
Do not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.
root@localhost [test]>set sql_mode='NO_TABLE_OPTIONS';
root@localhost [test]>show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`)
)
1 row in set (0.00 sec)
在计算两个整数差值的时候，如果数值都是无符号数，默认结果也是无符号数，如果差值是负数，那么就会报错。
如果NO_UNSIGNED_SUBTRACTION，则允许结果是负数.
root@localhost [test]>SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
root@localhost [test]>SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
root@localhost [test]>SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| -1 |
+-------------------------+
如果是更新语句
NO_UNSIGNED_SUBTRACTION 严格模式 CAST(0 AS UNSIGNED) - 1 报错，列值不会改变
NO_UNSIGNED_SUBTRACTION 非严格模式 CAST(0 AS UNSIGNED) - 1 ＝0
当使用NO_UNSIGNED_SUBTRACTION的时候，不管进行计算的列类型是否是有符号还是无符号，结果都是有符号的。
root@localhost [test]>desc test_int;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
root@localhost [test]>update test_int set id = CAST(0 AS UNSIGNED) - 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
root@localhost [test]>select * from test_int;
+------+
| id |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
root@localhost [test]>set sql_mode='STRICT_ALL_TABLES,NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [test]>update test_int set id = CAST(0 AS UNSIGNED) - 1;
ERROR 1264 (22003): Out of range value for column 'id' at row 1
root@localhost [test]>
mysql> SET sql_mode=''; mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL); mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test; mysql> DESCRIBE t1; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | c2 | bigint(21) unsigned | NO | | 0 | | +-------+---------------------+------+-----+---------+-------+ mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION'; mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test; mysql> DESCRIBE t2; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c2 | bigint(21) | NO | | 0 | | +-------+------------+------+-----+---------+-------+
'0000-00-00' 允许插入，有警告
INSERT IGNORE 或 UPDATE IGNORE,允许插入'0000-00-00' ，有警告
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>insert into test_date values(null,'0000-00-00');
Query OK, 1 row affected (0.01 sec)
root@localhost [test]>select * from test_date;
+----+------------+
| id | date1 |
+----+------------+
| 1 | 0000-00-00 |
+----+------------+
1 row in set (0.00 sec)
root@localhost [test]>set sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [test]>insert into test_date values(null,'0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date1' at row 1
root@localhost [test]>
root@localhost [test]>set sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';
root@localhost [test]>select * from test_date;
+----+------------+
| id | date1 |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 0000-00-00 |
| 3 | 0000-00-00 |
+----+------------+
3 rows in set (0.00 sec)
root@localhost [test]>update ignore test_date set id = 100,date1='0000-00-00' where id = 1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
root@localhost [test]>show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1264 | Out of range value for column 'date1' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
root@localhost [test]>select * from test_date;
+-----+------------+
| id | date1 |
+-----+------------+
| 2 | 0000-00-00 |
| 3 | 0000-00-00 |
| 100 | 0000-00-00 |
+-----+------------+
控制有年份但是月份和日期都是0的请，例如'2010-00-01' 或 '2010-01-00', 但是没有控制'0000-00-00'. 这个由上面NO_ZERO_DATE的控制。
没有NO_ZERO_IN_DATE，可以插入2010-00-01或者2010-01-00，没有警告
有NO_ZERO_IN_DATE ，可以插入2010-00-01或者2010-01-00，有警告
严格模式，不允许插入2010-00-01或者2010-01-00，会有报错。如果是INSERT IGNORE 或 UPDATE IGNORE,允许插入，有警告
拒绝在select，having，order by 中使用没有在group by中没有出现非聚合列。
拒绝在select，having，order by 中使用没有在group by中没有出现非聚合列。
root@localhost [test]>setsql_mode='ONLY_FULL_GROUP_BY';
root@localhost [test]>select id,name,updated_at,created_atfrom testtime where updated_at > '2016-09-13 23:06:20' group by updated_atorder by created_at;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause andcontains nonaggregated column 'test.testtime.id' which is not functionallydependent on columns in GROUP BY clause; this is incompatible withsql_mode=only_full_group_by
root@localhost [test]>select id,name,updated_at,created_at from testtimewhere updated_at > '2016-09-13 23:06:20' group by updated_at;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause andcontains nonaggregated column 'test.testtime.id' which is not functionallydependent on columns in GROUP BY clause; this is incompatible withsql_mode=only_full_group_by
root@localhost [test]>select updated_at,created_at from testtime whereupdated_at > '2016-09-13 23:06:20' group by updated_at;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause andcontains nonaggregated column 'test.testtime.created_at' which is notfunctionally dependent on columns in GROUP BY clause; this is incompatible withsql_mode=only_full_group_by
root@localhost [test]>select updated_at from testtime where updated_at >'2016-09-13 23:06:20' group by updated_at;
+---------------------+
| updated_at |
+---------------------+
| 2016-09-13 23:06:21 |
| 2016-09-13 23:06:24 |
| 2016-09-13 23:53:49 |
| 2016-09-13 23:53:50 |
| 2016-09-13 23:53:51 |
+---------------------+
root@localhost [test]>set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>select id,name,updated_at,created_at from testtimewhere updated_at > '2016-09-13 23:06:20' group by updated_at order bycreated_at;
+----+------+---------------------+---------------------+
| id | name | updated_at | created_at |
+----+------+---------------------+---------------------+
| 3 | name | 2016-09-13 23:06:21 | 2016-09-13 23:06:21 |
| 5 | name | 2016-09-13 23:06:24 | 2016-09-13 23:06:24 |
| 6 | name | 2016-09-13 23:53:49 | 2016-09-13 23:53:49 |
| 7 | name | 2016-09-13 23:53:50 | 2016-09-13 23:53:50 |
| 9 | name | 2016-09-13 23:53:51 | 2016-09-13 23:53:51 |
+----+------+---------------------+---------------------+
5 rows in set (0.00 sec)
root@localhost [test]>
默认情况会将char之后的空格过滤掉，使用这个模式不会过滤char中用来补齐的空格。
root@localhost [test]>desc test_char;
+-------+----------+------+-----+----------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+----------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | name | |
+-------+----------+------+-----+----------------------+----------------+
root@localhost [test]>set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>select id,name,char_length(name),length(name) fromtest_char;
+----+------+-------------------+--------------+
| id | name | char_length(name) | length(name) |
+----+------+-------------------+--------------+
| 1 | name | 4 | 4 |
+----+------+-------------------+--------------+
1 row in set (0.00 sec)
root@localhost [test]>set sql_mode='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>select id,name,char_length(name),length(name) fromtest_char;
+----+----------------------+-------------------+--------------+
| id | name |char_length(name) | length(name) |
+----+----------------------+-------------------+--------------+
| 1 | name | 20 | 20 |
+----+----------------------+-------------------+--------------+
1 row in set (0.00 sec)
将||作为连接字符串操作，跟concat类似，而不是or操作。
root@localhost [test]>set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>select 1||2;
+------+
| 1||2 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
root@localhost [test]>set sql_mode='PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]>select 1||2;
+------+
| 1||2 |
+------+
| 12 |
+------+
1 row in set (0.00 sec)
默认real代表double，可以通过REAL_AS_FLOAT让real代表float. real有实数的意思
root@localhost [test]>set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [test]>create table test_real ( id int,score real);
Query OK, 0 rows affected (0.05 sec)
root@localhost [test]>desc test_real;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| score | double | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
root@localhost [test]>alter table test_real modifyscore real;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost [test]>set sql_mode='REAL_AS_FLOAT';
root@localhost [test]>desc test_real;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| score | float | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
所有的表都是严格模式，包括ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE
所有支持事务的引擎都是严格模式，包括：ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE
严格模式：
严格模式控制在非法数值插入或者在使用insert和update进行数据更改时数据丢失的情况下的结果。非法数据有各种情况，比如数据类型不对，或超出类型范围。
一个没有指定默认值的列，在进行没有值插入的时候就会有数据丢失，（对于null列，如果没有值，就会插入null），严格模式还会影响create table等。
如果不是严格模式，mysql会对插入非法数据或丢失数据情况提出警告。在严格模式下就会报错，不过可以通过insert ignore和update ignore代替。
对于select语句，非法值只会有警告，不会报错。
如果不是严格模式，在尝试创建超过key长度的key时，只会提示警告，然后自动保留到最大值。
复合模式：
为了在指定模式的时候更加方便，mysql提供了一些模式的组合。
ANSI：
REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and (as of MySQL 5.7.5) ONLY_FULL_GROUP_BY.
DB2:
PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.
PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.
MYSQL323, HIGH_NOT_PRECEDENCE.
PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.
PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.
BeforeMySQL 5.7.4, and in MySQL 5.7.8 and later, TRADITIONAL is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, andNO_ENGINE_SUBSTITUTION.
FromMySQL 5.7.4 though 5.7.7, TRADITIONAL is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION. The NO_ZERO_IN_DATE, NO_ZERO_DATE, andERROR_FOR_DIVISION_BY_ZERO modes are not named because inthose versions their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). Thus, the effects of TRADITIONAL are the same in all MySQL 5.7versions (and the same as in MySQL 5.6).
905
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
