【学习打卡】第10天 MySQL面试指南 第4章

课程名称：MySQL面试指南

课程章节：使用SQLMode改变SQL处理行为

主讲老师：sqlercn

课程内容：

今天学习的内容包括：

设置sql_mode的方式

常用的sql_mode的值（一）

常用的sql_mode的值（二）

课程收获：

设置sql_mode的方式

set [session/global/persist] sql_mode=‘xxxxxx’;

说明：
set session 是仅修改当前连接中的MySQL变量值；
set global 是修改全局中的MySQL变量值，重启之后失效；
set persist 在global的基础上，会将设置值存储到var/lib/mysql/mysqld-auto.cnf中，重启后依然有效；

除此之外还可以手动在/etc/mysql/my.cnf中添加sql_mode设置：
[mysqld]
sql_mode=xxxxx

查看sql_mode的值：
show global variables; // 查看所有全局变量
show global variables like ‘sql_mode’; // 查看过滤的全局变量
select @@global.sql_mode; // 查看某个全局变量
select @@session.sql_mode; // 查看某个会话变量

常用的sql_mode的值（一）

• ONLY_FULL_GROUP_BY：在select中只允许出现group by的列

create table if not exists `stock` (
    `product_id` int unsigned not null,
    `category_id` int unsigned not null,
    `warehouse_id` int unsigned not null,
    `count` int unsigned not null default 0
);
insert into `stock` values
(2030, 9, 1, 10),
(2030, 9, 2, 15),
(2030, 9, 3, 20),
(2040, 8, 1, 30),
(2040, 8, 2, 20);
-- 下面第一条语句在sql_mode包含ONLY_FULL_GROUP_BY时会报错，select中不能包含未在group by中的列。
select product_id, warehouse_id, sum(count) as ct
    from stock group by product_id; -- ✗
select product_id, warehouse_id, sum(count) as ct
    from stock group by product_id, warehouse_id; -- ✓

• ANSI_QUOTES：禁止用双引号来引用用字符串。添加这个设置之后，双引号将会被视为列。

-- 在ANSI_QUOTES模式下，会报错，提示"Hello world!"列不存在。
select "Hello world!";

• REAL_AS_FLOAT：real做为float的同义词，MySQL默认情况下real是double的同义词。

create table test (id real);
-- show create table test; 看到：id fload not null

• PIPES_AS_CONCAT：将||视为字符串的连接操作符而非或运算符。

• ANSI：上面的那些模式设置都是让MySQL的语法模式接近ANSI标准，可以直接设置 sql_mode='ANSI’就等价于：
  sql_mode=‘ONLY_FULL_GROUP_BY,ANSI_QUOTES,REAL_AS_FLOAT,PIPES_AS_CONCAT’。

常用的sql_mode的值（二）

• STRICT_TRANS_TABLES：在事务存储引擎上启用严格模式，SQL语法检查报错。

• STRICT_ALL_TABLES：在所有存储引擎上启用严格模式，SQL语法检查报错。
  严格模式下，insert把字符串传给int型时会报错，宽松模式下字符串会被当成0。
  严格模式下，insert给int型的值如果超过最大值时会报错，宽松模式下会被截断。

• ERROR_FOR_DIVISION_BY_ZERO：不允许0做为除数。
  严格模式下，除数为0会报警告，值为NULL，宽松模式下值也为NULL，但没有警告。

• NO_AUTO_CREATE_USER：在用户不存在时不允许grant语句自动建立用，只对MySQL8.0以前的版本有效，MySQL8.0
  以后默认就不允许使用grant来创建用户。

• NO_ZERO_DATE：日期数据的年不能为0，月、日可以为0

• NO_ZERO_IN_DATE：日期数据内年、月、日均不能为0
  这两值只在严格模式下起作用。

• NO_ENGINE_SUBSTITUTION：当指定的存储引擎不可用时报错。MySQL8.0以后是默认行为。
  在严格模式下，当create table指定的存储引擎不存在时报错，宽松模式下则直接使用默认的存储引擎代替。

• TRADITIONAL：上面的那些模式设置都是让MySQL的语法更严格，可以直接设置
  sql_mode=‘TRADITIONAL’ 就等价于：
  sql_mode=‘STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION’。

对于其他版本的MySQL可以安装percona-toolkit，比如在Ubuntu中：
sudo apt install percona-toolkit