我要评论sql_mode
- sql_mode 是设置一些否允许一些非法操作 :
- 例如 : 不合法, 不规范, 会引起歧义, 或者是会引起安全性的SQL操作
- 在 MySQL5.5 中 sql_mode 默认是设置为空, 在旧版本中开发规范没有那么严格
- 在MySQL5.7 中对 sql_mode 这个进行了严格的设置
查询sql_mode的值
SHOW VARIABLES LIKE 'sql_mode';
sql_mode常用值
- ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，SELECT子句中只能包含函数和 GROUP BY 中出现的字段。
- NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么去掉该选项。
(MySQL5.5 你插入什么值就会显示什么值没有做限制要求会产生ID重复的问题, MySQL5.7会自动校验,不管你插入什么值还是会自增长 ) - STRICT_ALL_TABLES,STRICT_TRANS_TABLES：
- 对于支持事务的表，这两种模式是一样的：如果发现某个值缺失或非法，MySQL将抛出错误，语句会停止运行并回滚。
- 对于不支持事务的表，这两种模式的效果：
- 如果在插入或修第一个数据行时就发现某个值非法或缺失，那该语句直接抛错，语句停止执行。这个和支持事务的数据表行为是一样的。
- 如果在插入或修改第n个（n>1)数据行时才发现错误，那就会出现下面的情况：
- 在STRICT_ALL_TABLES模式下，停止语句执行，存在部分更新的问题
- 在STRICT_TRANS_TABLES模式下，MySQL将继续执行该语句避免“部分更新问题”，对每个非法值将其转换为最接近的合法值。
- *** NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零。
- NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
(在做数据迁移的时候经常容易出现的问题,从MySQL5.5中导出来的数据,无法在MySQL5.7中导入,会产生日期错误的提示 !
并且在MySQL5.5中日期字段如果不填写默认会插入一条 0000-00-00 : 00:00:00 这样的字段,
在MySQL5.7 中是不允许这样的日期格式的,表示一个非法的格式) - ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL。
- NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户。
- NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或不存在，那么抛出错误。不设置此值时，用默认的存储引擎替代。
设置sql_mode的值：当前会话
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
设置sql_mode的值：所有会话，重启mysql服务后失效
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
在 /etc/my.cnf 中配置sql_mode：永久生效
[mysqld]
#set the SQL mode to strict
#sql-mode="modes..."
sql-mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
测试 5.5 和 5.7
- 需求：查询每个部门年龄最大的人
SELECT name, dept, MAX(age) FROM employee GROUP BY dept;
MySQL5.7中执行 : 以上查询语句在 “ONLY_FULL_GROUP_BY” 模式下查询出错，因为 select子句中的 name列并没有出现在group by子句中，也没有出现在函数中
MySQL5.5中执行 : 在非 “ONLY_FULL_GROUP_BY” 模式下可以正常执行，但是得到的是错误的结果
- 正确的SQL
SELECT e.*
FROM employee e
INNER JOIN
(SELECT dept, max(age) age FROM employee GROUP BY dept) AS maxage
ON e.dept = maxage.dept AND e.age = maxage.age
本文地址：https://blog.csdn.net/Lance_welcome/article/details/107325458
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论