mysql中的sql_mode模式实例详解
作者:dawn-liu 发布时间:2024-01-19 14:56:45
本文实例讲述了mysql中的sql_mode模式。分享给大家供大家参考,具体如下:
mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法,数据校验等!我们可以通过以下方式查看当前数据库使用的sql_mode:
mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
mysql5.0以上版本支持三种sql_mode模式
ANSI模式 | 宽松模式,对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告。 |
TRADITIONAL模式 | 严格模式,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报error错误。用于事物时,会进行事物的回滚。 |
STRICT_TRANS_TABLES模式 | 严格模式,进行数据的严格校验,错误数据不能插入,报error错误。
|
1 ANSI模式
在ANSI模式下,当我们插入数据时,未满足列长度要求时,数据同样会插入成功,但是对超出列长度的字段进行截断,同时报告warning警告。
mysql> set @@sql_mode=ANSI;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test(name varchar(4), pass varchar(4));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
Query OK, 2 rows affected, 2 warnings (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
| Warning | 1265 | Data truncated for column 'pass' at row 1 |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from test;
+------+------+
| name | pass |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
+------+------+
2 rows in set (0.00 sec)
2 STRICT_TRANS_TABLES模式
在STRICT_TRANS_TABLES模式下,当我们插入数据时,mysql会严格的进行数据的校验,当发现插入列值未满足要求,直接报告error错误,保证了错误数据无法插入到数据库中。
mysql> set @@sql_mode=STRICT_TRANS_TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test(name varchar(4), pass varchar(4));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> show errors;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Error | 1406 | Data too long for column 'name' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
3 TRADITIONAL模式,初看结果是不是一样
mysql> set @@sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test(name varchar(4), pass varchar(4));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> show errors;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Error | 1406 | Data too long for column 'name' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
但是,可以看看设置后的情况
mysql> set @@sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@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)
在TRADITIONAL模式下,对所有的事务存储引擎,非事务存储引擎检查,日期类型中的月和日部分不能包含0,不能有0这样的日期(0000-00-00),数据不能除0,禁止grant自动创建新用户等一些校验。
最后:
set @@只是在sessions级别设置的,要想所有的都生效,还是要设置配置文件
vi /etc/my.cnf
在[mysqld]下面添加如下列:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#NO_ENGINE_SUBSTITUTION对于不存在的引擎就报错,不加的话,指定不支持的引擎时指定默认的innodb
另外:sql_mode还有一个配置ONLY_FULL_GROUP_BY,这个表示采用group by帅选数据的时候只能查看新组内信息
改模式之前的操作
mysql> select * from employee group by post;
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)
此时的sql_mode:
mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
修改一下,退出再进入才会生效
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
再次进入
mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
下面查看修改后的查看结果
mysql> select * from employee group by post; //只能查看post
ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
mysql> select post from employee group by post;
+-----------------------------------------+
| post |
+-----------------------------------------+
| operation |
| sale |
| teacher |
| 老男孩驻沙河办事处外交大使 |
+-----------------------------------------+
4 rows in set (0.00 sec)
mysql> select id,post from employee group by post;
ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
mysql> select name,post from employee group by post,name; //根据group by 后面的选择查看
+------------+-----------------------------------------+
| name | post |
+------------+-----------------------------------------+
| 张野 | operation |
| 程咬金 | operation |
| 程咬铁 | operation |
| 程咬铜 | operation |
| 程咬银 | operation |
| 丁丁 | sale |
| 丫丫 | sale |
| 星星 | sale |
| 格格 | sale |
| 歪歪 | sale |
| alex | teacher |
| jingliyang | teacher |
| jinxin | teacher |
| liwenzhou | teacher |
| wupeiqi | teacher |
| xiaomage | teacher |
| yuanhao | teacher |
| egon | 老男孩驻沙河办事处外交大使 |
+------------+-----------------------------------------+
18 rows in set (0.00 sec)
希望本文所述对大家MySQL数据库计有所帮助。
来源:https://www.cnblogs.com/mmyy-blog/p/9626326.html
猜你喜欢
- counter 是一种特殊的字典,主要方便用来计数,key 是要计数的 item,value 保存的是个数。from collections
- 在面试中,大家应该经历过如下场景面试官:"用过mysql吧,你们是用自增主键还是UUID?" 你
- 1、开始->运行,输入SERVICES.MSC到服务里,停止所有Oracle服务; 2、开始->程序->Oracle - OraHome81
- python sys模块包含了与python解释器和它的环境有关的函数,这个你可以通过dir(sys)来查看他里面的方法和成员属性impor
- 最近研究研究了css,少不了去网上分析一下别人的网页, 但很多网站的css文件都是要么写在一行,要么一个换行都没有,看起来极其痛苦,所以写一
- 今天我们来一起看看Dreamweaver MX 2004在加密FTP 传送 方面的新功能。我们一般在做
- -- 1. 查看被锁的表SELECT p.spid, a.serial#, c.object_name, b.session_id, b.o
- 现在,ORM框架、Web框架和配置都已就绪,我们可以开始编写一个最简单的MVC,把它们全部启动起来。通过Web框架的@decorator和O
- 首先我要吐槽一下,看程序的过程中遇见了yield这个关键字,然后百度的时候,发现没有一个能简单的让我懂的,讲起来真TM的都是头头是道,什么参
- 这篇文章主要介绍了如何使用Python破解ZIP或RAR压缩文件密码,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习
- Python自定义函数在Python编程中,可以使用已经定义好的函数,也可以自定义函数实现某些特殊的功能。自定义函数的语法在Python中,
- 一、准备工作:1、安装mysql3.7,创建一个test数据库,创建student表,创建列:(列名看代码),创建几条数据(以上工作直接用n
- 有的时候我们会去扫表,然后拿出扫的结果再到另一张表里去查信息。比如下面一段index_sql_str = "select %s f
- 创作思路:主要还是想尝试做点稍微不同于整天为迎合客户而做的东西.然后闲时就开始构思,比如坐车,走路什么的.看到有一些复古手机的相关图,就想到
- 在教材实例编写雷达图时出现ValueError,具体如下:ValueError: The number of FixedLocator lo
- 本文实例讲述了Python递归及尾递归优化操作。分享给大家供大家参考,具体如下:1、递归介绍递归简而言之就是自己调用自己。使用递归解决问题的
- 在这篇入门教程中,我们假定你已经有了PHP语言程序、MySQL数据库、计算机网络通讯及XML语言基础。如果你还没有,那么请先学习相关知识。我
- 测试语法如下:powered by jb51.netexec GetRecordFromPage news,newsid,10,100000
- 开发个人博客时往往避免不了分页功能的实现,实现方法大体上分为函数和类两种。本文基于类实现分页功能,以下是详细代码:class Page: p
- time()方法返回时间,在UTC时代以秒表示浮点数。注意:尽管在时间总是返回作为一个浮点数,并不是所有的系统提供时间超过1秒精