MySQL的严格模式

• MySQL的严格模式，简单来说就是MySQL自身对数据进行严格的校验(格式、长度、类型等)，比如一个整型字段我们写入一个字符串类型的数据，在非严格模式下MySQL不会报错，同样如果定义了char或varchar类型的字段，当写入或更新的数据超过了定义的长度也不会报错。我认为这个对于编程来说没有任何好处，虽然我们尽量在代码中做数据校验。MySQL开启了严格模式从一定程序上来讲是对我们代码的一种测试，如果我们的开发环境没有开启严格模式在开发过程中也没有遇到错误，那么在上线或代码移植的时候将有可能出现不兼容的情况，因此在开发过程做最好开启MySQL的严格模式。
• 查看sql_mode：select @@sql_mode;

mysql>select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

• 可以查询到一大串字符，这些字符都是什么意思呢？

sql_mode支持的模式

• ONLY_FULL_GROUP_BY

含义：sql中select后面的字段必须出现在group by后面，或者被聚合函数包裹，如下列语句，group by 所有字段，或者group by 主键
select column1, column2 from table group by column1 , column2

• STRICT_TRANS_TABLES

含义：存储引擎启用严格模式，非法数据值被拒绝。STRICT_TRANS_TABLES和STRICT_ALL_TABLES，只要把SQL_MODE设置为其中的一个，就被称为严格模式
这两个选项对于Innodb表是相同的作用，对于单个insert操作，无论插入单行或是多行，只要插入数据与字段类型不兼容，则insert操作失败并回滚。
对于Myisam表是不同的作用：我们可以通过SHOW CREATE TABLE 表名;查看表的存储引擎
1.STRICT_TRANS_TABLES，对于单个insert操作，插入单行数据与字段类型不兼容，则insert操作失败并回滚；插入多行数据，如果插入数据的第一行内容与字段类型不兼容，则insert操作失败并回滚，如果插入数据的第一行内容与字段类型兼容，但后续的数据行存在不兼容的情况，则兼容的数据正常插入，不兼容的数据会转换成符合字段类型的格式再插入，不会中断和回滚；
2.STRICT_ALL_TABLES，与STRICT_TRANS_TABLES不同的是，如果插入数据的第一行内容与字段类型兼容，但后续的数据行存在不兼容的情况，则兼容的数据正常插入，不兼容的数据则会报错并终止insert操作。

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• NO_ZERO_IN_DATE

在严格模式，这个模式不接受月和日部分为0的日期。可以接受年为0的日期。

• NO_ZERO_DATE

这个模式影响着插入的’0000-00-00’值是否作为合法的数值，这个结果和是否启用严格模式有关。如果这个模式禁用，则’0000-00-00’被允许并且插入没有警告，如果这个模式启用，则’0000-00-00’被运行并且插入产生一个警告信息；如果这个模式和严格模式被启用，则’0000-00-00’不被允许并且插入产生错误，除非ignore被使用。NO_ZERO_DATE不是严格模式的一部分，应该和严格模式一起被使用。因为NO_ZERO_DATE将会被放弃在将来的mysql中，它的影响将会被包含进严格模式中。

• ERROR_FOR_DIVISION_BY_ZERO

如果这个模式未启用，那么零除操作将会插入空值并且不会产生警告；如果这个模式启用，零除操作插入空值并产生警告；如果这个模式和严格模式都启用，零除从操作将会产生一个错误，除非使用来ignore来忽略错误。例如insert ignore和update ignore，这样的话零除操作将插入空并发出警告。ERROR_FOR_DIVISION_BY_ZERO不是严格模式的一部分，应该和严格模式一起启用，默认是启用的。

• NO_AUTO_CREATE_USER

禁止grant语句自动创建用户，除非认证信息被指定，语句必须包含一个非空的密码使用identified by或使用认证插件identified with.
grant语句可以对用户授权，如果对应的用户不存在，那么mysql将会自动创建对应的用户

• NO_ENGINE_SUBSTITUTIO

mysql 在create table 时可以指定engine子句；这个engine子句用于指定表的存储引擎，如果把引擎指定成一个并不存在的引擎，在设置了NO_ENGINE_SUBSTITUTIO的情况下会报错，如果没设置，会将引擎设置为默认引擎

开启/关闭严格模式

• 可以通过执行SQL语句来开启，但是只对当前连接有效，下面是SQL语句： 设置当前临时严格模式

 set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
1

• 通过修改MySQL的配置文件 my-default.ini，在配置文件中查找sql-mode，将此行修改成为： 永久严格模式

  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
1

MySQL修改用户权限

• 修改用户权限：GRANT privileges ON databasename.tablename TO 'username'@'host';

privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名，可用*表示所有数据库
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
host可指定用户连接的主机，可指定用户通过某个网段的地址连接数据库

grant all privileges on 数据库.* to 用户y@'192.168.%.%';
1

• 使用function关键字和procedure关键字可以对函数或存储过程授权，同理，使用view关键字即可对视图进行授权

grant execute on function 数据库名.表名 to 用户@host;
1

• 查看用户权限

show grants for username@host;
1

SELECT * FROM mysql.user WHERE user='root';
1

• 删除授权

revoke "要移除的权限" on 数据库.表 from 用户@host
1

MySQL的索引

• 索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。在每没有索引的情况下查询数据都是按行查找、遍历数据，在数据多的时候，遍历数据实在太慢。索引就是将每行数据的部分关键字抽出来，编成目录，如果用户搜索信息的关键字正好是构成索引的数据，则可以通过直接通过索引定位所需数据的位置，大大增加了查询效率
• 索引的方式有很多种，也就有了索引模型的概念。可以用于提高读写效率的数据结构很多，哈希表、有序数组和搜索树是三种常见、也比较简单的数据结构，。
• MySQL的InnoDB存储引擎使用B+Tree实现其索引结构。
  
• B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

存放的数据量

• 在InnoDB中，索引默认使用的数据结构为B+树，而B+树里的每个节点都是一个页，默认的页大小为16KB。非叶子节点存的是索引值以及页的偏移量，而叶子节点上存放的则是完整的每行记录
• 索引页格式
  

File Header 文件头部 38 字节 页的一些通用信息
Page Header 页面头部 56 字节 数据页专有的一些信息
Infimum + Supremum 最小记录和最大记录 26 字节 两个虚拟的行记录
User Records 用户记录 大小不确定 实际存储的行记录内容
Free Space 空闲空间 大小不确定 页中尚未使用的空间
Page Directory 页面目录 大小不确定 页中的某些记录的相对位置
File Trailer 文件尾部 8 字节 校验页是否完整

• 详情可见看完本篇，让你彻底了解InnoDB数据页结构

非叶子节点能存放多少数据

• 页默认16KB
• File Header、Page Header等一共占102个字节
• Infimum + Supremum分别占13个字节
• 记录头占5个字节
• id占为int，占4个字节
• 页目录的偏移量占4个字节

非叶子节点能存放的索引记录
= (页大小 - File Header - Page Header - …) / ( 记录头 + 主键 + 页偏移量)
= （16KB - 128B) / (5B + 4B + 4B)
= 16256 / 13
= 1250 条

叶子节点能存放多少数据

• 变长列表占1个字节
• null标志位忽略
• 记录头占5个字节
• 数据内容x个字节，比如：id为int，占4个字节
• 事务ID列占6个字节
• 回滚指针列占7个字节

叶子节点能存放的数据记录
= (页大小 - File Header - Page Header - …) / ( 主键 + 字段 + 下一条记录的偏移量)
= （16KB - 128B) / (1B + 5B + xB + 6B + 7B)
= 16256 / 19 + x

• 如果数据内同有两个字段分别为id和name,id占为int，占4个字节，name为VARCHAR，编码为UTF8，为了好算，所有行记录我都只用两个中文，那就是 2 * 3B = 6个字节

叶子节点能存放的数据记录
= 16256 / 29
560 条

高为3的B+树能存多少行数据记录

• 根节点能放1250条索引记录
• 第二层能放1250 * 1250 = 1,562,500条索引记录
• 叶子节点 1250 * 1250 * 560 = 875,000,000条数据记录，八亿多条数据

修改page

• 在上述的计算中可以看出，我们能够直接控制的变量有：索引类型、数据内容，其中索引类型最小只能为int型了，数据内容的大小实际上会比我们计算时大得多
• 那我们可以修改其他值吗？先看看mysql的页大小是否可以修改，我使用的mysql版本为5.7.26
• InnoDB 存储引擎中默认每个页的大小为16KB，在 MySQL 中可通过如下命令查看页的大小：show variables like 'innodb_page_size';。
  
• 尝试修改innodb_page_size，报错，MySQL本身没有提供修改页大小的参数
  
• 在配置文件中添加innodb_page_size =8K ,重启mysql，发现MySQL无法启动
  
• 只能通过修改源码重新编译mysql来实现改变页面大小，这我就不会了。。。

存储过程

• 存储过程：（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。
• 存储过程就类似编程语言中的函数，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类型。

基本语句格式

• 创建存储过程

DELIMITER $$

CREATE
    PROCEDURE 数据库名.存储过程名(in/out/inout 参数名 参数类型(长度))
	BEGIN
		存储过程的语句块;
	END$$
DELIMITER ;  
1
2
3
4
5
6
7
8

