MySQL高级三：SQL大小写规则和sql_mode

SQL大小写规则

一、Linux下数据库名、表名、列名、别名大小写规则

1. 数据库名、表名、表的别名、变量名是严格区分大小写的；
2. 关键字、函数名称在 SQL 中不区分大小写；
3. 列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的； MySQL在Windows的环境下全部不区分大小写

二、编写建议

1. 关键字和函数名称全部大写；
2. 数据库名、表名、表别名、字段名、字段别名等全部小写；

sql_mode

一、宽松模式和严格模式

1. 宽松模式

   作用

   如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受， 并且不报错。

   应用场景

   通过设置sql mode为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据 库之间进行 迁移 时，则不需要对业务sql 进行较大的修改。

2. 严格模式

   作用

   MySQL5.7版本就将sql_mode默认值改为了严格模式。所 以在 生产等环境 中，我们必须采用的是严格模式，进而 开发、测试环境 的数据库也必须要设置，这样在 开发测试阶段就可以发现问题。并且我们即便是用的MySQL5.6，也应该自行将其改为严格模式。

   应用场景

   MySQL等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验，其实开发 中，我们应该在自己 开发的项目程序级别将这些校验给做了 ，虽然写项目的时候麻烦了一些步骤，但是这 样做之后，我们在进行数据库迁移或者在项目的迁移时，就会方便很多。

二、查看当前的sql_mode

1. session中的sql_mode

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gkDkeLa8-1655198002167)(F:\MarkDown学习\图片素材-1\MySQL高级篇\字符集问题\7.查看session中的sql_mode.jpg)]

2. global中的sql_mode

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sMGRabUD-1655198002168)(F:\MarkDown学习\图片素材-1\MySQL高级篇\字符集问题\8.查看global中的sql_mode.jpg)]

三、修改sql_mode

1. 临时设置方式：

   # 全局
   SET GLOBAL sql_mode = 'modes...';
   # 当前会话
   SET SESSION sql_mode = 'modes...'; 
   

2. 永久设置方式：

   在/etc/my.cnf中配置sql_mode，新增：

   [mysqld]
   sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR
   _DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
   

   然后 重启MySQL

3. 实际生成中

   当然生产环境上是禁止重启MySQL服务的，所以采用 临时设置方式 + 永久设置方式 来解决线上的问题， 那么即便是有一天真的重启了MySQL服务，也会永久生效了。

四、各sel_mode的意义

• 	模式	作用
  1	ONLY_FULL_GROUP_BY	对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的
  2	STRICT_TRANS_TABLES	在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
  3	NO_ZERO_IN_DATE	在严格模式下，不允许日期和月份为零
  4	NO_ZERO_DATE	设置该值，mysql数据库不允许插入零日期，会报错
  5	ERROR_FOR_DIVISION_BY_ZERO	在INSERT或UPDATE过程中，如果数据被零除，会报错
  6	NO_AUTO_CREATE_USER	禁止授权创建密码为空的用户
  7	NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被禁用或未编译，则抛出错误
  8	NO_AUTO_VALUE_ON_ZONE	该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长，那么这个选项就起作用了
  9	PIPES_AS_CONCAT	将“||”视为字符串的连接操作符而不是或运算符
  10	ANSI_QUOTES	不能用双引号来引用字符串，以你为它被解释为识别符

  前七种是常用的