MySQL的SQL模式简介
发布时间：2022-01-19 14:02:31 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了MySQL的SQL模式介绍，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习MySQL的SQL模式介绍吧！ MySQL服务器能够在运行在不同的SQL模式下，并且能在不同客户端下设置不同的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模式介绍这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。 （编辑：丽水站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
