赞
踩
最近开发的项目上线,当然了,在开发环境和测试环境都是测试过的。由于是新项目,所以线上环境的部署得从0开始。
当把所有的环境都搭建成功后,项目运行起来后,发现MySQL
老是报一些语法错误,后来发现是每个环境的MySQL
的SQL_MODE
模式不同。下面就来学习一下SQL_MODE
吧。
SQL_MODE
是MySQL
中的一个系统变量(variable),可由多个MODE
组成,每个MODE
控制一种行为,如是否允许除数为0,日期中是否允许’0000-00-00’值。
下面来看三个简单的Demo,MySQL
版本为 5.6。
mysql> create table t1(c1 datetime);
Query OK, 0 rows affected (0.16 sec)
mysql> insert into t1 values('2019-02-29');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t1;
+---------------------+
| c1 |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> create table t2(c1 varchar(10)); Query OK, 0 rows affected (0.06 sec) mysql> insert into t2 values('a'),('b'),('c'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t2; +------+ | c1 | +------+ | a | | b | | c | +------+ 3 rows in set (0.00 sec) mysql> alter table t2 modify column c1 int; Query OK, 3 rows affected, 3 warnings (0.05 sec) Records: 3 Duplicates: 0 Warnings: 3 mysql> show warnings; +---------+------+-------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------+ | Warning | 1366 | Incorrect integer value: 'a' for column 'c1' at row 1 | | Warning | 1366 | Incorrect integer value: 'b' for column 'c1' at row 2 | | Warning | 1366 | Incorrect integer value: 'c' for column 'c1' at row 3 | +---------+------+-------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from t2; +------+ | c1 | +------+ | 0 | | 0 | | 0 | +------+ 3 rows in set (0.00 sec)
mysql> create table t3(id int not null,c1 varchar(10)); Query OK, 0 rows affected (0.05 sec) mysql> insert into t3 values(null,'a'); ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into t3(c1) values('a'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1364 | Field 'id' doesn't have a default value | +---------+------+-----------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t3; +----+------+ | id | c1 | +----+------+ | 0 | a | +----+------+ 1 row in set (0.00 sec)
为什么会出现上面这三种情况呢?这个就与SQL_MODE
有关。
在MySQL
5.6中, SQL_MODE
的默认值为"NO_ENGINE_SUBSTITUTION
",非严格模式。
在这种模式下,在进行数据变更操作时,如果涉及的列中存在无效值(如日期不存在,数据类型不对,数据溢出),只会提示"Warning",并不会报错。
那么如何规避上述问题呢?答案是需开启SQL_MODE
的严格模式。
所谓严格模式,即SQL_MODE
中开启了STRICT_ALL_TABLES
或STRICT_TRANS_TABLES
。
还是上面的Demo,看看严格模式下,MySQL
的处理逻辑。
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values('2019-02-29');
ERROR 1292 (22007): Incorrect datetime value: '2019-02-29' for column 'c1' at row 1
mysql> alter table t2 modify column c1 int;
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'c1' at row 1
mysql> insert into t3(c1) values('a');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
同样的SQL,在严格模式下,直接提示"ERROR",而不是"Warning"。
同是严格模式,那STRICT_ALL_TABLES
或STRICT_TRANS_TABLES
有什么区别呢?
STRICT_TRANS_TABLES
只对事务表开启严格模式,STRICT_ALL_TABLES
是对所有表开启严格模式,不仅仅是事务表,还包括非事务表。
看下面这个测试,对myisam表插入3条数据,其中,第3条数据是空字符串,与定义的int类型不匹配。
mysql> create table t (c1 int) engine=myisam; Query OK, 0 rows affected (0.00 sec) mysql> set session sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values (1),(2),(''); Query OK, 3 rows affected, 1 warning (0.00 sec) Records: 3 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------+ | Warning | 1366 | Incorrect integer value: '' for column 'c1' at row 3 | +---------+------+------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t; +------+ | c1 | +------+ | 1 | | 2 | | 0 | +------+ 3 rows in set (0.00 sec) mysql> set session sql_mode='STRICT_ALL_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values (1),(2),(''); ERROR 1366 (HY000): Incorrect integer value: '' for column 'c1' at row 3
可以看到,在表为myisam存储引擎的情况下,只有开启STRICT_ALL_TABLES
才会报错。
MySQL 5.5:空
MySQL 5.6:
NO_ENGINE_SUBSTITUTION
MySQL 5.7:
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
MySQL 8.0:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL_MODE
既可在全局级别修改,又可在会话级别修改。可指定多个MODE
,MODE
之间用逗号隔开。
全局级别
set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';
会话级别
set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';
在严格模式下,对于日期的检测较为严格,其必须有效。若开启该MODE
,对于month和day的检测会相对宽松。其中,month只需在1-12之间,day只需在1-31之间,而不管其是否有效,如下面的’2004-02-31’。
mysql> create table t (c1 datetime); Query OK, 0 rows affected (0.21 sec) mysql> set session sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values('2004-02-31'); ERROR 1292 (22007): Incorrect datetime value: '2004-02-31' for column 'c1' at row 1 mysql> set session sql_mode='STRICT_TRANS_TABLES,ALLOW_INVALID_DATES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values('2004-02-31'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +---------------------+ | c1 | +---------------------+ | 2004-02-31 00:00:00 | +---------------------+ 1 row in set (0.00 sec)
注意,该MODE
只适用于DATE
和DATETIME
,不适用于TIMESTAMP
。
在MySQL
中,对于关键字和保留字,是不允许用做表名和字段名的。如果一定要使用,必须使用反引号("`")进行转义。
mysql> create table order (id int);
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near 'order (id int)' at line 1
mysql> create table `order` (id int);
Query OK, 0 rows affected (0.12 sec)
若开启该MODE
,则双引号,同反引号一样,可对关键字和保留字转义。
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> create table "order" (c1 int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"order" (c1 int)' at line 1
mysql> set session sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql> create table "order" (c1 int);
Query OK, 0 rows affected (0.17 sec)
需要注意的是,在开启该MODE
的情况下,不能再用双引号来引字符串。
该MODE
决定除数为0的处理逻辑,实际效果还取决于是否开启严格模式。
1)、开启严格模式,且开启该MODE,插入1/0,会直接报错。
mysql> create table t (c1 double);
Query OK, 0 rows affected (0.04 sec)
mysql> set session sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t values(1/0);
ERROR 1365 (22012): Division by 0
2)、只开启严格模式,不开启该MODE
,允许1/0的插入,且不提示warning,1/0最后会转化为NULL。
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t values(1/0);
Query OK, 1 row affected (0.07 sec)
mysql> select * from t;
+------+
| c1 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
3)、不开启严格模式,只开启该MODE
,允许1/0的插入,但提示warning。
4)、不开启严格模式,也不开启该MODE,允许1/0的插入,且不提示warning,同2一样。
默认情况下,NOT的优先级低于比较运算符。但在某些低版本中,NOT的优先级高于比较运算符。
看看两者的区别。
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select not 1 < -1; +------------+ | not 1 < -1 | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql> set session sql_mode='HIGH_NOT_PRECEDENCE'; Query OK, 0 rows affected (0.00 sec) mysql> select not 1 < -1; +------------+ | not 1 < -1 | +------------+ | 0 | +------------+ 1 row in set (0.00 sec)
在sql_mode
为空的情况下, not 1 < -1相当于not (1 < -1),如果设置了'HIGH_ NOT_PRECEDENCE'
,则相当于(not 1) < -1。
默认情况下,函数名和左括号(“(”)之间不允许存在空格。若开启该MODE,则允许。
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from t; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) mysql> select count (*) from t; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from t' at line 1 mysql> set session sql_mode='IGNORE_SPACE'; Query OK, 0 rows affected (0.01 sec) mysql> select count (*) from t; +-----------+ | count (*) | +-----------+ | 2 | +-----------+ 1 row in set (0.01 sec)
默认情况下,在对自增主键插入NULL或0时,会自动生成下一个值。若开启该MODE,当插入0时,并不会自动生成下一个值。
如果表中自增主键列存在0值,在进行逻辑备份还原时,可能会导致数据不一致。所以mysqldump在生成备份数据之前,会自动开启该MODE,以避免数据不一致的情况。
mysql> create table t (id int auto_increment primary key); Query OK, 0 rows affected (0.11 sec) mysql> set session sql_mode=''; Query OK, 0 rows affected (0.01 sec) mysql> insert into t values (0); Query OK, 1 row affected (0.04 sec) mysql> select * from t; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> set session sql_mode='NO_AUTO_VALUE_ON_ZERO'; Query OK, 0 rows affected (0.02 sec) mysql> insert into t values (0); Query OK, 1 row affected (0.09 sec) mysql> select * from t; +----+ | id | +----+ | 0 | | 1 | +----+ 2 rows in set (0.00 sec)
默认情况下,反斜杠“\”会作为转义符,若开启该MODE,则反斜杠“\”会作为一个普通字符,而不是转义符。
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.01 sec) mysql> select '\\t'; +----+ | \t | +----+ | \t | +----+ 1 row in set (0.00 sec) mysql> set session sql_mode='NO_BACKSLASH_ESCAPES'; Query OK, 0 rows affected (0.00 sec) mysql> select '\\t'; +-----+ | \\t | +-----+ | \\t | +-----+ 1 row in set (0.00 sec)
默认情况下,在创建表时,可以指定数据目录(DATA DIRECTORY)和索引目录(INDEX DIRECTORY),若开启该MODE,则会忽略这两个选项。在主从复制场景下,可在从库上开启该MODE。
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.01 sec) mysql> create table t (id int) data directory '/tmp/'; Query OK, 0 rows affected (0.15 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='/tmp/' 1 row in set (0.00 sec) mysql> set session sql_mode='NO_DIR_IN_CREATE'; Query OK, 0 rows affected (0.00 sec) mysql> drop table t; Query OK, 0 rows affected (0.11 sec) mysql> create table t (id int) data directory '/tmp/'; Query OK, 0 rows affected, 1 warning (0.05 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
==在开启该MODE的情况下,在创建表时,如果指定的存储引擎不存在或不支持,则会直接提示“ERROR”。==若不开启,则只会提示“Warning”,且使用默认的存储引擎。
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> create table t (id int) engine=federated;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1286 | Unknown storage engine 'federated' |
| Warning | 1266 | Using storage engine InnoDB for table 't' |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) mysql> drop table t; Query OK, 0 rows affected (0.11 sec) mysql> set session sql_mode='NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) mysql> create table t (id int) engine=federated; ERROR 1286 (42000): Unknown storage engine 'federated'
两个整数相减,如果其中一个数是无符号位,默认情况下,会产生一个无符号位的值,如果该值为负数,则会提示“ERROR”,如,
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select cast(0 as unsigned)-1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
若开启该MODE,则允许结果为负数。
mysql> set session sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> select cast(0 as unsigned)-1;
+-----------------------+
| cast(0 as unsigned)-1 |
+-----------------------+
| -1 |
+-----------------------+
1 row in set (0.00 sec)
该MODE会影响’0000-00-00’的插入。实际效果还取决于是否开启严格模式。
1)、在开启严格模式,且同时开启该MODE,是不允许’0000-00-00’插入的。
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 3135
Message: 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in
a future release.1 row in set (0.00 sec)
mysql> insert into t values ('0000-00-00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00' for column 'c1' at row 1
2)、 只开启严格模式,不开启该MODE,允许’0000-00-00’值的插入,且不提示warning。
mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t values ('0000-00-00');
Query OK, 1 row affected (0.04 sec)
3)、不开启严格模式,只开启该MODE,允许’0000-00-00’值的插入,但提示warning。
mysql> set session sql_mode='NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t values ('0000-00-00');
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'c1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.01 sec)
4)、不开启严格模式,也不开启该MODE,允许’0000-00-00’值的插入,且不提示warning。
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values ('0000-00-00');
Query OK, 1 row affected (0.03 sec)
同NO_ZERO_DATE
类似,只不过NO_ZERO_DATE
针对的是’0000-00-00’,而NO_ZERO_IN_DATE
针对的是年不为0,但月或者日为0的日期,如,‘2010-00-01’ or ‘2010-01-00’。
实际效果也是取决于是否开启严格模式,同NO_ZERO_DATE
一样。
开启该MODE,则SELECT列表中只能出现分组列和聚合函数。在这里插入代码片
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select dept_no,emp_no,min(from_date) from dept_emp group by dept_no; +---------+--------+----------------+ | dept_no | emp_no | min(from_date) | +---------+--------+----------------+ | d001 | 10017 | 1985-01-01 | | d002 | 10042 | 1985-01-01 | | d003 | 10005 | 1985-01-01 | | d004 | 10003 | 1985-01-01 | | d005 | 10001 | 1985-01-01 | | d006 | 10009 | 1985-01-01 | | d007 | 10002 | 1985-01-01 | | d008 | 10007 | 1985-01-01 | | d009 | 10011 | 1985-01-01 | +---------+--------+----------------+ 9 rows in set (0.64 sec) mysql> set session sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> select dept_no,emp_no,min(from_date) from dept_emp group by dept_no; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.dept_emp.emp_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
如果不开启该MODE,则允许SELECT列表中出现任意列,但这些列的值并不是确定的。
在对CHAR字段进行存储时,在Compact格式下,会占用固定长度的字节。
如下面的c1列,定义为char(10),虽然’ab’只占用两个字节,但在Compact格式下,会占用10个字节,不足部分以空格填充。
在查询时,默认情况下,会剔除掉末尾的空格。若开启该MODE,则不会剔除,每次都会返回固定长度的字符。
mysql> create table t (c1 char(10)); Query OK, 0 rows affected (0.17 sec) mysql> insert into t values('ab'); Query OK, 1 row affected (0.11 sec) mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select c1, hex(c1), char_length(c1) from t; +------+---------+-----------------+ | c1 | hex(c1) | char_length(c1) | +------+---------+-----------------+ | ab | 6162 | 2 | +------+---------+-----------------+ 1 row in set (0.00 sec) mysql> set session sql_mode='PAD_CHAR_TO_FULL_LENGTH'; Query OK, 0 rows affected (0.00 sec) mysql> select c1, hex(c1), char_length(c1) from t; +------------+----------------------+-----------------+ | c1 | hex(c1) | char_length(c1) | +------------+----------------------+-----------------+ | ab | 61622020202020202020 | 10 | +------------+----------------------+-----------------+ 1 row in set (0.00 sec)
在Oracle中,连接字符串可用concat和管道符("||"),但concat只能连接两个字符串(MySQL中的concat可连接多个字符),局限性太大,如果要连接多个字符串,一般用的是管道符。
开启该MODE,即可将管道符作为连接符。
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select 'a'||'b'; +----------+ | 'a'||'b' | +----------+ | 0 | +----------+ 1 row in set, 2 warnings (0.00 sec) mysql> select concat('a','b'); +-----------------+ | concat('a','b') | +-----------------+ | ab | +-----------------+ 1 row in set (0.00 sec) mysql> set session sql_mode='PIPES_AS_CONCAT'; Query OK, 0 rows affected (0.00 sec) mysql> select 'a'||'b'; +----------+ | 'a'||'b' | +----------+ | ab | +----------+ 1 row in set (0.00 sec)
在创建表时,数据类型可指定为real,默认情况下,其会转化为double,若开启该MODE,则会转化为float。
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> create table t ( c1 real); Query OK, 0 rows affected (0.12 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `c1` double DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> drop table t; Query OK, 0 rows affected (0.04 sec) mysql> set session sql_mode='REAL_AS_FLOAT'; Query OK, 0 rows affected (0.00 sec) mysql> create table t ( c1 real); Query OK, 0 rows affected (0.11 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `c1` float DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
对事务表开启严格模式。
对所有表开启严格模式。
在时间类型定义了小数秒的情况下,如果插入的位数大于指定的位数,默认情况下,会四舍五入,若开启了该MODE,则会直接truncate掉。
mysql> create table t (c1 int,c2 datetime(2)); Query OK, 0 rows affected (0.04 sec) mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values(1,'2018-08-08 11:12:13.125'); Query OK, 1 row affected (0.06 sec) mysql> select * from t; +------+------------------------+ | c1 | c2 | +------+------------------------+ | 1 | 2018-08-08 11:12:13.13 | +------+------------------------+ 1 row in set (0.00 sec) mysql> set session sql_mode='TIME_TRUNCATE_FRACTIONAL'; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values(2,'2018-08-08 11:12:13.125'); Query OK, 1 row affected (0.06 sec) mysql> select * from t; +------+------------------------+ | c1 | c2 | +------+------------------------+ | 1 | 2018-08-08 11:12:13.13 | | 2 | 2018-08-08 11:12:13.12 | +------+------------------------+ 2 rows in set (0.00 sec)
在MySQL 8.0之前,直接授权会隐式创建用户。
mysql> select host,user from mysql.user where user='u1'; Empty set (0.00 sec) mysql> grant all on *.* to 'u1'@'%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.12 sec) mysql> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select host,user from mysql.user where user='u1'; +------+------+ | host | user | +------+------+ | % | u1 | +------+------+ 1 row in set (0.00 sec)
同样的grant语句,在MySQL 8.0中是会报错的。
mysql> grant all on *.* to 'u1'@'%' identified by '123';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123'' at line 1
在MySQL 8.0中,已不允许grant语句隐式创建用户,所以,该MODE在8.0中也不存在。
从字面上看,该MODE是禁止授权时隐式创建用户。但在实际测试过程中,发现其并不能禁止。
mysql> set session sql_mode='NO_AUTO_CREATE_USER';
Query OK, 0 rows affected (0.03 sec)
mysql> grant all on *.* to 'u1'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
其实,该MODE禁止的只是不带“identified by”子句的grant语句,对于带有“identified by”子句的grant语句,其并不会禁止。
mysql> drop user u1;
Query OK, 0 rows affected (0.00 sec)
mysql> set session sql_mode='NO_AUTO_CREATE_USER';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to 'u1'@'%';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> set session sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to 'u1'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
在MySQL 5.7中,还可将SQL_MODE设置为ANSI, DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, TRADITIONAL。
其实,这些MODE只是上述MODE的一种组合,目的是为了和其它数据库兼容。
在MySQL 8.0中,只支持ANSI和TRADITIONAL这两种组合。
ANSI等同于
REAL_AS_FLOAT, PIPES_AS_CONCAT,ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY。
mysql> set session sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like 'sql_mode';
+---------------+--------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------------------------------------------+
| sql_mode | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+---------------+--------------------------------------------------------------------------------+
1 row in set (0.03 sec)
TRADITIONAL
等同于
STRICT_TRANS_TABLES, STRICT_ALL_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION。
mysql> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
SQL_MODE在非严格模式下,会出现很多意料不到的结果。建议线上开启严格模式。但对于线上老的环境,如果一开始就运行在非严格模式下,切忌直接调整,毕竟两者的差异性还是相当巨大。
官方默认的SQL_MODE一直在发生变化,MySQL 5.5, 5.6, 5.7就不尽相同,但总体是趋严的,在对数据库进行升级时,其必须考虑默认的SQL_MODE是否需要调整。
在进行数据库迁移时,可通过调整SQL_MODE来兼容其它数据库的语法。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。