欢迎来到阿里云授权服务中心典名科技有限公司!咨询电话:023-66887777 18623333330

微信
手机版
网站地图

MySQL的SQL模式介绍

2021-08-23 10:25:30 投稿人 : admin 围观 : 89 次 0 评论

这篇文章主要讲解了“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)

版权声明:本站部分文章内容、图片来源于网友推荐、互联网收集整理而来,仅供大家学习参考,不代表本站立场,如有侵权,请联系站长,我们会第一时间处理!本站原创内容未经允许不得转载,或转载时需注明出处:典名科技资讯门户

相关文章

  • oracle临时表空间的增删改查命令
    oracle临时表空间的增删改查命令

    oracle临时表空间的增删改查命令发布时间:2021-08-31 01:57:42来源:阿里云阅读:60作者:chen栏目:关系型数据库本篇内容介绍了“oracle临时表空间的增删改查命令”的有关知识,在实际案例的操作过程中,不少人都会遇...

    2021-08-31 11:52:19
  • Redis4.0从库复制报错"master_link_status:down"怎么处理
    Redis4.0从库复制报错"master_link_status:down"怎么处理

    “Redis4.0从库复制报错"master_link_status:down"怎么处理”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“Redis4.0从库复制报错&quo...

    2021-08-31 11:52:19
  • MySQL数据库中有哪些锁
    MySQL数据库中有哪些锁

    “MySQL数据库中有哪些锁”,在日常操作中,相信很多人在MySQL数据库中有哪些锁问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”MySQL数据库中有哪些锁”的疑惑有所帮助!接下来!在 MySQL 数据库中,...

    2021-08-31 11:24:19
  • MSSQL报错参数数据类型text对于replace函数的参数1无效怎么解决
    MSSQL报错参数数据类型text对于replace函数的参数1无效怎么解决

    “MSSQL报错参数数据类型text对于replace函数的参数1无效怎么解决”,在日常操作中,相信很多人在MSSQL报错参数数据类型text对于replace函数的参数1无效怎么解决问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作...

    2021-08-31 11:21:19
  • solaris11 ssh怎么禁止root用户连接
    solaris11 ssh怎么禁止root用户连接

    “solaris11 ssh怎么禁止root用户连接”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“solaris11 ssh怎么禁止root用户连接”吧!在虚拟机中新安装的solaris11,安...

    2021-08-31 11:20:19
  • 怎么用SQL语句查看SQL Server的结构信息
    怎么用SQL语句查看SQL Server的结构信息

    “怎么用SQL语句查看SQL Server的结构信息”,在日常操作中,相信很多人在怎么用SQL语句查看SQL Server的结构信息问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”怎么用SQL语句查看SQL S...

    2021-08-31 11:04:20
  • sqlplus和sqlldr工具的安装方法
    sqlplus和sqlldr工具的安装方法

    “sqlplus和sqlldr工具的安装方法”。本文介绍的方法操作简单快捷,实用性强。sqlplus和sqlldr工具的安装方法”吧!不管安装什么客户端工具都需要先安装instantclient-basic-linux.x64-11.2.0...

    2021-08-31 10:59:20
  • MHA的安装和部署步骤
    MHA的安装和部署步骤

    “MHA的安装和部署步骤”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!##################MHA安装和部署###########...

    2021-08-31 10:52:20
  • redis4.0下MEMORY命令详解
    redis4.0下MEMORY命令详解

    “redis4.0下MEMORY命令详解”,在日常操作中,相信很多人在redis4.0下MEMORY命令详解问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”redis4.0下MEMORY命令详解”的疑惑有所帮助...

    2021-08-31 10:52:19
  • MySQL内存线程独享使用的方法
    MySQL内存线程独享使用的方法

    “MySQL内存线程独享使用的方法”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“MySQL内存线程独享使用的方法”吧!  今天这篇文章暂时先分析 MySQL 中主要的 “线程独享内存” 的。...

    2021-08-25 10:37:36
发表评论