mysql 0.00 sec,MySQL库中SQL_MODE功能详解

最新推荐文章于 2024-07-08 16:20:11 发布

李匡吾

最新推荐文章于 2024-07-08 16:20:11 发布

阅读量704

点赞数

文章标签： mysql 0.00 sec

SQL_MODE定义了MySQL支持的sql语法，对数据的校验等，是一个非常重要的系统变量，由一组MODE组成，不同的MODE控制着Mysql不同的行为。

如何查看当前数据库使用的sql_mode ？

mysql> select @@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.05 sec)

为何要特别重视SQL_MODE ？？？

答：MySQL服务器能够工作在不同的SQL模式下，并能针对不同的客户端以不同的方式应用这些模式。这样应用程序就能对服务器操作进行量身定制以满足不同的需求。Sql_mode定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。能在众多不同的环境下，与其他数据库服务器一起更Easy地使用MySQL

如果sql_mode设置不对就会跌入很多坑。

先来看三个简单的例子(MySQL 5.6)：

Example 1

mysql> create table test1(date1 datetime);

Query OK, 0 rows affected (0.07 sec)

mysql> insert into test1 values('2020-08-12');

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from test1;

+---------------------+

| date1 |

+---------------------+

| 0000-00-00 00:00:00 |

+---------------------+

1 row in set (0.00 sec)

结果：表中存储的值不是我们刚插入的值

Example 2

mysql> create table test2(c1 varchar(10));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into test2 values('a'),('b'),('c'), ('d');