在命令行定义存储过程时 ，需要用关键字delimiter定义结束符号(默认是;) ，这里改为$$，创建后，将结束符号改回;

• 查看存储过程

select * from information_schema.routines where routine_schema = '数据库名'; --查看指定数据库的存储过程及状态信息
1

show create procedure 存储过程名字 ; --查看某个存储过程的定义sql语句
1

• 删除存储过程

drop procedure [if exists] 存储过程名字;
1

参数的定义

• 存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类型

IN类型：表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT类型：该值可在存储过程内部被改变，并可返回
INOUT类型：输入输出参数:调用时指定，并且可被改变和返回

• 定义参数的格式为in/out/inout 参数名 参数类型(长度)，
• DECLARE中用来声明变量，变量默认赋值使用的DEFAULT，语句块中改变变量值，使用SET 变量=值；
• 案例：查询user库中的users表中具有相同的某个username的列数

DELIMITER $$  #定义结束符号为

CREATE
	# 接收两个参数，分别是需要统计的name和接收的统计结果repeat_num，name不能被改变设置IN类型，需要返回repeat_num设置为OUT类型
    PROCEDURE user.repeat_name(in name vaarchar(50), out repeat_num int)  
	BEGIN
		# 把SQL中查询的结果通过INTO赋给变量
		select * count(*) into repeat_num where username = name;  # 把SQL中查询的结果通过INTO赋给变量
	END$$
DELIMITER ;  
1
2
3
4
5
6
7
8
9
10

• 调用该存储过程

call user.repeat_num('pw',@num);  # @num表示接收返回结果的repeat_num结果
1

• 查看该变量

select @num
1

• 存储过程不仅可以定义参数，还可以定义局部变量和使用全局变量

变量的使用

• 系统变量：是mysql服务器提供，不是用户自定义的，属于服务器层面。分为全变量global、会话变量session。

# 查看系统变量
show [session|global] variables;  --查看所有的系统变量
show [session|global] variables like ‘...’;  --可以通过like模糊匹配方式查找变量
select @@[session/global.]系统变量名；  --查看指定变了的值，注意可选参数里面的
1
2
3
4

# 设置系统变量
set [session|global] 系统变量名字 = 值;  --不需要@，查询是才需要@
上面这种设置全局变量方法重启后就失效，不想失效就修改参数文件, 然后重启mysql
1
2
3

• 用户变量：是用户根据需求自己定义的变量，用户变量不用提前声明，在用是时候直接用‘@变量名’使用就可以。其作用域为当前连接

# 赋值
set @var_name = 值;
set @var_name := 值; --推荐使用这种冒号等于的方式定义
select 字段名 into @var_name from 表名; -- 将查询结果赋值给用户变量
1
2
3
4

# 查看变量
select @var_name ;    --如果使用select 一个不存在的用户变量名会返回null而不会报错
1
2

• 局部变量：是根据需要定义在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的作用范围是在其内声明的begin…end模块。

# 声明：
declare 变量名 变量类型(长度) [default 默认值]
# 变量类型就是数据库字段类型：int、bigint、char、varchar、date、time
1
2
3

赋值：
set 变量名 = 值；
set 变量名 ：= 值；
select 字段名 into 变量名 from 表名；
1
2
3
4

其他语法

• if

IF 条件1 THEN
	sql语句;
ELSE 条件2 THEN  # 可选
	sql语句;
ELSE             # 可选
	sql语句 ;
1
2
3
4
5
6

DELIMITER $$
CREATE
    PROCEDURE `demo`.`demo3`(IN `day` INT)
	BEGIN
		IF `day` = 0 THEN
		SELECT '星期天';  
		ELSEIF `day` = 1 THEN
		SELECT '星期一';
		ELSEIF `day` = 2 THEN
		SELECT '星期二';
		ELSE
		SELECT '无效日期';
		END IF;
		
	END$$
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• case

# 语法一
CASE case_value
	WHEN when_value1 THEN
		sql语句;
	WHEN when_value2 THEN
		sql语句；
	ELSE
		sql语句;
END CASE;
1
2
3
4
5
6
7
8
9

DELIMITER $$
CREATE 
    PROCEDURE demo5(IN num INT)
	BEGIN
		CASE num  -- 条件开始
		WHEN 1 THEN 
			SELECT '输入为1';
		WHEN 0 THEN 
			SELECT '输入为0';
		ELSE 
		SELECT '不是1也不是0';
		END CASE; -- 条件结束
	END$$
DELIMITER;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

CASE
	WHEN contidion1 THEN
		sql语句;
	WHEN condition2 THEN
		sql语句;
	ELSE
		sql语句;
END CASE;
	
1
2
3
4
5
6
7
8
9

