MySQL的SQL模式介绍

这篇文章主要讲解了“MySQL的SQL模式介绍”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL的SQL模式介绍”吧！

MySQL服务器能够在运行在不同的SQL模式下，并且能在不同客户端下设置不同的SQL模式，可以通过sql_mode系统参数来设定SQL模式。
SQL模式会影响MySQL支持的SQL语法和数据的验证。

MariaDB [test]> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.08 sec)

NO_AUTO_CREATE_USER
防止通过GRANT语句创建没有密码的数据库账户。在通过GRANT创建用户时，必须在IDENTIFIED BY后指定非空的密码。建议使用CREATE USER语句创建数据库账户，尽量避免使用GRANT语句来创建数据库账户。

NO_ENGINE_SUBSTITUTION
当执行CREATE TABLE 或 ALTER TABLE语句，指定了一个不支持或没有编译的存储引擎时，控制如何自动替换默认的存储引擎。
当NO_ENGINE_SUBSTITUTION参数没有启用时，对于CREATE TABLE语句，如果指定的存储引擎不可用，会使用默认的存储，且会产生一个警告。对于ALTER TABLE语句，会产生一个警告，且表的存储引擎转化失败。
当NO_ENGINE_SUBSTITUTION参数启用时，如果指定的存储引擎不可用，会发生报错，且表不会创建或更改成功。

--最重要的SQL模式

ANSI
这个模式改变SQL的语法和行为以更接近标准的SQL。

STRICT_TRANS_TABLES
如果值不能插入到事务表中，则退出这条语句；对于非事务表，如果插入或更新的值发生在一行的SQL或多行语句的第一行中，则退出语句。

TRADITIONAL
当插入一个错误的值到一列中时，MySQL会报错而不是警告。


--SQL模式的结合

有一些特殊的SQL模式是一些SQL结合在一起的缩写

例如，ANSI是REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE,ONLY_FULL_GROUP_BY（MySQL 5.7.5）模式的结合；
DB2是PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS模式的结合。

--严格SQL模式

严格模式控制MySQL如何处理数据改变SQL语句（例如INSERT或UPDATE）中无效或丢失的值。插入或更新的值无效有许多原因，例如，有可能因为字段的数据类型，或值超过了字段的最大范围值。对于一个非空字段，当要插入的一个值为空，且没有默认值，这是值丢失。严格模式也会影响DDL语句，例如CREATE TABLE。

如果严格模式没有生效，对于无效或丢失的值，MySQL会插入调整的值。在严格模式下，可以通过INSERT IGNORE或UPDATE IGNORE语句来跳过错误。

在严格模式下，对于类似SELECT这样不改变数据的语句，无效的值会产生一个警告，而不是错误。在严格模式下，对于插入或更新超过字段最大长度的值，会产生一个错误；而在非严格模式下，会产生一个警告，并将截断后的值插入或更新到表中。

严格模式不会影响是否外键验证。可以通过foreign_key_checks参数来验证外键。

MariaDB [test]> show variables like '%foreign%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)


--示例①
严格SQL模式

MariaDB [test]> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.08 sec)

MariaDB [test]> create table t (name varchar(20), email varchar(40));
Query OK, 0 rows affected (0.21 sec)

MariaDB [test]> insert into t values('1234567890000000000000000000','fire beijing @ hotmail.com');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from t;
+----------------------+----------------------------+
| name                 | email                      |
+----------------------+----------------------------+
| 12345678900000000000 | fire beijing @ hotmail.com |
+----------------------+----------------------------+
1 row in set (0.00 sec)

MariaDB [test]> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t values('1234567890000000000000000000','fire beijing @ hotmail.com');
ERROR 1406 (22001): Data too long for column 'name' at row 1


--示例②
ANSI模式和传统模式的区别

MariaDB [test]> set session sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@sql_mode;
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> drop table t;
Query OK, 0 rows affected (0.13 sec)

MariaDB [test]> create table t(d datetime);
Query OK, 0 rows affected (0.21 sec)

MariaDB [test]> insert into t values('2007-04-31');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [test]> select * from t;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [test]> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t values('2007-04-31');
ERROR 1292 (22007): Incorrect datetime value: '2007-04-31' for column 'd' at row 1

--示例③
ANSI模式和传统模式的对比

MariaDB [test]> set sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> drop table t;
Query OK, 0 rows affected (0.11 sec)

MariaDB [test]> create table t(i int);
Query OK, 0 rows affected (0.45 sec)

MariaDB [test]> insert into t values(9%0);
Query OK, 1 row affected (0.08 sec)

MariaDB [test]> select * from t;
+------+
| i    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

MariaDB [test]> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t values(9%0);
ERROR 1365 (22012): Division by 0

--示例④
使反斜线称为普通字符

MariaDB [test]> set sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode                          |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select @@session.sql_mode;
+-------------------------------------------------------------+
| @@session.sql_mode                                          |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select @@sql_mode;
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> drop table t;
Query OK, 0 rows affected (0.14 sec)

MariaDB [test]> create table t (context varchar(20));
Query OK, 0 rows affected (0.17 sec)

MariaDB [test]> insert into t values('\beijing');
Query OK, 1 row affected (0.07 sec)

MariaDB [test]> select * from t;
+---------+
| context |
+---------+
|eijing |
+---------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t values('\\beijing');
Query OK, 1 row affected (0.11 sec)

MariaDB [test]> select * from t;
+----------+
| context  |
+----------+
|eijing  |
| \beijing |
+----------+
2 rows in set (0.00 sec)

MariaDB [test]> set sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t values('\\beijing');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from t;
+-----------+
| context   |
+-----------+
|eijing   |
| \beijing  |
| \\beijing |
+-----------+
3 rows in set (0.00 sec)

--示例⑤
ANSI模式包含了PIPES_AS_CONCAT模式，会将||视为连接操作符

MariaDB [test]> set sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@session.sql_mode;
+-------------------------------------------------------------+
| @@session.sql_mode                                          |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select 'beijing'||'2008';
+-------------------+
| 'beijing'||'2008' |
+-------------------+
| beijing2008       |
+-------------------+
1 row in set (0.00 sec)

MariaDB [test]> set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select 'beijing'||'2008';
+-------------------+
| 'beijing'||'2008' |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 2 warnings (0.02 sec)

MariaDB [test]> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'beijing' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'beijing'  |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

--示例⑥
在将MySQL数据库迁移到其他数据时，可以设置sql_mode为NO_TABLE_OPTIONS模式，这样可以去掉engine关键字

MariaDB [test]> show create table t;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                 |
+-------+----------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `context` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> set session sql_mode='NO_TABLE_OPTIONS';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show create table t;
+-------+-----------------------------------------------------------+
| Table | Create Table                                              |
+-------+-----------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `context` varchar(20) DEFAULT NULL
) |
+-------+-----------------------------------------------------------+
1 row in set (0.00 sec)

感谢各位的阅读，以上就是“MySQL的SQL模式介绍”的内容了，经过本文的学习后，相信大家对MySQL的SQL模式介绍这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！