Query OK, 4 rows affected (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test2;

+------+

| c1 |

+------+

| a |

| b |

| c |

| d |

+------+

4 rows in set (0.00 sec)

mysql> alter table test2 modify column c1 int;

Query OK, 4 rows affected, 4 warnings (0.05 sec)

Records: 4 Duplicates: 0 Warnings: 4

mysql>show warnings;

+---------+------+------------------------------------------------+

|Leve | Code | Message |

+---------+------+------------------------------------------------+

| Warning | 1366 |Incorrect integer value:'a'forcolumn'c1'at row 1 |

| Warning | 1366 |Incorrect integer value:'b'forcolumn'c1'at row 2 |

| Warning | 1366 |Incorrect integer value:'c'forcolumn'c1'at row 3 |

| Warning | 1366 |Incorrect integer value:'d'forcolumn'c1'at row 4 |

+---------+------+------------------------------------------------+

4 rows in set (0.00 sec)

mysql> select * from test2;

+------+

| c1 |

+------+

| 0 |

+------+

4 rows in set (0.00 sec)

结果：DDL导致原列内容丢失

Example 3

mysql> create table test3(id int not null,c2 varchar(10));

Query OK, 0 rows affected (0.05 sec)

mysql> insert into test3 values(null,'a');

ERROR 1048 (23000): Column 'id' cannot be null

mysql> insert into test3(c2) values('a');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;

+---------+------+----------------------------------------+

|Leve | Code | Message |

+---------+------+----------------------------------------+

| Warning | 1364 |Field 'id' doesn't have a default value |

+---------+------+----------------------------------------+

1 row in set (0.00 sec)

mysql> select * from test3;

+----+------+

| id | c2 |

+----+------+

| 0 | a |

+----+------+

1 rows in set (0.00 sec)

结果：显式指定列和不显式指定列的处理逻辑竟然不一样

为何会这样？？

亲，这些都与SQL_MODE有关！

在MySQL 5.6中SQL_MODE的默认值为"NO_ENGINE_SUBSTITUTION"，非严格模式。

此模式下进行数据变更操作时，若涉及的列中存在无效值(日期不存在，数据类型不对，数据溢出)，只提示"Warning"，并不报错。

要规避以上问题，需开启SQL_MODE的严格模式。

SQL_MODE的严格模式

将SQL_MODE添加STRICT_ALL_TABLES或STRICT_TRANS_TABLES

继续使用上面的例子，看严格模式下MySQL的处理逻辑。

mysql> set session sql_mode='STRICT_TRANS_TABLES';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values('2020-08-12');

ERROR 1292 (22007): Incorrect datetime value: '2020-08-12' for column 'a' at row 1

mysql> alter table test2 modify column c1 int;

ERROR 1366 (HY000): Incorrect integer value: 'a' forcolumn 'c1' at row 1

mysql> insert into test3(c2) 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 test (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 test values (1),(2),('');

Query OK, 3 rows affected, 1 warning (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 1

mysql> show warnings;

+---------+------+----------------------------------------------------+

|Leve | Code | Message |

+---------+------+----------------------------------------------------+

| Warning | 1366 |Incorrect integer value: '' for column 'c1' at row 3|

+---------+------+----------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from test;

+------+

| 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中SQL_MODE的默认Mode

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

SQL_MODE在全局级别/会话级别都可修改。当指定多个MODE时用逗号分隔。

全局级别

set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';

会话级别

set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';

SQL_MODE的完整列表

ANSI_QUOTES

在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.08 sec)

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

若开启该MODE，则双引号，同反引号一样，可对关键字和保留字转义:

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.10 sec)

备注：在开启该MODE的情况下，不能再用反引号来引字符串

ERROR_FOR_DIVISION_BY_ZERO

决定除数为0的处理逻辑，实际效果还取决于是否开启严格模式。

1. 开启严格模式，并开启该MODE，插入1/0，会直接报错。

mysql> create table test (c1 double);

Query OK, 0 rows affected (0.03 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 test 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 test values(1/0);

Query OK, 1 row affected (0.09 sec)

mysql> select * from test;

+--------+

| c1 |

+--------+

| NULL |

+--------+

1 rows in set (0.00 sec)

3. 不开启严格模式，只开启该MODE，允许1/0的插入，但提示warning。

4. 不开启严格模式，也不开启该MODE，允许1/0的插入，且不提示warning，同2一样。

ALLOW_INVALID_DATES

在严格模式下，对于日期的检测较为严格，其必须有效。若开启该MODE，对于month和day的检测会相对宽松。其中month只需在1~12之间，day只需在1~31之间，而不管其是否有效，如下面的'2020-08-12'。

mysql> create table test1 (date1 datetime);

Query OK, 0 rows affected (0.10 sec)

mysql> set session sql_mode='STRICT_TRANS_TABLES';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into test1 values('2020-08-12');

ERROR 1292 (22007): Incorrect datetime value: '2020-08-12' for column 'date1' 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 test1 values('2020-08-12');

Query OK, 1 row affected (0.01 sec)

mysql> select * from test1;

+---------------------+

| date1 |

+---------------------+

| 2020-08-12 00:00:00 |

+---------------------+

1 row in set (0.00 sec)

备注：该MODE只适用于DATE和DATETIME，不适用于TIMESTAMP

NO_AUTO_VALUE_ON_ZERO

默认设置下，对自增主键插入NULL或0时，自动生成下一个值。若开启该MODE，当插入0时，并不会自动生成下一个值。

若表中自增主键列存在0值，在进行逻辑备份还原时，可能会导致数据不一致。所以mysqldump在生成备份数据之前，会自动开启该MODE以避免数据不一致的情况。

mysql> create table test (id int auto_incrementprimary key);

Query OK, 0 rows affected (0.09 sec)

mysql> set session sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (0);

Query OK, 1 row affected (0.03 sec)

mysql> select * from test;

+------+

| 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.01 sec)

mysql> insert into test values (0);

Query OK, 1 row affected (0.08 sec)

mysql> select * from test;

+------+

| id |

+------+

| 0 |

| 1 |

+------+

2 row in set (0.00 sec)

HIGH_NOT_PRECEDENCE

默认情况下，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。

IGNORE_SPACE

默认情况下，函数名和左括号 “(”之间不允许存在空格。若开启该MODE，则允许。

mysql> select count(*) from test;

+------------+

| count(*) |

+------------+

| 6 |

+------------+

1 row in set (0.00 sec)

mysql> set session sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> select count (*) from test;

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 test' at line 1

mysql> set session sql_mode='IGNORE_SPACE';

Query OK, 0 rows affected (0.00 sec)

mysql> select count (*) from test;

+------------+

| count(*) |

+------------+

| 6 |

+------------+

1 row in set (0.01 sec)

NO_BACKSLASH_ESCAPES

默认情况下，反斜杠“\”会作为转义符，若开启该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)

NO_DIR_IN_CREATE

默认设置下在创建表时，可以指定数据目录(DATA DIRECTORY)和索引目录(INDEX DIRECTORY)，若开启该MODE，则会忽略这两个选项。在主从复制场景下，可在从库上开启该MODE

mysql> set session sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> create table test (id int) data directory '/data_dir/';

Query OK, 0 rows affected (0.17 sec)

mysql>show create table test\G

*************************** 1. row *************************** Table: test

Create Table: CREATE TABLE `test` (

`id` int(11) DEFAULT NULL)

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='/data_dir/'

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 test;

Query OK, 0 rows affected (0.11 sec)

mysql> create table test (id int) data directory '/data_dir/';

Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> show create table test\G

*************************** 1. row *************************** Table: test

Create Table: CREATE TABLE `test` (

`id` int(11) DEFAULT NULL)

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

NO_ENGINE_SUBSTITUTION

开启该MODE的情况下，创建表时如果指定的存储引擎不存在或不支持，则会直接提示“ERROR”

不开启时只会提示“Warning”，且使用默认的存储引擎。

mysql> set session sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> create table test (id int) engine=federated;

Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> show warnings;

+---------+------+----------------------------------------------------+

|Leve | Code | Message |

+---------+------+----------------------------------------------------+

| Warning | 1286 |Unknown storage engine 'federated' |

| Warning | 1266 |Using storage engine InnoDB for table 'test' |

+---------+------+----------------------------------------------------+

2 rows in set (0.00 sec)

mysql> show create table test\G

*************************** 1. row*************************** Table: test

Create Table: CREATE TABLE `test` (

`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 test;

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 test (id int) engine=federated;

ERROR 1286 (42000): Unknown storage engine 'federated'

NO_UNSIGNED_SUBTRACTION

两个整数相减，如果其中一个数是无符号位，默认情况下会产生一个无符号位的值，如果该值为负数，则会提示“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 valueis 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)

NO_ZERO_DATE

该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 test 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 test 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 test values ('0000-00-00');

Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> show warnings;

+---------+------+----------------------------------------------------+

|Leve | 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 test values ('0000-00-00');

Query OK, 1 row affected (0.03 sec)

NO_ZERO_IN_DATE

在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。

实际效果也是取决于是否开启严格模式，同NO_ZERO_DATE一样。

PIPES_AS_CONCAT

在Oracle中，连接字符串可用concat和管道符("||")，但concat只能连接两个字符串(MySQL中的concat可连接多个字符)，局限性太大，如果要连接多个字符串，一般用管道符。Mysql中开启该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)

ONLY_FULL_GROUP_BY

开启该MODE，则SELECT列表中只能出现分组列和聚合函数

mysql> set session sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> select deptno,empno,min(update_date) from deptgroup by deptno;

+--------+-------+-------------+

| deptno | empno | update_date |

+--------+-------+-------------+

| dept01 | 1001 | 2001-01-01 |

| dept02 | 1002 | 2001-01-01 |

| dept03 | 1011 | 2001-01-01 |

| dept04 | 1051 | 2001-01-01 |

| dept05 | 1006 | 2001-01-01 |

| dept06 | 1008 | 2001-01-01 |

+---------+------+-------------+

6 rows in set (0.56 sec)

mysql> set session sql_mode='ONLY_FULL_GROUP_BY';

Query OK, 0 rows affected (0.00 sec)

mysql> select deptno,empno,min(update_date) from dept group by deptno;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dept.empno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如果不开启该MODE，则允许SELECT列表中出现任意列，但这些列的值并不是确定的，官方文档中关于这一点的描述如下：

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query.

In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses.

Disabling ONLY_FULL_GROUP_BY is useful primarily when you know that, due to some property of the data, all values in each nonaggregated column not named in the GROUP BY are the same for each group.

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

PAD_CHAR_TO_FULL_LENGTH

在对CHAR字段进行存储时,在Compact格式下会占用固定长度的字节。

如下面的c1列，定义为char(10)，虽然'ab'只占用两个字节，但在Compact格式下，会占用10个字节，不足部分以空格填充。

在查询时默认情况下，会剔除掉末尾的空格。若开启该MODE，则不会剔除，每次都会返回固定长度的字符。

mysql> create table test (c1 char(10));

Query OK, 0 rows affected (0.11 sec)

mysql> insert into test values('ab');

Query OK, 1 row affected (0.12 sec)

mysql> set session sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> select c1, hex(c1), char_length(c1) fromtest;

+--------+---------+-----------------+

| 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) fromtest;

+--------+------------------------+------------------+

| c1 | hex(c1) | char_length(c1) |

+--------+------------------------+------------------+

| ab | 61622020202020202020 | 10 |

+--------+------------------------+------------------+

1 row in set (0.00 sec)

REAL_AS_FLOAT

在创建表时，数据类型可指定为real，默认设置下，其会转化为double，若开启该MODE，则会转化为float

mysql> set session sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> create table test (c1 real);

Query OK, 0 rows affected (0.08 sec)

mysql> show create table test\G

*************************** 1. row *************************** Table: test

Create Table: CREATE TABLE `test` (

`c1` double DEFAULT NULL)

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

mysql> drop table test;

Query OK, 0 rows affected (0.03 sec)

mysql> set session sql_mode='REAL_AS_FLOAT';

Query OK, 0 rows affected (0.00 sec)

mysql> create table test (c1 real);

Query OK, 0 rows affected (0.08 sec)

mysql> show create table test\G

*************************** 1. row *************************** Table: test

Create Table: CREATE TABLE `test` (

`c1` float DEFAULT NULL)

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

TIME_TRUNCATE_FRACTIONAL

在时间类型定义了小数秒的情况下，如果插入的位数大于指定的位数，默认情况下，会四舍五入，若开启了该MODE，则会直接truncate掉。

mysql> create table test (id int,c2 datetime(2));

Query OK, 0 rows affected (0.03 sec)

mysql> set session sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1,'2020-08-1211:12:13.125');

Query OK, 1 row affected (0.06 sec)

mysql> select * from test;

+----+-------------------------+

| id | c2 |

+----+-------------------------+

| 1 | 2020-08-12 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 test values(2,'2020-08-1211:12:13.125');

Query OK, 1 row affected (0.06 sec)

mysql> select * from test;

+----+-------------------------+

| c1 | c2 |

+----+-------------------------+

| 1 | 2020-08-12 11:12:13.13 |

| 2 | 2020-08-12 11:12:13.12 |

+----+-------------------------+

2 row in set (0.00 sec)

NO_AUTO_CREATE_USER

MySQL 8.0之前，直接授权会隐式创建用户。

mysql> select host,user from mysql.user whereuser='user1';

Empty set (0.00 sec)

mysql> grant all on *.* to 'user1'@'%' identified by'123456';

Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> show warnings;

+---------+------+------------------------------------------------+

|Leve | Code | Message |

+---------+------+------------------------------------------------+

| Warning | 1287 |Using GRANT for creating new user isdeprecated 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 whereuser='user1';

+------+-------+

|host | user |

+------+-------+

| % | user1 |

+------+-------+

1 rows in set (0.00 sec)

从字面上看，该MODE是禁止授权时隐式创建用户。但在实际测试过程中，发现其并不能禁止。

mysql> set session sql_mode='NO_AUTO_CREATE_USER';

Query OK, 0 rows affected (0.03 sec)

mysql> grant all on *.* to 'user1'@'%' identified by '123456';

Query OK, 0 rows affected, 1 warning (0.00 sec)

其实，该MODE禁止的只是不带“identified by”子句的grant语句，对于带有“identified by”子句的grant语句，并不会禁止。

mysql> drop user user1;

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 'user1'@'%';

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 'user1'@'%';

Query OK, 0 rows affected, 1 warning (0.00 sec)

同样的grant语句，在MySQL 8.0中是会报错的

mysql> grant all on *.* to 'user1'@'%' identified by'123456';

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 ' '123456' at line 1

在MySQL 8.0中已不允许grant语句隐式创建用户，该MODE在8.0中也存在，且约束性更强。

SQL_MODE的常见组合

在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.02 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 |+---------------+-------------------------------------------------------------------------------------------------------------------+1row in set (0.01 sec)

STRICT_ALL_TABLES

对所有表开启严格模式。

STRICT_TRANS_TABLES

对事务表开启严格模式。

总结

1. SQL_MODE在非严格模式下，会出现很多意料不到的结果。生产环境最好开启严格模式。对于旧有环境，如果一开始就运行在非严格模式下，切忌直接调整，两者差异性巨大，易出现数据异常。

2. Mysql不同版本的SQL_MODE一直在改变,MySQL 5.5, 5.6, 5.7不尽相同,但总体是趋严的。对数据库进行升级时，必须考虑默认的SQL_MODE是否需要调整。

3. 进行数据库迁移时，可通过调整SQL_MODE来兼容其它数据库的语法。

4. 启动之后根据不同的应用需求，使用SET[SESSION|GLOBAL]sql_mode='mode1,mode2…’语句，设置不同的变量更改mysql DB支持的SQL语法

李匡吾

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 0.00 sec,MySQL库中SQL_MODE功能详解

SQL_MODE定义了MySQL支持的sql语法，对数据的校验等，是一个非常重要的系统变量，由一组MODE组成，不同的MODE控制着Mysql不同的行为。如何查看当前数据库使用的sql_mode？mysql> select@@sql_mode；+-------------+---------------------------------------------------+|Varia...
复制链接

扫一扫