MySQL的SQL模式介绍

59次阅读

共计 9601 个字符，预计需要花费 25 分钟才能阅读完成。

这篇文章主要讲解了“MySQL 的 SQL 模式介绍”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“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 参数来验证外键。

– 示例①
严格 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] 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] 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)

– 示例⑤
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)

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

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

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

正文完