DELIMITER $$
CREATE 
    PROCEDURE demo4(IN num INT)
	BEGIN
		CASE -- 条件开始
	
		WHEN num<0 THEN 
			SELECT '负数';
		WHEN num>0 THEN 
			SELECT '正数';
		ELSE 
		SELECT '不是正数也不是负数';
	
		END CASE; -- 条件结束
	END$$
DELIMITER;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• while：while循环是有条件的循环控制语句，满足条件后，在执行循环体中的SQL语句

WHILE 条件 DO
	sql语句;
END WHILE;
1
2
3

• repeat：repeat是有条件的循环控制语句，当满足条件的时候退出循环

REPEAT
	sql语句;
	UNTIL 条件
END REPEAT;
1
2
3
4

• loop：实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用来实现简单的死循环，LOOP可以配合一下两个语句使用：LEAVE:配合循环使用，退出循环；ITERATE:：必须用在循环中，跳出当前循环剩下的语句，直接进入下一次循环

[begin_label:] LOOP
	SQL逻辑;
END LOOP [end_label];
1
2
3

LEAVE lable; # 退出指定标记的循环体
ITERATE lable; # 直接进入下一次循环
1
2

DELIMITER $$
CREATE 
    PROCEDURE demo8(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     demo_sum:LOOP-- 循环开始
		SET num = num+1;
		IF num > 10 THEN
		    LEAVE demo_sum; -- 结束此次循环
		ELSEIF num <= 9 THEN
		    ITERATE demo_sum; -- 跳过此次循环
		END IF;
		
		SET SUM = SUM+num;
		END LOOP demo_sum; -- 循环结束
	END$$
DELIMITER;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

触发器

• 触发器（trigger）是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器特性
什么条件会触发：I、D、U
什么时候会触发：在增删改前或者后
触发频率：针对每一行执行
触发器定义在表上，附着在表上

触发器基本语法

• 创建只有一个执行语句的触发器

create trigger 触发器名 before|after 触发事件
on 表名 for each row
执行语句;
1
2
3

• 创建有多个执行语句的触发器

create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin
	执行语句列表;
end;
1
2
3
4
5

• 删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
1

• 触发器信息查看，在MySQL中，所有的触发器的定义都存在于INFORMATION_SCHEMA数据库的triggers表中，可以通过查询命令SELECT来查看

SHOW TRIGGERS [FROM schema_name];
1

• 触发器语句中的if等语句和存储过程相同

MySQL的一些函数

substr和substring

• substr用法如下：string：为字符串；start：为起始位置；length：为长度。mysql中的start是从1开始的

substr(String string, int start, int length);
1

• substring用法有两种：

# string：为被截取的字符串，start：为起始位置；length：为长度
substring(string, start, length)
1
2

# 是一种更加规范的写法，参数含义同上所述,for length可以不写，默认从position开始截取到最后一个字符
substring(string from start for length)
1
2

• MySQL 字符串截取函数还有：left(), right(), substring_index()， mid()。

truncate和delete

• truncate作用是清空表或者说是截断表，只能作用于表，语法如下：

truncate tbl_name
1

• delete来删除 MySQL 数据表中的记录，语法如下：

DELETE FROM table_name [WHERE Clause]  
1

• truncate命令结果跟delete很像，其实这两者者还是与很大的不同的

truncate是DDL语句，执行后无法回滚；delete是DML语句，可回滚。
truncate只能作用于表；delete可作用于表、视图等。
truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；delete只删除 MySQL 数据表中的记录
truncate会重置表的自增值；delete不会
truncate不会激活与表有关的删除触发器；delete可以
truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间

concat和group_concat

• concat：将多个字符串连接成一个字符串，语法如下：返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

concat(str1, str2,...)  
1

• group_concat：将分组中括号里对应的字符串进行连接.如果分组中括号里的参数xxx有多行，那么就会将这多行的字符串连接，每个字符串之间会有特定的符号进行分隔。语法如下：

[DISTINCT] column1 [ORDER BY column2 ASC\DESC] [SEPARATOR seq])
1

[ ORDER BY column2 ASC\DESC] :表示将会根据column2升序或者降序连接.其中column2不一定一定要求是column1,只要保证column2在这个分组中即可.如果没有写ORDER BY句段，那么连接是没有顺序的。
[ SEPARATOR seq] : 表示各个column1将会以什么分隔符进行分隔，例如SEPARATOR '’,则表示column1将会以进行分隔。如果没有指定seq的时候，也即没有写SEPARATOR seq这个句段，那么就会默认是以,分隔的。
CONCAT函数中要连接的数据含有NULL，最后返回的是NULL，但是GROUP_CONCAT不会这样，他会忽略NULL值。