- 1MySQL8.0 优化器介绍（一）_greatsql和mysql的关系
- 2使用人工智能将扫描地图中的数据数字化_qgis 插件 bunting labs
- 3实验题 用类建立职工工资管理数据库，计算总工资并排序输出 统计并输出总工资最高的职工姓名和工资_计算员工的总工资,并按总工资进行排名。
- 4AI在软件测试行业中的应用_ai在软件测试中的应用
- 5Unity和Android的交互_unity android 交互
- 6Unzip安装_unzip安装包
- 7观测云产品更新 | 监控、场景仪表板、查看器、服务管理、DQL等
- 8Rust语言中文版教程_rust中文教程
- 9VCRUNTIME140_1.dll丢失是怎么回事，如何修复VCRUNTIME140_1.dll缺失？_计算机中丢失vcruntime140-1.dll
- 10数据结构_二维数组a[20][10]采用列优先的存储方法,若每个元素占2个存储单元,且第1个元素的
MySQL基础篇：sql_mode配置_sql mode
赞
踩
零、简介
MySQL数据库中的一个环境变量sql_mode，定义了MySQL应该支持的SQL语法、数据校验等等。
sql_mode 是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境下必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须设置。
一、sql_mode常用来解决的几类问题
- 通过设置sql_mode，可以完成不同严格程度的数据校验，有效地保证数据准确性；
- 通过设置sql_mode为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时。则不需要对业务sql进行较大的修改；
- 在不同数据库之间进行数据迁移之前，通过设置sql_mode可以使MySQL上的数据更方便地迁移到目标数据库中
二、sql_mode包含的模式
MySQL5.5以上版本支持三种sql_mode模式，具体如下：
| ANSI模式 | TRADITIONAL模式 | STRICT_TRANS_TABLES模式 |
|---|---|---|
| 宽松模式 ，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告 | 严格模式 ，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事务时，会进行事务的回滚 | 严格模式 ，进行数据的严格校验，错误数据不能插入，报error错误 |
三、sql_mode各个选项作用示例
3.1、sql_mode为空（对于不符合定义的值，会截断到符合定义类型）
查看数据库sql_mode的默认值
由上图可知，可以通过两种方式查看MySQL5.5默认的sql_mode的值，即MySQL5.5中的环境变量sql_mode默认为空。
按照上图所示的步骤进行操作：
- 在MySQL数据库中建立一张测试表；
- 向测试表中插入两条测试数据，并查看数据插入语句是否运行成功；
- 通过
show warnings命令查看，MySQL数据库中是否有警告或者报错信息； - 通过
select命令查看最终插入数据库中数据的内容。
结论：在sql_mode为空的模式下，当我们插入数据时，为满足列长度要求时，数据同样会插入成功，但对超出列长度要求的字段进行截断，同时报warning警告。
3.2、sql_mode为ANSI
修改MySQL数据库的sql_mode模式为ANSI，具体如下：
修改MySQL数据库sql_mode模式为ANSI，针对修改ANSI模式后的结果REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI的具体含义，稍后详解。
结论：ANSI模式下，插入数据时，未满足列长度要求时，数据同样会插入成功，但是对超出列长度的字段进行截断，同时报告warning警告。
3.3、sql_mode为TRADITIONAL
修改MySQL数据库的sql_mode模式为TRADITIONAL，具体如下：
修改MySQL数据库sql_mode模式为ANSI，针对修改ANSI模式后的结果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、严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误，而不仅仅是警告。用于事务时，会进行事务的回滚。
2、一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎，这种方式不是你想要的，因为出现错误前进行的数据更改不会“滚动”，结果是更新“只进行了一部分”。
3.4、sql_mode为STRICT_TRANS_TABLES
修改MySQL数据库的sql_mode模式为STRICT_TRANS_TABLES，具体如下：
结论：在STRICT_TRANS_TABLES模式下，插入数据时，mysql会严格的进行数据的校验，当发现插入列值未满足要求，直接报告error错误，保证了错误数据无法插入到数据库中
四、sql_mode选项说明
| 模式名称 | 模式说明 |
|---|---|
| ONLY_FULL_GROUP_BY | 对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中 |
| NO_AUTO_VALUE_ON_ZERO | 该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了 |
| STRICT_TRANS_TABLES | 在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制， 只对支持事务的表启用严格模式 |
| NO_ZERO_IN_DATE | 在严格模式下,不允许日期和月份为零 |
| NO_ZERO_DATE | 在非严格模式下，可以插入形如“00-00-0000:00:00”的非法日期，mysql仅抛出一个警告，而启用该选项后，mysql不允许插入零日期，插入0日期会抛出错误而非警告 |
| ERROR_FOR_DIVISION_BY_ZERO | 在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL |
| NO_AUTO_CREATE_USER | 禁止GRANT创建密码为空的用户 |
| PIPES_AS_CONCAT | 将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似 |
| ANSI_QUOTES | 启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符 |
| REAL_AS_FLOAT | 将real视为float的同义词而不是double的同义词 |
| STRICT_ALL_TABLES | 对所有引擎的表都启用严格模式 |
五、sql_mode设置
- 查看当前sql_mode
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
- 1
- 2
- 设置当前sql_mode
SET GLOBAL sql_mode = 'modes……';
SET SESSION sql_mode = 'modes ……';
- 1
- 2
- 如果使用mysql，为了继续保留大家使用oracle的习惯，可以对mysql的sql_mode设置如下
进入/etc/my.cnf配置文件，添加如下内容后，重启MySQL服务：
