MySQL学习笔记
<blockquote><p>本篇摘录自黑马程序员的B站教学视频，由本人学习视频内容后总结并提取摘要制作而成的简要笔记。<br>本笔记只记录到进阶篇（大部分），剩下的进阶篇以及运维篇由于本人职业生涯没有用武之地，所以没有进行学习。</p>
<strong>黑马程序员</strong><cite>黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括</cite></blockquote>
<p>在此非常感谢两位热爱学习的小伙伴的鼎力相助，帮助我们完成了所有的章节内容，使得本篇超长笔记得以完结。至此，整个MySQL笔记从入门到高级的所有内容都已编写完毕。</p>
<p>特别感谢：</p>
<ul>
<li>wlh (wen-lh) - Gitee.com</li>
<li>B站同学：@守心-人</li>
</ul>
<h1 id="基础篇">基础篇</h1>
<h2 id="通用语法及分类">通用语法及分类</h2>
<ul>
<li>DDL: 数据定义语言，用来定义数据库对象（数据库、表、字段）</li>
<li>DML: 数据操作语言，用来对数据库表中的数据进行增删改</li>
<li>DQL: 数据查询语言，用来查询数据库中表的记录</li>
<li>DCL: 数据控制语言，用来创建数据库用户、控制数据库的控制权限</li>
</ul>
<h3 id="DDL（数据定义语言）">DDL（数据定义语言）</h3>
<p>数据定义语言</p>
<h4 id="数据库操作">数据库操作</h4>
<p>查询所有数据库：<br><code>SHOW DATABASES;</code><br>查询当前数据库：<br><code>SELECT DATABASE();</code><br>创建数据库：<br><code>CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] ;</code><br>删除数据库：<br><code>DROP DATABASE [ IF EXISTS ] 数据库名;</code><br>使用数据库：<br><code>USE 数据库名;</code></p>
<h5 id="注意事项">注意事项</h5>
<ul>
<li>UTF8字符集长度为3字节，有些符号占4字节，所以推荐用utf8mb4字符集</li>
</ul>
<h4 id="表操作">表操作</h4>
<p>查询当前数据库所有表：<br><code>SHOW TABLES;</code><br>查询表结构：<br><code>DESC 表名;</code><br>查询指定表的建表语句：<br><code>SHOW CREATE TABLE 表名;</code></p>
<p>创建表：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">CREATE TABLE 表名(<br><span class="line"> 字段1 字段1类型 ,<br><span class="line"> 字段2 字段2类型 ,<br><span class="line"> 字段3 字段3类型 ,<br><span class="line"> ...<br><span class="line"> 字段n 字段n类型 <br><span class="line">)[ COMMENT 表注释 ];<br></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>最后一个字段后面没有逗号</strong></p>
<p>添加字段：<br><code>ALTER TABLE 表名 ADD 字段名 类型(长度) [约束];</code><br>例：<code>ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';</code></p>
<p>修改数据类型：<br><code>ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);</code><br>修改字段名和字段类型：<br><code>ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [约束];</code><br>例：将emp表的nickname字段修改为username，类型为varchar(30)<br><code>ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';</code></p>
<p>删除字段：<br><code>ALTER TABLE 表名 DROP 字段名;</code></p>
<p>修改表名：<br><code>ALTER TABLE 表名 RENAME TO 新表名</code></p>
<p>删除表：<br><code>DROP TABLE 表名;</code><br>删除表，并重新创建该表：<br><code>TRUNCATE TABLE 表名;</code></p>
<h3 id="DML（数据操作语言）">DML（数据操作语言）</h3>
<h4 id="添加数据">添加数据</h4>
<p>指定字段：<br><code>INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);</code><br>全部字段：<br><code>INSERT INTO 表名 VALUES (值1, 值2, ...);</code></p>
<p>批量添加数据：<br><code>INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);</code><br><code>INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);</code></p>
<h5 id="注意事项-1">注意事项</h5>
<ul>
<li>字符串和日期类型数据应该包含在引号中</li>
<li>插入的数据大小应该在字段的规定范围内</li>
</ul>
<h4 id="更新和删除数据">更新和删除数据</h4>
<p>修改数据：<br><code>UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];</code><br>例：<br><code>UPDATE emp SET name = 'Jack' WHERE id = 1;</code></p>
<p>删除数据：<br><code>DELETE FROM 表名 [ WHERE 条件 ];</code></p>
<h3 id="DQL（数据查询语言）">DQL（数据查询语言）</h3>
<p>语法：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">SELECT<br><span class="line"> 字段列表<br><span class="line">FROM<br><span class="line"> 表名字段<br><span class="line">WHERE<br><span class="line"> 条件列表<br><span class="line">GROUP BY<br><span class="line"> 分组字段列表<br><span class="line">HAVING<br><span class="line"> 分组后的条件列表<br><span class="line">ORDER BY<br><span class="line"> 排序字段列表<br><span class="line">LIMIT<br><span class="line"> 分页参数<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="基础查询">基础查询</h4>
<p>查询多个字段：<br><code>SELECT 字段1, 字段2, 字段3, ... FROM 表名;</code><br><code>SELECT * FROM 表名;</code></p>
<p>设置别名：<br><code>SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;</code><br><code>SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;</code></p>
<p>去除重复记录：<br><code>SELECT DISTINCT 字段列表 FROM 表名;</code></p>
<p>转义：<br><code>SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'</code><br>/ 之后的_不作为通配符</p>
<h4 id="条件查询">条件查询</h4>
<p>语法：<br><code>SELECT 字段列表 FROM 表名 WHERE 条件列表;</code></p>
<p>条件：</p>
<div class="table-container">
<table>
<thead>
<tr><th>比较运算符</th><th>功能</th>
</tr>
</thead>
<tbody>
<tr>
<td>></td>
<td>大于</td>
</tr>
<tr>
<td>>=</td>
<td>大于等于</td>
</tr>
<tr>
<td><</td>
<td>小于</td>
</tr>
<tr>
<td><=</td>
<td>小于等于</td>
</tr>
<tr>
<td>=</td>
<td>等于</td>
</tr>
<tr>
<td><> 或 !=</td>
<td>不等于</td>
</tr>
<tr>
<td>BETWEEN … AND …</td>
<td>在某个范围内（含最小、最大值）</td>
</tr>
<tr>
<td>IN(…)</td>
<td>在in之后的列表中的值，多选一</td>
</tr>
<tr>
<td>LIKE 占位符</td>
<td>模糊匹配（_匹配单个字符，%匹配任意个字符）</td>
</tr>
<tr>
<td>IS NULL</td>
<td>是NULL</td>
</tr>
</tbody>
</table>
</div>
<div class="table-container">
<table>
<thead>
<tr><th>逻辑运算符</th><th>功能</th>
</tr>
</thead>
<tbody>
<tr>
<td>AND 或 &&</td>
<td>并且（多个条件同时成立）</td>
</tr>
<tr>
<td>OR 或 ||</td>
<td>或者（多个条件任意一个成立）</td>
</tr>
<tr>
<td>NOT 或 !</td>
<td>非，不是</td>
</tr>
</tbody>
</table>
</div>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 年龄等于30<br><span class="line">select * from employee where age = 30;<br><span class="line">-- 年龄小于30<br><span class="line">select * from employee where age < 30;<br><span class="line">-- 小于等于<br><span class="line">select * from employee where age <= 30;<br><span class="line">-- 没有身份证<br><span class="line">select * from employee where idcard is null or idcard = '';<br><span class="line">-- 有身份证<br><span class="line">select * from employee where idcard;<br><span class="line">select * from employee where idcard is not null;<br><span class="line">-- 不等于<br><span class="line">select * from employee where age != 30;<br><span class="line">-- 年龄在20到30之间<br><span class="line">select * from employee where age between 20 and 30;<br><span class="line">select * from employee where age >= 20 and age <= 30;<br><span class="line">-- 下面语句不报错，但查不到任何信息<br><span class="line">select * from employee where age between 30 and 20;<br><span class="line">-- 性别为女且年龄小于30<br><span class="line">select * from employee where age < 30 and gender = '女';<br><span class="line">-- 年龄等于25或30或35<br><span class="line">select * from employee where age = 25 or age = 30 or age = 35;<br><span class="line">select * from employee where age in (25, 30, 35);<br><span class="line">-- 姓名为两个字<br><span class="line">select * from employee where name like '__';<br><span class="line">-- 身份证最后为X<br><span class="line">select * from employee where idcard like '%X';<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="聚合查询（聚合函数）">聚合查询（聚合函数）</h4>
<p>常见聚合函数：</p>
<div class="table-container">
<table>
<thead>
<tr><th>函数</th><th>功能</th></tr>
</thead>
<tbody>
<tr>
<td>count</td>
<td>统计数量</td>
</tr>
<tr>
<td>max</td>
<td>最大值</td>
</tr>
<tr>
<td>min</td>
<td>最小值</td>
</tr>
<tr>
<td>avg</td>
<td>平均值</td>
</tr>
<tr>
<td>sum</td>
<td>求和</td>
</tr>
</tbody>
</table>
</div>
<p>语法：<br><code>SELECT 聚合函数(字段列表) FROM 表名;</code><br>例：<br><code>SELECT count(id) from employee where workaddress = "广东省";</code></p>
<h4 id="分组查询">分组查询</h4>
<p>语法：<br><code>SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];</code></p>
<p>where 和 having 的区别：</p>
<ul>
<li>执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。</li>
<li>判断条件不同：where不能对聚合函数进行判断，而having可以。</li>
</ul>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 根据性别分组，统计男性和女性数量（只显示分组数量，不显示哪个是男哪个是女）<br><span class="line">select count(*) from employee group by gender;<br><span class="line">-- 根据性别分组，统计男性和女性数量<br><span class="line">select gender, count(*) from employee group by gender;<br><span class="line">-- 根据性别分组，统计男性和女性的平均年龄<br><span class="line">select gender, avg(age) from employee group by gender;<br><span class="line">-- 年龄小于45，并根据工作地址分组<br><span class="line">select workaddress, count(*) from employee where age < 45 group by workaddress;<br><span class="line">-- 年龄小于45，并根据工作地址分组，获取员工数量大于等于3的工作地址<br><span class="line">select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3;<br></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h5 id="注意事项-2">注意事项</h5>
<ul>
<li>执行顺序：where > 聚合函数 > having</li>
<li>分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义</li>
</ul>
<h4 id="排序查询">排序查询</h4>
<p>语法：<br><code>SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;</code></p>
<p>排序方式：</p>
<ul>
<li>ASC: 升序（默认）</li>
<li>DESC: 降序</li>
</ul>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 根据年龄升序排序<br><span class="line">SELECT * FROM employee ORDER BY age ASC;<br><span class="line">SELECT * FROM employee ORDER BY age;<br><span class="line">-- 两字段排序，根据年龄升序排序，入职时间降序排序<br><span class="line">SELECT * FROM employee ORDER BY age ASC, entrydate DESC;<br></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h5 id="注意事项-3">注意事项</h5>
<p>如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序</p>
<h4 id="分页查询">分页查询</h4>
<p>语法：<br><code>SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;</code></p>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 查询第一页数据，展示10条<br><span class="line">SELECT * FROM employee LIMIT 0, 10;<br><span class="line">-- 查询第二页<br><span class="line">SELECT * FROM employee LIMIT 10, 10;<br></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h5 id="注意事项-4">注意事项</h5>
<ul>
<li>起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数</li>
<li>分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT</li>
<li>如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10</li>
</ul>
<h4 id="DQL执行顺序">DQL执行顺序</h4>
<p>FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT</p>
<h3 id="DCL">DCL</h3>
<h4 id="管理用户">管理用户</h4>
<p>查询用户：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">USE mysql;<br><span class="line">SELECT * FROM user;<br></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p>创建用户:<br><code>CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';</code></p>
<p>修改用户密码：<br><code>ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';</code></p>
<p>删除用户：<br><code>DROP USER '用户名'@'主机名';</code></p>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 创建用户test，只能在当前主机localhost访问<br><span class="line">create user 'test'@'localhost' identified by '123456';<br><span class="line">-- 创建用户test，能在任意主机访问<br><span class="line">create user 'test'@'%' identified by '123456';<br><span class="line">create user 'test' identified by '123456';<br><span class="line">-- 修改密码<br><span class="line">alter user 'test'@'localhost' identified with mysql_native_password by '1234';<br><span class="line">-- 删除用户<br><span class="line">drop user 'test'@'localhost';<br></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h5 id="注意事项-5">注意事项</h5>
<ul>
<li>主机名可以使用 % 通配</li>
</ul>
<h4 id="权限控制">权限控制</h4>
<p>常用权限：</p>
<div class="table-container">
<table>
<thead>
<tr><th>权限</th><th>说明</th></tr>
</thead>
<tbody>
<tr>
<td>ALL, ALL PRIVILEGES</td>
<td>所有权限</td>
</tr>
<tr>
<td>SELECT</td>
<td>查询数据</td>
</tr>
<tr>
<td>INSERT</td>
<td>插入数据</td>
</tr>
<tr>
<td>UPDATE</td>
<td>修改数据</td>
</tr>
<tr>
<td>DELETE</td>
<td>删除数据</td>
</tr>
<tr>
<td>ALTER</td>
<td>修改表</td>
</tr>
<tr>
<td>DROP</td>
<td>删除数据库/表/视图</td>
</tr>
<tr>
<td>CREATE</td>
<td>创建数据库/表</td>
</tr>
</tbody>
</table>
</div>
<p>更多权限请看权限一览表</p>
<p>查询权限：<br><code>SHOW GRANTS FOR '用户名'@'主机名';</code></p>
<p>授予权限：<br><code>GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';</code></p>
<p>撤销权限：<br><code>REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';</code></p>
<h5 id="注意事项-6">注意事项</h5>
<ul>
<li>多个权限用逗号分隔</li>
<li>授权时，数据库名和表名可以用 * 进行通配，代表所有</li>
</ul>
<h2 id="函数">函数</h2>
<ul>
<li>字符串函数</li>
<li>数值函数</li>
<li>日期函数</li>
<li>流程函数</li>
</ul>
<h3 id="字符串函数">字符串函数</h3>
<p>常用函数：</p>
<div class="table-container">
<table>
<thead>
<tr><th>函数</th><th>功能</th>
</tr>
</thead>
<tbody>
<tr>
<td>CONCAT(s1, s2, …, sn)</td>
<td>字符串拼接，将s1, s2, …, sn拼接成一个字符串</td>
</tr>
<tr>
<td>LOWER(str)</td>
<td>将字符串全部转为小写</td>
</tr>
<tr>
<td>UPPER(str)</td>
<td>将字符串全部转为大写</td>
</tr>
<tr>
<td>LPAD(str, n, pad)</td>
<td>左填充，用字符串pad对str的左边进行填充，达到n个字符串长度</td>
</tr>
<tr>
<td>RPAD(str, n, pad)</td>
<td>右填充，用字符串pad对str的右边进行填充，达到n个字符串长度</td>
</tr>
<tr>
<td>TRIM(str)</td>
<td>去掉字符串头部和尾部的空格</td>
</tr>
<tr>
<td>SUBSTRING(str, start, len)</td>
<td>返回从字符串str从start位置起的len个长度的字符串</td>
</tr>
<tr>
<td>REPLACE(column, source, replace)</td>
<td>替换字符串</td>
</tr>
</tbody>
</table>
</div>
<p>使用示例：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 拼接<br><span class="line">SELECT CONCAT('Hello', 'World');<br><span class="line">-- 小写<br><span class="line">SELECT LOWER('Hello');<br><span class="line">-- 大写<br><span class="line">SELECT UPPER('Hello');<br><span class="line">-- 左填充<br><span class="line">SELECT LPAD('01', 5, '-');<br><span class="line">-- 右填充<br><span class="line">SELECT RPAD('01', 5, '-');<br><span class="line">-- 去除空格<br><span class="line">SELECT TRIM(' Hello World ');<br><span class="line">-- 切片（起始索引为1）<br><span class="line">SELECT SUBSTRING('Hello World', 1, 5);<br><span class="line"><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="数值函数">数值函数</h3>
<p>常见函数：</p>
<div class="table-container">
<table>
<thead>
<tr><th>函数</th><th>功能</th></tr>
</thead>
<tbody>
<tr>
<td>CEIL(x)</td>
<td>向上取整</td>
</tr>
<tr>
<td>FLOOR(x)</td>
<td>向下取整</td>
</tr>
<tr>
<td>MOD(x, y)</td>
<td>返回x/y的模</td>
</tr>
<tr>
<td>RAND()</td>
<td>返回0~1内的随机数</td>
</tr>
<tr>
<td>ROUND(x, y)</td>
<td>求参数x的四舍五入值，保留y位小数</td>
</tr>
</tbody>
</table>
</div>
<h3 id="日期函数">日期函数</h3>
<p>常用函数：</p>
<div class="table-container">
<table>
<thead>
<tr><th>函数</th><th>功能</th></tr>
</thead>
<tbody>
<tr>
<td>CURDATE()</td>
<td>返回当前日期</td>
</tr>
<tr>
<td>CURTIME()</td>
<td>返回当前时间</td>
</tr>
<tr>
<td>NOW()</td>
<td>返回当前日期和时间</td>
</tr>
<tr>
<td>YEAR(date)</td>
<td>获取指定date的年份</td>
</tr>
<tr>
<td>MONTH(date)</td>
<td>获取指定date的月份</td>
</tr>
<tr>
<td>DAY(date)</td>
<td>获取指定date的日期</td>
</tr>
<tr>
<td>DATE_ADD(date, INTERVAL expr type)</td>
<td>返回一个日期/时间值加上一个时间间隔expr后的时间值</td>
</tr>
<tr>
<td>DATEDIFF(date1, date2)</td>
<td>返回起始时间date1和结束时间date2之间的天数</td>
</tr>
</tbody>
</table>
</div>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- DATE_ADD<br><span class="line">SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR);<br><span class="line"><br></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="流程函数">流程函数</h3>
<p>常用函数：</p>
<div class="table-container">
<table>
<thead>
<tr><th>函数</th><th>功能</th></tr>
</thead>
<tbody>
<tr>
<td>IF(value, t, f)</td>
<td>如果value为true，则返回t，否则返回f</td>
</tr>
<tr>
<td>IFNULL(value1, value2)</td>
<td>如果value1不为空，返回value1，否则返回value2</td>
</tr>
<tr>
<td>CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END</td>
<td>如果val1为true，返回res1，… 否则返回default默认值</td>
</tr>
<tr>
<td>CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END</td>
<td>如果expr的值等于val1，返回res1，… 否则返回default默认值</td>
</tr>
</tbody>
</table>
</div>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">select<br><span class="line"> name,<br><span class="line"> (case when age > 30 then '中年' else '青年' end)<br><span class="line">from employee;<br><span class="line">select<br><span class="line"> name,<br><span class="line"> (case workaddress when '北京市' then '一线城市' when '上海市' then '一线城市' else '二线城市' end) as '工作地址'<br><span class="line">from employee;<br><span class="line"><br></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h2 id="约束">约束</h2>
<p>分类：</p>
<div class="table-container">
<table>
<thead>
<tr><th>约束</th><th>描述</th><th>关键字</th></tr>
</thead>
<tbody>
<tr>
<td>非空约束</td>
<td>限制该字段的数据不能为null</td>
<td>NOT NULL</td>
</tr>
<tr>
<td>唯一约束</td>
<td>保证该字段的所有数据都是唯一、不重复的</td>
<td>UNIQUE</td>
</tr>
<tr>
<td>主键约束</td>
<td>主键是一行数据的唯一标识，要求非空且唯一</td>
<td>PRIMARY KEY</td>
</tr>
<tr>
<td>默认约束</td>
<td>保存数据时，如果未指定该字段的值，则采用默认值</td>
<td>DEFAULT</td>
</tr>
<tr>
<td>检查约束（8.0.1版本后）</td>
<td>保证字段值满足某一个条件</td>
<td>CHECK</td>
</tr>
<tr>
<td>外键约束</td>
<td>用来让两张图的数据之间建立连接，保证数据的一致性和完整性</td>
<td>FOREIGN KEY</td>
</tr>
</tbody>
</table>
</div>
<p>约束是作用于表中字段上的，可以再创建表/修改表的时候添加约束。</p>
<h3 id="常用约束">常用约束</h3>
<div class="table-container">
<table>
<thead>
<tr><th>约束条件</th><th>关键字</th></tr>
</thead>
<tbody>
<tr>
<td>主键</td>
<td>PRIMARY KEY</td>
</tr>
<tr>
<td>自动增长</td>
<td>AUTO_INCREMENT</td>
</tr>
<tr>
<td>不为空</td>
<td>NOT NULL</td>
</tr>
<tr>
<td>唯一</td>
<td>UNIQUE</td>
</tr>
<tr>
<td>逻辑条件</td>
<td>CHECK</td>
</tr>
<tr>
<td>默认值</td>
<td>DEFAULT</td>
</tr>
</tbody>
</table>
</div>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">create table user(<br><span class="line"> id int primary key auto_increment,<br><span class="line"> name varchar(10) not null unique,<br><span class="line"> age int check(age > 0 and age < 120),<br><span class="line"> status char(1) default '1',<br><span class="line"> gender char(1)<br><span class="line">);<br><span class="line"><br></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="外键约束">外键约束</h3>
<p>添加外键：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">CREATE TABLE 表名(<br><span class="line"> 字段名 字段类型,<br><span class="line"> ...<br><span class="line"> [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)<br><span class="line">);<br><span class="line">ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);<br><span class="line"><br><span class="line">-- 例子<br><span class="line">alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);<br><span class="line"><br></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p>删除外键：<br><code>ALTER TABLE 表名 DROP FOREIGN KEY 外键名;</code></p>
<h4 id="删除-更新行为">删除/更新行为</h4>
<div class="table-container">
<table>
<thead>
<tr><th>行为</th><th>说明</th>
</tr>
</thead>
<tbody>
<tr>
<td>NO ACTION</td>
<td>当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与RESTRICT一致）</td>
</tr>
<tr>
<td>RESTRICT</td>
<td>当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与NO ACTION一致）</td>
</tr>
<tr>
<td>CASCADE</td>
<td>当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录</td>
</tr>
<tr>
<td>SET NULL</td>
<td>当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为null）</td>
</tr>
<tr>
<td>SET DEFAULT</td>
<td>父表有变更时，子表将外键设为一个默认值（Innodb不支持）</td>
</tr>
</tbody>
</table>
</div>
<p>更改删除/更新行为：<br><code>ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;</code></p>
<h2 id="多表查询">多表查询</h2>
<h3 id="多表关系">多表关系</h3>
<ul>
<li>一对多（多对一）</li>
<li>多对多</li>
<li>一对一</li>
</ul>
<h4 id="一对多">一对多</h4>
<p>案例：部门与员工<br>关系：一个部门对应多个员工，一个员工对应一个部门<br>实现：在多的一方建立外键，指向一的一方的主键</p>
<h4 id="多对多">多对多</h4>
<p>案例：学生与课程<br>关系：一个学生可以选多门课程，一门课程也可以供多个学生选修<br>实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键</p>
<h4 id="一对一">一对一</h4>
<p>案例：用户与用户详情<br>关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率<br>实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）</p>
<h3 id="查询">查询</h3>
<p>合并查询（笛卡尔积，会展示所有组合结果）：<br><code>select * from employee, dept;</code></p>
<blockquote>
<p>笛卡尔积：两个集合A集合和B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）</p>
</blockquote>
<p>消除无效笛卡尔积：<br><code>select * from employee, dept where employee.dept = dept.id;</code></p>
<h3 id="内连接查询">内连接查询</h3>
<p>内连接查询的是两张表交集的部分</p>
<p>隐式内连接：<br><code>SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;</code></p>
<p>显式内连接：<br><code>SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;</code></p>
<p>显式性能比隐式高</p>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 查询员工姓名，及关联的部门的名称<br><span class="line">-- 隐式<br><span class="line">select e.name, d.name from employee as e, dept as d where e.dept = d.id;<br><span class="line">-- 显式<br><span class="line">select e.name, d.name from employee as e inner join dept as d on e.dept = d.id;<br><span class="line"><br></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="外连接查询">外连接查询</h3>
<p>左外连接：<br>查询左表所有数据，以及两张表交集部分数据<br><code>SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;</code><br>相当于查询表1的所有数据，包含表1和表2交集部分数据</p>
<p>右外连接：<br>查询右表所有数据，以及两张表交集部分数据<br><code>SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;</code></p>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 左<br><span class="line">select e.*, d.name from employee as e left outer join dept as d on e.dept = d.id;<br><span class="line">select d.name, e.* from dept d left outer join emp e on e.dept = d.id;-- 这条语句与下面的语句效果一样<br><span class="line">-- 右<br><span class="line">select d.name, e.* from employee as e right outer join dept as d on e.dept = d.id;<br><span class="line"><br></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p>左连接可以查询到没有dept的employee，右连接可以查询到没有employee的dept</p>
<h3 id="自连接查询">自连接查询</h3>
<p>当前表与自身的连接查询，自连接必须使用表别名</p>
<p>语法：<br><code>SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;</code></p>
<p>自连接查询，可以是内连接查询，也可以是外连接查询</p>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 查询员工及其所属领导的名字<br><span class="line">select a.name, b.name from employee a, employee b where a.manager = b.id;<br><span class="line">-- 没有领导的也查询出来<br><span class="line">select a.name, b.name from employee a left join employee b on a.manager = b.id;<br><span class="line"><br></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="联合查询-union-union-all">联合查询 union, union all</h3>
<p>把多次查询的结果合并，形成一个新的查询集</p>
<p>语法：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">SELECT 字段列表 FROM 表A ...<br><span class="line">UNION <br><span class="line">SELECT 字段列表 FROM 表B ...<br><span class="line"><br></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="注意事项-7">注意事项</h4>
<ul>
<li>UNION ALL 会有重复结果，UNION 不会</li>
<li>联合查询比使用or效率高，不会使索引失效</li>
</ul>
<h3 id="子查询">子查询</h3>
<p>SQL语句中嵌套SELECT语句，称谓嵌套查询，又称子查询。<br><code>SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);</code><br><strong>子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个</strong></p>
<p>根据子查询结果可以分为：</p>
<ul>
<li>标量子查询（子查询结果为单个值）</li>
<li>列子查询（子查询结果为一列）</li>
<li>行子查询（子查询结果为一行）</li>
<li>表子查询（子查询结果为多行多列）</li>
</ul>
<p>根据子查询位置可分为：</p>
<ul>
<li>WHERE 之后</li>
<li>FROM 之后</li>
<li>SELECT 之后</li>
</ul>
<h4 id="标量子查询">标量子查询</h4>
<p>子查询返回的结果是单个值（数字、字符串、日期等）。<br>常用操作符：- < > > >= < <=</p>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 查询销售部所有员工<br><span class="line">select id from dept where name = '销售部';<br><span class="line">-- 根据销售部部门ID，查询员工信息<br><span class="line">select * from employee where dept = 4;<br><span class="line">-- 合并（子查询）<br><span class="line">select * from employee where dept = (select id from dept where name = '销售部');<br><span class="line"><br><span class="line">-- 查询xxx入职之后的员工信息<br><span class="line">select * from employee where entrydate > (select entrydate from employee where name = 'xxx');<br><span class="line"><br></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="列子查询">列子查询</h4>
<p>返回的结果是一列（可以是多行）。</p>
<p>常用操作符：</p>
<div class="table-container">
<table>
<thead>
<tr><th>操作符</th><th>描述</th></tr>
</thead>
<tbody>
<tr>
<td>IN</td>
<td>在指定的集合范围内，多选一</td>
</tr>
<tr>
<td>NOT IN</td>
<td>不在指定的集合范围内</td>
</tr>
<tr>
<td>ANY</td>
<td>子查询返回列表中，有任意一个满足即可</td>
</tr>
<tr>
<td>SOME</td>
<td>与ANY等同，使用SOME的地方都可以使用ANY</td>
</tr>
<tr>
<td>ALL</td>
<td>子查询返回列表的所有值都必须满足</td>
</tr>
</tbody>
</table>
</div>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 查询销售部和市场部的所有员工信息<br><span class="line">select * from employee where dept in (select id from dept where name = '销售部' or name = '市场部');<br><span class="line">-- 查询比财务部所有人工资都高的员工信息<br><span class="line">select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '财务部'));<br><span class="line">-- 查询比研发部任意一人工资高的员工信息<br><span class="line">select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研发部'));<br><span class="line"><br></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="行子查询">行子查询</h4>
<p>返回的结果是一行（可以是多列）。<br>常用操作符：=, <, >, IN, NOT IN</p>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 查询与xxx的薪资及直属领导相同的员工信息<br><span class="line">select * from employee where (salary, manager) = (12500, 1);<br><span class="line">select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');<br><span class="line"><br></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="表子查询">表子查询</h4>
<p>返回的结果是多行多列<br>常用操作符：IN</p>
<p>例子：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 查询与xxx1，xxx2的职位和薪资相同的员工<br><span class="line">select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');<br><span class="line">-- 查询入职日期是2006-01-01之后的员工，及其部门信息<br><span class="line">select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;<br><span class="line"><br></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h2 id="事务">事务</h2>
<p>事务是一组操作的集合，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。</p>
<p>基本操作：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 1. 查询张三账户余额<br><span class="line">select * from account where name = '张三';<br><span class="line">-- 2. 将张三账户余额-1000<br><span class="line">update account set money = money - 1000 where name = '张三';<br><span class="line">-- 此语句出错后张三钱减少但是李四钱没有增加<br><span class="line">模拟sql语句错误<br><span class="line">-- 3. 将李四账户余额+1000<br><span class="line">update account set money = money + 1000 where name = '李四';<br><span class="line"><br><span class="line">-- 查看事务提交方式<br><span class="line">SELECT @@AUTOCOMMIT;<br><span class="line">-- 设置事务提交方式，1为自动提交，0为手动提交，该设置只对当前会话有效<br><span class="line">SET @@AUTOCOMMIT = 0;<br><span class="line">-- 提交事务<br><span class="line">COMMIT;<br><span class="line">-- 回滚事务<br><span class="line">ROLLBACK;<br><span class="line"><br><span class="line">-- 设置手动提交后上面代码改为：<br><span class="line">select * from account where name = '张三';<br><span class="line">update account set money = money - 1000 where name = '张三';<br><span class="line">update account set money = money + 1000 where name = '李四';<br><span class="line">commit;<br><span class="line"><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p>操作方式二：</p>
<p>开启事务：<br><code>START TRANSACTION 或 BEGIN TRANSACTION;</code><br>提交事务：<br><code>COMMIT;</code><br>回滚事务：<br><code>ROLLBACK;</code></p>
<p>操作实例：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">start transaction;<br><span class="line">select * from account where name = '张三';<br><span class="line">update account set money = money - 1000 where name = '张三';<br><span class="line">update account set money = money + 1000 where name = '李四';<br><span class="line">commit;<br><span class="line"><br></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="四大特性ACID">四大特性ACID</h3>
<ul>
<li>原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败</li>
<li>一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态</li>
<li>隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行</li>
<li>持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的</li>
</ul>
<h3 id="并发事务">并发事务</h3>
<div class="table-container">
<table>
<thead>
<tr><th>问题</th><th>描述</th></tr>
</thead>
<tbody>
<tr>
<td>脏读</td>
<td>一个事务读到另一个事务还没提交的数据</td>
</tr>
<tr>
<td>不可重复读</td>
<td>一个事务先后读取同一条记录，但两次读取的数据不同</td>
</tr>
<tr>
<td>幻读</td>
<td>一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在</td>
</tr>
</tbody>
</table>
</div>
<blockquote>
<p>这三个问题的详细演示：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=55cd</p>
</blockquote>
<p>并发事务隔离级别：</p>
<div class="table-container">
<table>
<thead>
<tr><th>隔离级别</th><th>脏读</th><th>不可重复读</th><th>幻读</th></tr>
</thead>
<tbody>
<tr>
<td>Read uncommitted</td>
<td>√</td>
<td>√</td>
<td>√</td>
</tr>
<tr>
<td>Read committed</td>
<td>×</td>
<td>√</td>
<td>√</td>
</tr>
<tr>
<td>Repeatable Read(默认)</td>
<td>×</td>
<td>×</td>
<td>√</td>
</tr>
<tr>
<td>Serializable</td>
<td>×</td>
<td>×</td>
<td>×</td>
</tr>
</tbody>
</table>
</div>
<ul>
<li>√表示在当前隔离级别下该问题会出现</li>
<li>Serializable 性能最低；Read uncommitted 性能最高，数据安全性最差</li>
</ul>
<p>查看事务隔离级别：<br><code>SELECT @@TRANSACTION_ISOLATION;</code><br>设置事务隔离级别：<br><code>SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };</code><br>SESSION 是会话级别，表示只针对当前会话有效，GLOBAL 表示对所有会话有效</p>
<h1 id="进阶篇">进阶篇</h1>
<h2 id="存储引擎">存储引擎</h2>
<p>MySQL体系结构：</p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/MySQL%E4%BD%93%E7%B3%BB%E7%BB%93%E6%9E%84.png"><br><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/MySQL%E4%BD%93%E7%B3%BB%E7%BB%93%E6%9E%84%E5%B1%82%E7%BA%A7%E5%90%AB%E4%B9%89.png"></p>
<p>存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。<br>默认存储引擎是InnoDB。</p>
<p>相关操作：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 查询建表语句<br><span class="line">show create table account;<br><span class="line">-- 建表时指定存储引擎<br><span class="line">CREATE TABLE 表名(<br><span class="line"> ...<br><span class="line">) ENGINE=INNODB;<br><span class="line">-- 查看当前数据库支持的存储引擎<br><span class="line">show engines;<br><span class="line"><br></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="InnoDB">InnoDB</h3>
<p>InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎</p>
<p>特点：</p>
<ul>
<li>DML 操作遵循 ACID 模型，支持<strong>事务</strong></li>
<li><strong>行级锁</strong>，提高并发访问性能</li>
<li>支持<strong>外键</strong>约束，保证数据的完整性和正确性</li>
</ul>
<p>文件：</p>
<ul>
<li>xxx.ibd: xxx代表表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。</li>
</ul>
<p>参数：innodb_file_per_table，决定多张表共享一个表空间还是每张表对应一个表空间</p>
<p>知识点：</p>
<p>查看 Mysql 变量：<br><code>show variables like 'innodb_file_per_table';</code></p>
<p>从idb文件提取表结构数据：<br>（在cmd运行）<br><code>ibd2sdi xxx.ibd</code></p>
<p>InnoDB 逻辑存储结构：<br><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E9%80%BB%E8%BE%91%E5%AD%98%E5%82%A8%E7%BB%93%E6%9E%84.png"></p>
<h3 id="MyISAM">MyISAM</h3>
<p>MyISAM 是 MySQL 早期的默认存储引擎。</p>
<p>特点：</p>
<ul>
<li>不支持事务，不支持外键</li>
<li>支持表锁，不支持行锁</li>
<li>访问速度快</li>
</ul>
<p>文件：</p>
<ul>
<li>xxx.sdi: 存储表结构信息</li>
<li>xxx.MYD: 存储数据</li>
<li>xxx.MYI: 存储索引</li>
</ul>
<h3 id="Memory">Memory</h3>
<p>Memory 引擎的表数据是存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。</p>
<p>特点：</p>
<ul>
<li>存放在内存中，速度快</li>
<li>hash索引（默认）</li>
</ul>
<p>文件：</p>
<ul>
<li>xxx.sdi: 存储表结构信息</li>
</ul>
<h3 id="存储引擎特点">存储引擎特点</h3>
<div class="table-container">
<table>
<thead>
<tr><th>特点</th><th>InnoDB</th><th>MyISAM</th><th>Memory</th>
</tr>
</thead>
<tbody>
<tr>
<td>存储限制</td>
<td>64TB</td>
<td>有</td>
<td>有</td>
</tr>
<tr>
<td>事务安全</td>
<td>支持</td>
<td>-</td>
<td>-</td>
</tr>
<tr>
<td>锁机制</td>
<td>行锁</td>
<td>表锁</td>
<td>表锁</td>
</tr>
<tr>
<td>B+tree索引</td>
<td>支持</td>
<td>支持</td>
<td>支持</td>
</tr>
<tr>
<td>Hash索引</td>
<td>-</td>
<td>-</td>
<td>支持</td>
</tr>
<tr>
<td>全文索引</td>
<td>支持（5.6版本之后）</td>
<td>支持</td>
<td>-</td>
</tr>
<tr>
<td>空间使用</td>
<td>高</td>
<td>低</td>
<td>N/A</td>
</tr>
<tr>
<td>内存使用</td>
<td>高</td>
<td>低</td>
<td>中等</td>
</tr>
<tr>
<td>批量插入速度</td>
<td>低</td>
<td>高</td>
<td>高</td>
</tr>
<tr>
<td>支持外键</td>
<td>支持</td>
<td>-</td>
<td>-</td>
</tr>
</tbody>
</table>
</div>
<h3 id="存储引擎的选择">存储引擎的选择</h3>
<p>在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。</p>
<ul>
<li>InnoDB: 如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择</li>
<li>MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的。</li>
<li>Memory: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性</li>
</ul>
<p>电商中的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎。</p>
<h2 id="性能分析">性能分析</h2>
<h3 id="查看执行频次">查看执行频次</h3>
<p>查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次：<br><code>SHOW GLOBAL STATUS LIKE 'Com_______';</code> 或者 <code>SHOW SESSION STATUS LIKE 'Com_______';</code><br>例：<code>show global status like 'Com_______'</code></p>
<h3 id="慢查询日志">慢查询日志</h3>
<p>慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。<br>MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：<br> # 开启慢查询日志开关<br> slow_query_log=1<br> # 设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志<br> long_query_time=2<br>更改后记得重启MySQL服务，日志文件位置：/var/lib/mysql/localhost-slow.log</p>
<p>查看慢查询日志开关状态：<br><code>show variables like 'slow_query_log';</code></p>
<h3 id="profile">profile</h3>
<p>show profile 能在做SQL优化时帮我们了解时间都耗费在哪里。通过 have_profiling 参数，能看到当前 MySQL 是否支持 profile 操作：<br><code>SELECT @@have_profiling;</code><br>profiling 默认关闭，可以通过set语句在session/global级别开启 profiling：<br><code>SET profiling = 1;</code><br>查看所有语句的耗时：<br><code>show profiles;</code><br>查看指定query_id的SQL语句各个阶段的耗时：<br><code>show profile for query query_id;</code><br>查看指定query_id的SQL语句CPU的使用情况<br><code>show profile cpu for query query_id;</code></p>
<h3 id="explain">explain</h3>
<p>EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。<br>语法：<br> # 直接在select语句之前加上关键字 explain / desc<br> EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;</p>
<p>EXPLAIN 各字段含义：</p>
<ul>
<li>id：select 查询的序列号，表示查询中执行 select 子句或者操作表的顺序（id相同，执行顺序从上到下；id不同，值越大越先执行）</li>
<li>select_type：表示 SELECT 的类型，常见取值有
SIMPLE（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等</li>
<li>type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all</li>
<li>possible_key：可能应用在这张表上的索引，一个或多个</li>
<li>Key：实际使用的索引，如果为 NULL，则没有使用索引</li>
<li>Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好</li>
<li>rows：MySQL认为必须要执行的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的</li>
<li>filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好</li>
</ul>
<h2 id="索引">索引</h2>
<p>索引是帮助 MySQL <strong>高效获取数据</strong>的<strong>数据结构（有序）</strong>。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查询算法，这种数据结构就是索引。</p>
<p>优缺点：</p>
<p>优点：</p>
<ul>
<li>提高数据检索效率，降低数据库的IO成本</li>
<li>通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗</li>
</ul>
<p>缺点：</p>
<ul>
<li>索引列也是要占用空间的</li>
<li>索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE</li>
</ul>
<h3 id="索引结构">索引结构</h3>
<div class="table-container">
<table>
<thead>
<tr><th>索引结构</th><th>描述</th>
</tr>
</thead>
<tbody>
<tr>
<td>B+Tree</td>
<td>最常见的索引类型，大部分引擎都支持B+树索引</td>
</tr>
<tr>
<td>Hash</td>
<td>底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询</td>
</tr>
<tr>
<td>R-Tree(空间索引)</td>
<td>空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少</td>
</tr>
<tr>
<td>Full-Text(全文索引)</td>
<td>是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES</td>
</tr>
</tbody>
</table>
</div>
<div class="table-container">
<table>
<thead>
<tr><th>索引</th><th>InnoDB</th><th>MyISAM</th><th>Memory</th>
</tr>
</thead>
<tbody>
<tr>
<td>B+Tree索引</td>
<td>支持</td>
<td>支持</td>
<td>支持</td>
</tr>
<tr>
<td>Hash索引</td>
<td>不支持</td>
<td>不支持</td>
<td>支持</td>
</tr>
<tr>
<td>R-Tree索引</td>
<td>不支持</td>
<td>支持</td>
<td>不支持</td>
</tr>
<tr>
<td>Full-text</td>
<td>5.6版本后支持</td>
<td>支持</td>
<td>不支持</td>
</tr>
</tbody>
</table>
</div>
<h4 id="B-Tree">B-Tree</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E4%BA%8C%E5%8F%89%E6%A0%91.png"></p>
<p>二叉树的缺点可以用红黑树来解决：<br><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E7%BA%A2%E9%BB%91%E6%A0%91.png"><br>红黑树也存在大数据量情况下，层级较深，检索速度慢的问题。</p>
<p>为了解决上述问题，可以使用 B-Tree 结构。<br>B-Tree (多路平衡查找树) 以一棵最大度数（max-degree，指一个节点的子节点个数）为5（5阶）的 b-tree 为例（每个节点最多存储4个key，5个指针）</p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/B-Tree%E7%BB%93%E6%9E%84.png"></p>
<blockquote>
<p>B-Tree 的数据插入过程动画参照：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=68<br>演示地址：https://www.cs.usfca.edu/~galles/visualization/BTree.html</p>
</blockquote>
<h4 id="B-Tree-1">B+Tree</h4>
<p>结构图：</p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/B+Tree%E7%BB%93%E6%9E%84%E5%9B%BE.png"></p>
<blockquote>
<p>演示地址：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html</p>
</blockquote>
<p>与 B-Tree 的区别：</p>
<ul>
<li>所有的数据都会出现在叶子节点</li>
<li>叶子节点形成一个单向链表</li>
</ul>
<p>MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。</p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E7%BB%93%E6%9E%84%E5%9B%BE.png"></p>
<h4 id="Hash">Hash</h4>
<p>哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。<br>如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。</p>
<p><img src="https://jimhackking.github.io/Hash%E7%B4%A2%E5%BC%95%E5%8E%9F%E7%90%86%E5%9B%BE"></p>
<p>特点：</p>
<ul>
<li>Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）</li>
<li>无法利用索引完成排序操作</li>
<li>查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引</li>
</ul>
<p>存储引擎支持：</p>
<ul>
<li>Memory</li>
<li>InnoDB: 具有自适应hash功能，hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的</li>
</ul>
<h4 id="面试题">面试题</h4>
<ol>
<li>为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？</li>
</ol>
<ul>
<li>相对于二叉树，层级更少，搜索效率高</li>
<li>对于 B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低</li>
<li>相对于 Hash 索引，B+Tree 支持范围匹配及排序操作</li>
</ul>
<h3 id="索引分类">索引分类</h3>
<div class="table-container">
<table>
<thead>
<tr><th>分类</th><th>含义</th><th>特点</th><th>关键字</th>
</tr>
</thead>
<tbody>
<tr>
<td>主键索引</td>
<td>针对于表中主键创建的索引</td>
<td>默认自动创建，只能有一个</td>
<td>PRIMARY</td>
</tr>
<tr>
<td>唯一索引</td>
<td>避免同一个表中某数据列中的值重复</td>
<td>可以有多个</td>
<td>UNIQUE</td>
</tr>
<tr>
<td>常规索引</td>
<td>快速定位特定数据</td>
<td>可以有多个</td>
<td> </td>
</tr>
<tr>
<td>全文索引</td>
<td>全文索引查找的是文本中的关键词，而不是比较索引中的值</td>
<td>可以有多个</td>
<td>FULLTEXT</td>
</tr>
</tbody>
</table>
</div>
<p>在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：</p>
<div class="table-container">
<table>
<thead>
<tr><th>分类</th><th>含义</th><th>特点</th>
</tr>
</thead>
<tbody>
<tr>
<td>聚集索引(Clustered Index)</td>
<td>将数据存储与索引放一块，索引结构的叶子节点保存了行数据</td>
<td>必须有，而且只有一个</td>
</tr>
<tr>
<td>二级索引(Secondary Index)</td>
<td>将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键</td>
<td>可以存在多个</td>
</tr>
</tbody>
</table>
</div>
<p>演示图：</p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E5%8E%9F%E7%90%86%E5%9B%BE.png"><br><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E6%BC%94%E7%A4%BA%E5%9B%BE.png"></p>
<p>聚集索引选取规则：</p>
<ul>
<li>如果存在主键，主键索引就是聚集索引</li>
<li>如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引</li>
<li>如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引</li>
</ul>
<h4 id="思考题">思考题</h4>
<p>1. 以下 SQL 语句，哪个执行效率高？为什么？</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">select * from user where id = 10;<br><span class="line">select * from user where name = 'Arm';<br><span class="line">-- 备注：id为主键，name字段创建的有索引<br><span class="line"><br></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p>答：第一条语句，因为第二条需要回表查询，相当于两个步骤。</p>
<p>2. InnoDB 主键索引的 B+Tree 高度为多少？</p>
<p>答：假设一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB 的指针占用6个字节的空间，主键假设为bigint，占用字节数为8.<br>可得公式：<code>n * 8 + (n + 1) * 6 = 16 * 1024</code>，其中 8 表示 bigint 占用的字节数，n 表示当前节点存储的key的数量，(n + 1) 表示指针数量（比key多一个）。算出n约为1170。</p>
<p>如果树的高度为2，那么他能存储的数据量大概为：<code>1171 * 16 = 18736</code>；<br>如果树的高度为3，那么他能存储的数据量大概为：<code>1171 * 1171 * 16 = 21939856</code>。</p>
<p>另外，如果有成千上万的数据，那么就要考虑分表，涉及运维篇知识。</p>
<h3 id="语法">语法</h3>
<p>创建索引：<br><code>CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);</code><br>如果不加 CREATE 后面不加索引类型参数，则创建的是常规索引</p>
<p>查看索引：<br><code>SHOW INDEX FROM table_name;</code></p>
<p>删除索引：<br><code>DROP INDEX index_name ON table_name;</code></p>
<p>案例：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引<br><span class="line">create index idx_user_name on tb_user(name);<br><span class="line">-- phone手机号字段的值非空，且唯一，为该字段创建唯一索引<br><span class="line">create unique index idx_user_phone on tb_user (phone);<br><span class="line">-- 为profession, age, status创建联合索引<br><span class="line">create index idx_user_pro_age_stat on tb_user(profession, age, status);<br><span class="line">-- 为email建立合适的索引来提升查询效率<br><span class="line">create index idx_user_email on tb_user(email);<br><span class="line"><br><span class="line">-- 删除索引<br><span class="line">drop index idx_user_email on tb_user;<br><span class="line"><br></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="使用规则">使用规则</h3>
<h4 id="最左前缀法则">最左前缀法则</h4>
<p>如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。<br>如果跳跃某一列，索引将部分失效（后面的字段索引失效）。</p>
<p>联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。</p>
<h4 id="索引失效情况">索引失效情况</h4>
<ol>
<li>在索引列上进行运算操作，索引将失效。如：<code>explain select * from tb_user where substring(phone, 10, 2) = '15';</code></li>
<li>字符串类型字段使用时，不加引号，索引将失效。如：<code>explain select * from tb_user where phone = 17799990015;</code>，此处phone的值没有加引号</li>
<li>模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：<code>explain select * from tb_user where profession like '%工程';</code>，前后都有 % 也会失效。</li>
<li>用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。</li>
<li>如果 MySQL 评估使用索引比全表更慢，则不使用索引。</li>
</ol>
<h4 id="SQL-提示">SQL 提示</h4>
<p>是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。</p>
<p>例如，使用索引：<br><code>explain select * from tb_user use index(idx_user_pro) where profession="软件工程";</code><br>不使用哪个索引：<br><code>explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";</code><br>必须使用哪个索引：<br><code>explain select * from tb_user force index(idx_user_pro) where profession="软件工程";</code></p>
<p>use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改，force就是无论如何都强制使用该索引。</p>
<h4 id="覆盖索引-回表查询">覆盖索引&回表查询</h4>
<p>尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少 select *。</p>
<p>explain 中 extra 字段含义：<br><code>using index condition</code>：查找使用了索引，但是需要回表查询数据<br><code>using where; using index;</code>：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询</p>
<p>如果在聚集索引中直接能找到对应的行，则直接返回行数据，只需要一次查询，哪怕是select *；如果在辅助索引中找聚集索引，如<code>select id, name from xxx where name='xxx';</code>，也只需要通过辅助索引(name)查找到对应的id，返回name和name索引对应的id即可，只需要一次查询；如果是通过辅助索引查找其他字段，则需要回表查询，如<code>select id, name, gender from xxx where name='xxx';</code></p>
<p>所以尽量不要用<code>select *</code>，容易出现回表查询，降低效率，除非有联合索引包含了所有字段</p>
<p>面试题：一张表，有四个字段（id, username, password, status），由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案：<br><code>select id, username, password from tb_user where username='itcast';</code></p>
<p>解：给username和password字段建立联合索引，则不需要回表查询，直接覆盖索引</p>
<h4 id="前缀索引">前缀索引</h4>
<p>当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只降字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。</p>
<p>语法：<code>create index idx_xxxx on table_name(columnn(n));</code><br>前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。<br>求选择性公式：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">select count(distinct email) / count(*) from tb_user;<br><span class="line">select count(distinct substring(email, 1, 5)) / count(*) from tb_user;<br><span class="line"><br></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p>show index 里面的sub_part可以看到接取的长度</p>
<h4 id="单列索引-联合索引">单列索引&联合索引</h4>
<p>单列索引：即一个索引只包含单个列<br>联合索引：即一个索引包含了多个列<br>在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。</p>
<p>单列索引情况：<br><code>explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';</code><br>这句只会用到phone索引字段</p>
<h5 id="注意事项-8">注意事项</h5>
<ul>
<li>多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询</li>
</ul>
<h3 id="设计原则">设计原则</h3>
<ol>
<li>针对于数据量较大，且查询比较频繁的表建立索引</li>
<li>针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引</li>
<li>尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高</li>
<li>如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引</li>
<li>尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率</li>
<li>要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率</li>
<li>如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询</li>
</ol>
<h2 id="SQL-优化">SQL 优化</h2>
<h3 id="插入数据">插入数据</h3>
<p>普通插入：</p>
<ol>
<li>采用批量插入（一次插入的数据不建议超过1000条）</li>
<li>手动提交事务</li>
<li>主键顺序插入</li>
</ol>
<p>大批量插入：<br>如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入。</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"># 客户端连接服务端时，加上参数 --local-infile（这一行在bash/cmd界面输入）<br><span class="line">mysql --local-infile -u root -p<br><span class="line"># 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关<br><span class="line">set global local_infile = 1;<br><span class="line">select @@local_infile;<br><span class="line"># 执行load指令将准备好的数据，加载到表结构中<br><span class="line">load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';<br><span class="line"><br></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="主键优化">主键优化</h3>
<p>数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index organized table, IOT）</p>
<p>页分裂：页可以为空，也可以填充一般，也可以填充100%，每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。<br>页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达
MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前后）看看是否可以将这两个页合并以优化空间使用。</p>
<p>MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或创建索引时指定</p>
<blockquote>
<p>文字说明不够清晰明了，具体可以看视频里的PPT演示过程：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=90</p>
</blockquote>
<p>主键设计原则：</p>
<ul>
<li>满足业务需求的情况下，尽量降低主键的长度</li>
<li>插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键</li>
<li>尽量不要使用 UUID 做主键或者是其他的自然主键，如身份证号</li>
<li>业务操作时，避免对主键的修改</li>
</ul>
<h3 id="order-by优化">order by优化</h3>
<ol>
<li>Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序</li>
<li>Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高</li>
</ol>
<p>如果order by字段全部使用升序排序或者降序排序，则都会走索引，但是如果一个字段升序排序，另一个字段降序排序，则不会走索引，explain的extra信息显示的是<code>Using index, Using filesort</code>，如果要优化掉Using filesort，则需要另外再创建一个索引，如：<code>create index idx_user_age_phone_ad on tb_user(age asc, phone desc);</code>，此时使用<code>select id, age, phone from tb_user order by age asc, phone desc;</code>会全部走索引</p>
<p>总结：</p>
<ul>
<li>根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则</li>
<li>尽量使用覆盖索引</li>
<li>多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）</li>
<li>如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）</li>
</ul>
<h3 id="group-by优化">group by优化</h3>
<ul>
<li>在分组操作时，可以通过索引来提高效率</li>
<li>分组操作时，索引的使用也是满足最左前缀法则的</li>
</ul>
<p>如索引为<code>idx_user_pro_age_stat</code>，则句式可以是<code>select ... where profession order by age</code>，这样也符合最左前缀法则</p>
<h3 id="limit优化">limit优化</h3>
<p>常见的问题如<code>limit 2000000, 10</code>，此时需要 MySQL 排序前2000000条记录，但仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。<br>优化方案：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化</p>
<p>例如：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">-- 此语句耗时很长<br><span class="line">select * from tb_sku limit 9000000, 10;<br><span class="line">-- 通过覆盖索引加快速度，直接通过主键索引进行排序及查询<br><span class="line">select id from tb_sku order by id limit 9000000, 10;<br><span class="line">-- 下面的语句是错误的，因为 MySQL 不支持 in 里面使用 limit<br><span class="line">-- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);<br><span class="line">-- 通过连表查询即可实现第一句的效果，并且能达到第二句的速度<br><span class="line">select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;<br><span class="line"><br></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="count优化">count优化</h3>
<p>MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高（前提是不适用where）；<br>InnoDB 在执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累计计数。<br>优化方案：自己计数，如创建key-value表存储在内存或硬盘，或者是用redis</p>
<p>count的几种用法：</p>
<ul>
<li>如果count函数的参数（count里面写的那个字段）不是NULL（字段值不为NULL），累计值就加一，最后返回累计值</li>
<li>用法：count(*)、count(主键)、count(字段)、count(1)</li>
<li>count(主键)跟count(*)一样，因为主键不能为空；count(字段)只计算字段值不为NULL的行；count(1)引擎会为每行添加一个1，然后就count这个1，返回结果也跟count(*)一样；count(null)返回0</li>
</ul>
<p>各种用法的性能：</p>
<ul>
<li>count(主键)：InnoDB引擎会遍历整张表，把每行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为空）</li>
<li>count(字段)：没有not
null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not
null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加</li>
<li>count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加</li>
<li>count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加</li>
</ul>
<p>按效率排序：count(字段) < count(主键) < count(1) < count(*)，所以尽量使用 count(*)</p>
<h3 id="update优化（避免行锁升级为表锁）">update优化（避免行锁升级为表锁）</h3>
<p>InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。</p>
<p>如以下两条语句：<br><code>update student set no = '123' where id = 1;</code>，这句由于id有主键索引，所以只会锁这一行；<br><code>update student set no = '123' where name = 'test';</code>，这句由于name没有索引，所以会把整张表都锁住进行数据更新，解决方法是给name字段添加索引</p>
<h2 id="视图-存储过程-触发器">视图/存储过程/触发器</h2>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E8%A7%86%E5%9B%BE%E7%AD%89%E5%86%85%E5%AE%B9%E7%9A%84%E6%80%BB%E7%BB%93.png"></p>
<h3 id="视图">视图</h3>
<p>视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。</p>
<p>通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。</p>
<h4 id="语法-1">语法</h4>
<p>创建视图：<br><code>CREATE VIEW 视图名称(列名列表)】AS SELECT语句 CHECK OPTION]</code></p>
<p>查询视图：<br>查看创建视图语句：<code>SHOW CRETE VIEW 视图名称;</code><br><span style="color: rgba(38, 38, 38, 1)">查看视图数据：</span><code>查看视图数据:SELECT*FROM 视图名称…;</code></p>
<p><span style="color: rgba(38, 38, 38, 1)">修改视图：<br></span><span style="color: rgba(38, 38, 38, 1)">方式一：<br></span><code>CREATE VIEW 视图名称(列名列表)AS SELECT语句 CHECK OPTION</code><span style="color: rgba(38, 38, 38, 1)"><br></span><span style="color: rgba(38, 38, 38, 1)">方式二：<br></span><code>ALTER VEW 视图名称(列名列表)AS SELECT语句CHECK OPTION]</code></p>
<p><span style="color: rgba(38, 38, 38, 1)">删除视图：<br></span><code>DROP VIEW 视图名称[,视图名称]</code></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- 创建视图<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">or replace <span class="hljs-keyword">view stu_v_1 <span class="hljs-keyword">as <span class="hljs-keyword">select id, name <span class="hljs-keyword">from student <span class="hljs-keyword">where id <span class="hljs-operator"><= <span class="hljs-number">10;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 查询视图<br><span class="line"><span class="hljs-keyword">show <span class="hljs-keyword">create <span class="hljs-keyword">view stu_v_1;<br><span class="line"><span class="hljs-keyword">select <span class="hljs-operator">* <span class="hljs-keyword">from stu_v_1;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 修改视图<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">or replace <span class="hljs-keyword">view stu_v_1 <span class="hljs-keyword">as <span class="hljs-keyword">select id, name, <span class="hljs-keyword">no <span class="hljs-keyword">from student <span class="hljs-keyword">where id <span class="hljs-operator"><= <span class="hljs-number">10;<br><span class="line"><span class="hljs-keyword">alter <span class="hljs-keyword">view stu_v_1 <span class="hljs-keyword">as <span class="hljs-keyword">select id, name <span class="hljs-keyword">from student <span class="hljs-keyword">where id <span class="hljs-operator"><= <span class="hljs-number">10;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 删除视图<br><span class="line"><span class="hljs-keyword">drop <span class="hljs-keyword">view if <span class="hljs-keyword">exists stu_v_1;<span class="hljs-comment">/**/<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="检查选项">检查选项</h4>
<p><strong>视图的检查选项：</strong></p>
<p>当使用WITH CHECK OPTION子句创建视图时，MySOL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。MVSOL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。</p>
<p>为了确定检查的范围，mysql 提供了两个选项:CASCADED 和 LOCAL，默认值为CASCADED。</p>
<p><strong>cascaded：</strong>在对创建时含有该字段的视图，插入数据时，该视图依赖的视图都会加上检查，需要<strong>所有条件</strong>都满足才能够插入成功。</p>
<p><strong>local：</strong>在对创建时含有该字段的视图，插入数据时，对于该视图依赖的视图中<strong>含有检查语句的条件</strong>进行检查判断。</p>
<h4 id="更新及作用">更新及作用</h4>
<p><strong>视图的更新：</strong></p>
<p>要使视图可更新，视图中的行与基础表中的行之间<strong>必须存在一对一的关系</strong>。</p>
<p>如果视图包含以下任何一项，则该<strong>视图不可更新</strong>：</p>
<ol>
<li>聚合函数或窗口函数(SUM()、MIN()、MAX()、COUNT()等</li>
<li>DISTINCT</li>
<li>GROUP BY</li>
<li>HAVINGA</li>
<li>UNION 或者 UNION ALL</li>
</ol>
<p><strong>作用：</strong></p>
<ul>
<li><strong>简单</strong><br>视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。</li>
<li><strong>安全</strong><br>数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。</li>
<li><strong>数据独立</strong><br>视图可帮助用户屏蔽真实表结构变化带来的影响。</li>
</ul>
<h4 id="案例">案例</h4>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- 1.为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">view tb <span class="hljs-keyword">user <span class="hljs-keyword">view <span class="hljs-keyword">as <span class="hljs-keyword">select id,name,profession, age,gender,status,createtime <span class="hljs-keyword">from tb_user;<br><span class="line"><span class="hljs-keyword">select <span class="hljs-operator">*<span class="hljs-keyword">from tb <span class="hljs-keyword">user <span class="hljs-keyword">view;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 2.查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">view tb_stu_course_view <br><span class="line"><span class="hljs-keyword">select s.name student_name, s.no student_no, c.name course_name <br><span class="line"><span class="hljs-keyword">from student s, stuent_course sc, course c <br><span class="line"><span class="hljs-keyword">where s.id <span class="hljs-operator">= sc.studentid <span class="hljs-keyword">and sc.courseid <span class="hljs-operator">= c.id;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 以后每次只需要进行查询视图即可<br><span class="line"><span class="hljs-keyword">select <span class="hljs-operator">* <span class="hljs-keyword">from tb_stu_course_view;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="存储过程">存储过程</h3>
<p>存储过程其实就类似 java，c 这种语言，这一部分可以通过文档快速学习，不懂的再回过头看视频。</p>
<p>存储过程是事先经过编译并存储在数据库中的一段 SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。</p>
<p>存储过程思想上很简单，就是数据库 SOL语言层面的代码封装与重用。</p>
<p><strong>特点：</strong></p>
<ul>
<li>封装，复用</li>
<li>可以接收参数，也可以返回数据</li>
<li>减少网络交互，效率提升</li>
</ul>
<h4 id="基本语法">基本语法</h4>
<p><code>查看视图数据:SELECT*FROM 视图名称…;</code></p>
<p><strong><span style="color: rgba(38, 38, 38, 1)">查看：</span></strong></p>
<p><code>SELECT* FROM INFORMATION SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='xx';--查询数据库的存储过程及状态信息</code><span style="color: rgba(38, 38, 38, 1)"><br></span><code>SHOW CREATE PROCEDURE 存储过程名称;--查询某个存储过程的定义</code></p>
<p><strong><span style="color: rgba(38, 38, 38, 1)">删除：</span></strong></p>
<p><code>DROP PROCEDURE 存储过程名称;</code></p>
<p><span style="color: rgba(38, 38, 38, 1)">案例：</span></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- 存储过程基本语法<br><span class="line"><span class="hljs-comment">-- 创建<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p1()<br><span class="line"><span class="hljs-keyword">begin<br><span class="line"><span class="hljs-keyword">select <span class="hljs-built_in">count(<span class="hljs-operator">*)<span class="hljs-keyword">from student;<br><span class="line"><span class="hljs-keyword">end;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 调用<br><span class="line"><span class="hljs-keyword">call p1();<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 查看<br><span class="line"><span class="hljs-keyword">select <span class="hljs-operator">* <span class="hljs-keyword">from information_schema.ROUTINES <span class="hljs-keyword">where ROUTINE_SCHEMA <span class="hljs-operator">= <span class="hljs-string">'itcast';<br><span class="line"><span class="hljs-keyword">show <span class="hljs-keyword">create <span class="hljs-keyword">procedure p1;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 删除<br><span class="line"><span class="hljs-keyword">drop <span class="hljs-keyword">procedure if <span class="hljs-keyword">exists p1;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="变量">变量</h4>
<h5 id="系统变量">系统变量</h5>
<p>系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。</p>
<p>查看系统变量</p>
<p><code>SHOW VARIABLES ; --查看所有系统变量</code><span style="color: rgba(38, 38, 38, 1)"><br></span><code>SHOW VARIABLES LIKE'; --可以通过LKE模糊匹配方式查找变量</code><span style="color: rgba(38, 38, 38, 1)"><br></span><code>SELECT @@系统变量名; -- 查看指定变量的值</code></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- 变量：系统变量<br><span class="line"><span class="hljs-comment">-- 查看系统变量<br><span class="line"><span class="hljs-keyword">show session variables;<br><span class="line"><span class="hljs-keyword">show session variables <span class="hljs-keyword">like <span class="hljs-string">'auto%';<br><span class="line"><span class="hljs-keyword">show glabal variables <span class="hljs-keyword">like <span class="hljs-string">'auto%';<br><span class="line"><span class="hljs-keyword">select @<span class="hljs-variable">@global.autocommit;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 设置系统变量<br><span class="line"><span class="hljs-keyword">set session autocommit <span class="hljs-operator">= <span class="hljs-number">1;<br><span class="line"><span class="hljs-keyword">insert intto course(id, name) <span class="hljs-keyword">values (<span class="hljs-number">6, <span class="hljs-string">'ES');<br><span class="line"><span class="hljs-keyword">set <span class="hljs-keyword">global auto <span class="hljs-keyword">commit <span class="hljs-operator">= <span class="hljs-number">0;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>注意：</strong></p>
<ul>
<li>如果没有指定 session / global，默认 session，会话变量</li>
<li>myesql 服务器重启之后，所设置的全局参数会失效，要想不失效，需要更改/etc/my.cnf 中的配置。</li>
</ul>
<h5 id="用户定义变量">用户定义变量</h5>
<p>用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。</p>
<p><strong>赋值：</strong></p>
<p><code>SET @var name = expr [, @var_name = expr]...;</code><span style="color: rgba(38, 38, 38, 1)"><br></span><code>SET @var name := expr [, @var_name := expr]...;</code></p>
<p><code>SELECT @var name := expr , @var name := expr ...;</code><br><code>SELECT 字段名 INTO @var_name FROM 表名;</code></p>
<p><strong><span style="color: rgba(38, 38, 38, 1)">使用：</span></strong></p>
<p><code>SELECT @var_name;</code></p>
<p><span style="color: rgba(38, 38, 38, 1)">案例：</span></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- 变量：用户变量<br><span class="line"><span class="hljs-comment">-- 赋值<br><span class="line"><span class="hljs-keyword">set <span class="hljs-variable">@myname <span class="hljs-operator">= <span class="hljs-string">'itcast';<br><span class="line"><span class="hljs-keyword">set <span class="hljs-variable">@myage :<span class="hljs-operator">= <span class="hljs-number">10;<br><span class="line"><br><span class="line"><span class="hljs-keyword">select <span class="hljs-variable">@mycolor :<span class="hljs-operator">= <span class="hljs-string">'red';<br><span class="line"><span class="hljs-keyword">select <span class="hljs-built_in">count(<span class="hljs-operator">*) <span class="hljs-keyword">into <span class="hljs-variable">@mycount <span class="hljs-keyword">from tb_user;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 使用<br><span class="line"><span class="hljs-keyword">select <span class="hljs-variable">@myname, <span class="hljs-variable">@myage, <span class="hljs-variable">@mycolor, <span class="hljs-variable">@mycount;<br><span class="line"><br><span class="line"><span class="hljs-keyword">select <span class="hljs-variable">@abc; <span class="hljs-comment">-- 输出为NULL<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>注意：</strong></p>
<p>用户定义的变量无需对其进行声明或者初始化，只不过获取到的值为 NULL。</p>
<h5 id="局部变量">局部变量</h5>
<p>局部变量 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN .. END块。</p>
<p><strong>声明：</strong></p>
<p><code>DECLARE 变量名 变量类型 ;</code></p>
<p><span style="color: rgba(38, 38, 38, 1)">变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。</span></p>
<p><strong><span style="color: rgba(38, 38, 38, 1)">赋值：</span></strong></p>
<p><code>SET 变量名=值;</code></p>
<p><code>SET 变量名:=值;</code></p>
<p><code>SELECT 字段名 INTO 变量名 FROM 表名 ...;</code></p>
<p><span style="color: rgba(38, 38, 38, 1)">案例：</span></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- 变量：局部变量<br><span class="line"><span class="hljs-comment">-- 声明 - declare<br><span class="line"><span class="hljs-comment">-- 赋值 -<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p2()<br><span class="line"><span class="hljs-keyword">begin<br><span class="line"><span class="hljs-keyword">declare stu_count <span class="hljs-type">int <span class="hljs-keyword">default <span class="hljs-number">0;<br><span class="line"><span class="hljs-keyword">select <span class="hljs-built_in">count(<span class="hljs-operator">*) <span class="hljs-keyword">into stu_count <span class="hljs-keyword">from student;<br><span class="line"><span class="hljs-keyword">select stu_count;<br><span class="line"><span class="hljs-keyword">end;<br><span class="line"><br><span class="line"><span class="hljs-keyword">call p2();<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="if-判断">if 判断</h4>
<p>语法：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">IF 条件<span class="hljs-number">1 <span class="hljs-keyword">THEN<br><span class="line"> ...<br><span class="line">ELSEIF 条件<span class="hljs-number">2 <span class="hljs-keyword">THEN <span class="hljs-comment">-- 可选<br><span class="line"> ...<br><span class="line"><span class="hljs-keyword">ELSE <span class="hljs-comment">-- 可选<br><span class="line"> ...<br><span class="line"><span class="hljs-keyword">END IF;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p>案例：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p3()<br><span class="line"><span class="hljs-keyword">begin<br><span class="line"><span class="hljs-keyword">declare score <span class="hljs-type">int <span class="hljs-keyword">default <span class="hljs-number">58;<br><span class="line"><span class="hljs-keyword">declare <span class="hljs-keyword">result <span class="hljs-type">varchar(<span class="hljs-number">10);<br><span class="line">if score <span class="hljs-operator">>= <span class="hljs-number">85 <span class="hljs-keyword">then<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">=<span class="hljs-string">'优秀';<br><span class="line">elseif score <span class="hljs-operator">>= <span class="hljs-number">60 <span class="hljs-keyword">then<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">=<span class="hljs-string">'及格';<br><span class="line"><span class="hljs-keyword">else<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">=<span class="hljs-string">'不及格';<br><span class="line"><span class="hljs-keyword">end if;<br><span class="line"><span class="hljs-keyword">select <span class="hljs-keyword">result;<br><span class="line"><span class="hljs-keyword">end;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="参数（in-out-inout">参数（in, out, inout)</h4>
<div class="table-container">
<table>
<thead>
<tr><th align="center">类型</th><th align="center">含义</th><th align="center">备注</th></tr>
</thead>
<tbody>
<tr>
<td align="center">IN</td>
<td align="center">该类参数作为输入，也就是需要调用时传入值</td>
<td align="center">默认</td>
</tr>
<tr>
<td align="center">OUT</td>
<td align="center">该类参数作为输出，也就是该参数可以作为返回值</td>
<td align="center"> </td>
</tr>
<tr>
<td align="center">INOUT</td>
<td align="center">既可以作为输入参数，也可以作为输出参数****</td>
<td align="center"> </td>
</tr>
</tbody>
</table>
</div>
<p><strong>用法：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">CREATE <span class="hljs-keyword">PROCEDURE 存储过程名称([<span class="hljs-keyword">IN<span class="hljs-operator">/<span class="hljs-keyword">OUT<span class="hljs-operator">/<span class="hljs-keyword">INOUT 参数名 参数类型 ])<br><span class="line"><span class="hljs-keyword">BEGIN<br><span class="line"> <span class="hljs-comment">-- SQL语句<br><span class="line"><span class="hljs-keyword">END :<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>案例：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)<br><span class="line"><span class="hljs-comment">-- score >= 85分，等级为优秀。<br><span class="line"><span class="hljs-comment">-- score >= 60分 且 score < 85分，等级为及格<br><span class="line"><span class="hljs-comment">-- score < 60分，等级为不及格。<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p3(<span class="hljs-keyword">in score <span class="hljs-type">int, <span class="hljs-keyword">out <span class="hljs-keyword">result <span class="hljs-type">varchar(<span class="hljs-number">10))<br><span class="line"><span class="hljs-keyword">begin<br><span class="line">if score <span class="hljs-operator">>= <span class="hljs-number">85 <span class="hljs-keyword">then<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">=<span class="hljs-string">'优秀';<br><span class="line">elseif score <span class="hljs-operator">>= <span class="hljs-number">60 <span class="hljs-keyword">then<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">=<span class="hljs-string">'及格';<br><span class="line"><span class="hljs-keyword">else<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">=<span class="hljs-string">'不及格';<br><span class="line"><span class="hljs-keyword">end if;<br><span class="line"><span class="hljs-keyword">select <span class="hljs-keyword">result;<br><span class="line"><span class="hljs-keyword">end;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 将传入的200分制的分数，进行换算，换算成百分制，然后返回分数 --> inout<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p5(<span class="hljs-keyword">inout score <span class="hljs-keyword">double)<br><span class="line"><span class="hljs-keyword">begin<br><span class="line"><span class="hljs-keyword">set score :<span class="hljs-operator">= score <span class="hljs-operator">* <span class="hljs-number">0.5;<br><span class="line"><span class="hljs-keyword">end;<br><span class="line"><br><span class="line"><span class="hljs-keyword">set <span class="hljs-variable">@score <span class="hljs-operator">= <span class="hljs-number">198;<br><span class="line"><span class="hljs-keyword">call p5(score);<br><span class="line"><span class="hljs-keyword">select <span class="hljs-variable">@score;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="case">case</h4>
<p><strong>语法一：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">CASE <span class="hljs-keyword">case <span class="hljs-keyword">value<br><span class="line"><span class="hljs-keyword">WHEN when_value1 <span class="hljs-keyword">THEN statement_list1<br><span class="line">[<span class="hljs-keyword">WHEN when_value2 <span class="hljs-keyword">THEN statement_list2]...<br><span class="line">[<span class="hljs-keyword">ELSE statement_list ]<br><span class="line"><span class="hljs-keyword">END <span class="hljs-keyword">CASE;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>语法二：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">CASE<br><span class="line"><span class="hljs-keyword">WHEN search_conditionl <span class="hljs-keyword">THEN statement_list1<br><span class="line"><span class="hljs-keyword">WHEN search_condition2 <span class="hljs-keyword">THEN statement_list2]...<br><span class="line">[<span class="hljs-keyword">ELSE statement_list]<br><span class="line"><span class="hljs-keyword">END <span class="hljs-keyword">CASE;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>案例：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- case<br><span class="line"><span class="hljs-comment">-- 根据传入的月份，判定月份所属的季节(要求采用case结构)<br><span class="line"><span class="hljs-comment">-- 1-3月份，为第一季度<br><span class="line"><span class="hljs-comment">-- 4-6月份，为第二季度<br><span class="line"><span class="hljs-comment">-- 7-9月份，为第三季度<br><span class="line"><span class="hljs-comment">-- 10-12月份，为第四季度<br><span class="line"><br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p6(<span class="hljs-keyword">in <span class="hljs-keyword">month <span class="hljs-type">int)<br><span class="line"><span class="hljs-keyword">begin <br><span class="line"><span class="hljs-keyword">declare <span class="hljs-keyword">result <span class="hljs-type">varchar(<span class="hljs-number">10);<br><span class="line"><span class="hljs-keyword">case <br><span class="line"> <span class="hljs-keyword">when <span class="hljs-keyword">month <span class="hljs-operator">>= <span class="hljs-number">1 <span class="hljs-keyword">and <span class="hljs-keyword">month <span class="hljs-operator"><= <span class="hljs-number">3 <span class="hljs-keyword">then<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">= <span class="hljs-string">'第一季度';<br><span class="line"> <span class="hljs-keyword">when <span class="hljs-keyword">month <span class="hljs-operator">>= <span class="hljs-number">4 <span class="hljs-keyword">and <span class="hljs-keyword">month <span class="hljs-operator"><= <span class="hljs-number">6 <span class="hljs-keyword">then<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">= <span class="hljs-string">'第二季度';<br><span class="line"> <span class="hljs-keyword">when <span class="hljs-keyword">month <span class="hljs-operator">>= <span class="hljs-number">7 <span class="hljs-keyword">and <span class="hljs-keyword">month <span class="hljs-operator"><= <span class="hljs-number">9 <span class="hljs-keyword">then<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">= <span class="hljs-string">' 第三季度';<br><span class="line"> <span class="hljs-keyword">when <span class="hljs-keyword">month <span class="hljs-operator">>= <span class="hljs-number">10 <span class="hljs-keyword">and <span class="hljs-keyword">month <span class="hljs-operator"><= <span class="hljs-number">12 <span class="hljs-keyword">then<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">= <span class="hljs-string">'第四季度';<br><span class="line"> <span class="hljs-keyword">else<br><span class="line"> <span class="hljs-keyword">set <span class="hljs-keyword">result :<span class="hljs-operator">= <span class="hljs-string">'非法参数';<br><span class="line"><span class="hljs-keyword">end <span class="hljs-keyword">case;<br><span class="line"><br><span class="line"><span class="hljs-keyword">select concat(<span class="hljs-string">'你输入的月份为：', <span class="hljs-keyword">month, <span class="hljs-string">'，所属季度为：', <span class="hljs-keyword">result);<br><span class="line"><span class="hljs-keyword">end;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="循环">循环</h4>
<h5 id="while">while</h5>
<p>while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。</p>
<p><strong>语法：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">#先判定条件，如果条件为<span class="hljs-literal">true，则执行逻辑，否则，不执行逻辑<br><span class="line">WHILE 条件 DO<br><span class="line">SOL逻辑...<br><span class="line"><span class="hljs-keyword">END WHILE;<br></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>案例：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- while计算从1累加到 n 的值，n 为传入的参数值。<br><span class="line"><span class="hljs-comment">-- A.定义局部变量，记录累加之后的值;<br><span class="line"><span class="hljs-comment">-- B.每循环一次，就会对 n 进行减1，如果 n 减到0，则退出循环<br><span class="line"><br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p7(<span class="hljs-keyword">in n <span class="hljs-type">int)<br><span class="line"><span class="hljs-keyword">begin<br><span class="line"><span class="hljs-keyword">declare total <span class="hljs-type">int <span class="hljs-keyword">default <span class="hljs-number">0;<br><span class="line"><br><span class="line">while n<span class="hljs-operator">><span class="hljs-number">0 do<br><span class="line"> <span class="hljs-keyword">set total :<span class="hljs-operator">= total <span class="hljs-operator">+ n<br><span class="line"> <span class="hljs-keyword">set n:<span class="hljs-operator">=n<span class="hljs-number">-1;<br><span class="line"><span class="hljs-keyword">end while;<br><span class="line"><br><span class="line"><span class="hljs-keyword">select total;<br><span class="line"><span class="hljs-keyword">end;<br><span class="line"><span class="hljs-keyword">call p7( n: <span class="hljs-number">100);<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h5 id="repeat">repeat</h5>
<p>repeat是有条件的循环控制语句,当满足条件的时候退出循环。</p>
<p>与 while 区别：</p>
<ol>
<li>先进行循环一次再判断。相当于 c 语言中的 do while();</li>
<li>满足条件则退出</li>
</ol>
<p><strong>语法：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环<br><span class="line">REPEAT<br><span class="line">SOL逻辑.<br><span class="line">UNTIL 条件<br><span class="line"><span class="hljs-keyword">END REPEAT;<br></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>案例：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- while计算从1累加到 n 的值，n 为传入的参数值。<br><span class="line"><span class="hljs-comment">-- A.定义局部变量，记录累加之后的值;<br><span class="line"><span class="hljs-comment">-- B.每循环一次，就会对 n 进行减1，如果 n 减到0，则退出循环<br><span class="line"><br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p8(innint)<br><span class="line"><span class="hljs-keyword">begin<br><span class="line"><span class="hljs-keyword">declare total <span class="hljs-type">int <span class="hljs-keyword">default <span class="hljs-number">0;<br><span class="line"><br><span class="line">repeat<br><span class="line"> <span class="hljs-keyword">set total :<span class="hljs-operator">= total <span class="hljs-operator">+ n;<br><span class="line"> <span class="hljs-keyword">set n :<span class="hljs-operator">= n <span class="hljs-operator">- <span class="hljs-number">1;<br><span class="line">until n <span class="hljs-operator"><= <span class="hljs-number">0<br><span class="line"><span class="hljs-keyword">end repeat;<br><span class="line"><br><span class="line"><span class="hljs-keyword">select total;<br><span class="line"><span class="hljs-keyword">end;<br><span class="line"><br><span class="line"><span class="hljs-keyword">call p8( n: <span class="hljs-number">10);<br><span class="line"><span class="hljs-keyword">call p8( n: <span class="hljs-number">100);<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h5 id="loop">loop</h5>
<p>LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用。</p>
<ol>
<li>LEAVE:配合循环使用，退出循环。</li>
<li>ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。</li>
</ol>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">[<span class="hljs-keyword">begin label:] LOOP<br><span class="line"><span class="hljs-keyword">SQL逻辑..<br><span class="line"><span class="hljs-keyword">END LOOP [<span class="hljs-keyword">end label];<br><span class="line"><br><span class="line">LEAVE label;<span class="hljs-comment">-- 退出指定标记的循环体<br><span class="line">ITERATE label;<span class="hljs-comment">-- 直接进入下一次循环<br></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>案例：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- loop 计算从1到n之间的偶数累加的值，n为传入的参数值。<br><span class="line"><span class="hljs-comment">-- A.定义局部变量，记录累加之后的值;<br><span class="line"><span class="hljs-comment">-- B.每循环一次，就会劝进行-1，如果n减到0，则退出循环。------> leave xx<br><span class="line"><span class="hljs-comment">-- C.如果当次累加的数据是奇数，则直接进入下一次循坏。-------> iterate xx<br><span class="line"><br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p10(<span class="hljs-keyword">in n <span class="hljs-type">int)<br><span class="line"><span class="hljs-keyword">begin <br><span class="line"><span class="hljs-keyword">declare total <span class="hljs-type">int defatult <span class="hljs-number">0;<br><span class="line"><br><span class="line">sum: loop<br><span class="line"> if n <span class="hljs-operator"><= <span class="hljs-number">10 <span class="hljs-keyword">then<br><span class="line"> leave sum;<br><span class="line"> <span class="hljs-keyword">end if;<br><span class="line"><br><span class="line"> if n <span class="hljs-operator">%<span class="hljs-number">2 <span class="hljs-operator">= <span class="hljs-number">1 <span class="hljs-keyword">then<br><span class="line"> <span class="hljs-keyword">set n :<span class="hljs-operator">= n <span class="hljs-operator">- <span class="hljs-number">1;<br><span class="line"> iterate sum;<br><span class="line"> <span class="hljs-keyword">end if;<br><span class="line"><br><span class="line"> <span class="hljs-keyword">set total :<span class="hljs-operator">= total <span class="hljs-operator">+ n;<br><span class="line"> <span class="hljs-keyword">set n :<span class="hljs-operator">= n <span class="hljs-operator">- <span class="hljs-number">1;<br><span class="line"><span class="hljs-keyword">end loop sum;<br><span class="line"><br><span class="line"><span class="hljs-keyword">select total;<br><span class="line"><span class="hljs-keyword">end;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="游标-cursor">游标-cursor</h4>
<p>游标(CURSOR)是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和 CLOSE，其语法分别如下。</p>
<p>通俗点讲：类似于 c 语言中的结构体，java 中的实体类。</p>
<p><strong>声明游标</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">DECLARE 游标名称 <span class="hljs-keyword">CURSOR <span class="hljs-keyword">FOR 查询语句;<br></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>打开游标：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">OPEN 游标名称;<br></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>获取游标记录：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">FETCH 游标名称 <span class="hljs-keyword">INTO 变量[,变量];<br></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>关闭游标：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">CLOSE 游标名称;<br></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>案例：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- 游标<br><span class="line"><span class="hljs-comment">-- 根据传入的参数uage，来查询用户表tb_user 中， 所有的用户年龄小于uage的用户姓名（name)和专业（profession），<br><span class="line"><span class="hljs-comment">-- 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。<br><span class="line"><span class="hljs-comment">-- 逻辑:<br><span class="line"><span class="hljs-comment">-- A.声明游标，存储查询结果集-<br><span class="line"><span class="hljs-comment">-- B.准备:创建表结构<br><span class="line"><span class="hljs-comment">-- C.开启游标-<br><span class="line"><span class="hljs-comment">-- D.获取游标中的记录<br><span class="line"><span class="hljs-comment">-- E.插入数据到新表中-<br><span class="line"><span class="hljs-comment">-- F.关闭游标<br><span class="line"><br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p11(<span class="hljs-keyword">in uage <span class="hljs-type">int)<br><span class="line"><span class="hljs-keyword">begin <br><span class="line"><span class="hljs-keyword">declare uname <span class="hljs-type">varchar(<span class="hljs-number">100);<br><span class="line"><span class="hljs-keyword">declare upro <span class="hljs-type">varchar(<span class="hljs-number">100);<br><span class="line"><span class="hljs-keyword">declare u_cursor <span class="hljs-keyword">cursor <span class="hljs-keyword">for <span class="hljs-keyword">select name, profession <span class="hljs-keyword">from tb_user <span class="hljs-keyword">where age <span class="hljs-operator"><= uage;<br><span class="line"><br><span class="line"><span class="hljs-keyword">drop <span class="hljs-keyword">table if <span class="hljs-keyword">exists tb_user_pro;<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">table if <span class="hljs-keyword">not <span class="hljs-keyword">exists tb_user_pro(<br><span class="line"> id <span class="hljs-type">int <span class="hljs-keyword">primary key auto_increment,<br><span class="line"> name <span class="hljs-type">varchar(<span class="hljs-number">100),<br><span class="line"> profession <span class="hljs-type">varchar(<span class="hljs-number">100)<br><span class="line">);<br><span class="line"><br><span class="line"><span class="hljs-keyword">open u_cursor;<br><span class="line">while <span class="hljs-literal">true do<br><span class="line"> <span class="hljs-keyword">fetch u_cursor <span class="hljs-keyword">into uname,upro;<br><span class="line"> <span class="hljs-keyword">insert <span class="hljs-keyword">into tb_user_pro <span class="hljs-keyword">values(<span class="hljs-keyword">null, uname, upro);<br><span class="line"><span class="hljs-keyword">end while;<br><span class="line"><span class="hljs-keyword">close u_cursor;<br><span class="line"><span class="hljs-keyword">end;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h5 id="条件处理程序-handler">条件处理程序-handler</h5>
<p>条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。</p>
<p><strong>语法：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">DECLARE handler action HANDLERFOR <span class="hljs-keyword">condition <span class="hljs-keyword">value l, <span class="hljs-keyword">condition value.... statement;<br><span class="line">handler action<br><span class="line">CONTINUE: 继续执行当前程序<br><span class="line">EXIT: 终止执行当前程序<br><span class="line"><span class="hljs-keyword">condition <span class="hljs-keyword">value<br><span class="line">SOLSTATE sqlstate_value:状态码，如 <span class="hljs-number">02000<br><span class="line"><span class="hljs-keyword">SQLWARNING:所有以<span class="hljs-number">01开头的<span class="hljs-keyword">SQLSTATE代码的简写<br><span class="line"><span class="hljs-keyword">NOT FOUND:所有以<span class="hljs-number">02开头的SOLSTATE代码的简写<br><span class="line">SOLEXCEPTION:所有没有被SOLWARNING 或 <span class="hljs-keyword">NOT FOUND捕获的SOLSTATE代码的简写<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>案例：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">procedure p11(<span class="hljs-keyword">in uage <span class="hljs-type">int)<br><span class="line"><span class="hljs-keyword">begin <br><span class="line"><span class="hljs-keyword">declare uname <span class="hljs-type">varchar(<span class="hljs-number">100);<br><span class="line"><span class="hljs-keyword">declare upro <span class="hljs-type">varchar(<span class="hljs-number">100);<br><span class="line"><span class="hljs-keyword">declare u_cursor <span class="hljs-keyword">cursor <span class="hljs-keyword">for <span class="hljs-keyword">select name, profession <span class="hljs-keyword">from tb_user <span class="hljs-keyword">where age <span class="hljs-operator"><= uage;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 监控到02000的状态码后，关闭游标后执行exit退出操作。<br><span class="line"><span class="hljs-keyword">declare exit handler <span class="hljs-keyword">for <span class="hljs-keyword">not found <span class="hljs-keyword">close u_cursor; <br><span class="line"><br><span class="line"><span class="hljs-keyword">drop <span class="hljs-keyword">table if <span class="hljs-keyword">exists tb_user_pro;<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">table if <span class="hljs-keyword">not <span class="hljs-keyword">exists tb_user_pro(<br><span class="line"> id <span class="hljs-type">int <span class="hljs-keyword">primary key auto_increment,<br><span class="line"> name <span class="hljs-type">varchar(<span class="hljs-number">100),<br><span class="line"> profession <span class="hljs-type">varchar(<span class="hljs-number">100)<br><span class="line">);<br><span class="line"><br><span class="line"><span class="hljs-keyword">open u_cursor;<br><span class="line">while <span class="hljs-literal">true do<br><span class="line"> <span class="hljs-keyword">fetch u_cursor <span class="hljs-keyword">into uname,upro;<br><span class="line"> <span class="hljs-keyword">insert <span class="hljs-keyword">into tb_user_pro <span class="hljs-keyword">values(<span class="hljs-keyword">null, uname, upro);<br><span class="line"><span class="hljs-keyword">end while;<br><span class="line"><span class="hljs-keyword">close u_cursor;<br><span class="line"><span class="hljs-keyword">end;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="存储函数：">存储函数：</h3>
<p>存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。</p>
<p>存储函数用的较少，能够使用存储函数的地方都可以用存储过程替换。</p>
<p><strong>语法：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">CREATE <span class="hljs-keyword">FUNCTION 存储函数名称([ 参数列表 ])<br><span class="line"><span class="hljs-keyword">RETURNS type <br><span class="line"><span class="hljs-keyword">BEGIN<br><span class="line"><span class="hljs-comment">-- SQL语句<br><span class="line"><span class="hljs-keyword">RETURN ...;<br><span class="line"><span class="hljs-keyword">END ;<br><span class="line">characteristic说明:<br><span class="line">· <span class="hljs-keyword">DETERMINISTIC:相同的输入参数总是产生相同的结果<br><span class="line">· <span class="hljs-keyword">NO <span class="hljs-keyword">SQL:不包含 <span class="hljs-keyword">SQL语句。<br><span class="line">· <span class="hljs-keyword">READS SOL DATA:包含读取数据的语句，但不包含写入数据的语句,<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>案例：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">function fun1(n <span class="hljs-type">int)<br><span class="line"><span class="hljs-keyword">returns <span class="hljs-type">int <span class="hljs-keyword">deterministic<br><span class="line"><span class="hljs-keyword">begin<br><span class="line"><span class="hljs-keyword">declare total <span class="hljs-type">int <span class="hljs-keyword">default <span class="hljs-number">0;<br><span class="line"><br><span class="line">while n <span class="hljs-operator">> <span class="hljs-number">0 do <br><span class="line"> <span class="hljs-keyword">set total :<span class="hljs-operator">= total <span class="hljs-operator">+ n;<br><span class="line"> <span class="hljs-keyword">set n :<span class="hljs-operator">= n <span class="hljs-operator">- <span class="hljs-number">1;<br><span class="line"><span class="hljs-keyword">end while;<br><span class="line"><br><span class="line"><span class="hljs-keyword">return total;<br><span class="line"><span class="hljs-keyword">end;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="触发器">触发器</h3>
<p>触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。</p>
<p>使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。</p>
<div class="table-container">
<table>
<thead>
<tr><th align="center">触发器类型</th><th>NEW 和 OLD</th></tr>
</thead>
<tbody>
<tr>
<td align="center">insert 型触发器</td>
<td>NEW 表示将要或者已经新增的数据</td>
</tr>
<tr>
<td align="center">update 型触发器</td>
<td>OLD 表示修改之前的数据，NEW 表示将要或已经修改后的数据</td>
</tr>
<tr>
<td align="center">delete 型触发器</td>
<td>OLD 表示将要或者已经删除的数据</td>
</tr>
</tbody>
</table>
</div>
<p><strong>语法：</strong></p>
<p><strong>创建：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">CREATE <span class="hljs-keyword">TRIGGER <span class="hljs-keyword">trigger name<br><span class="line">BEFORE<span class="hljs-operator">/AFTER <span class="hljs-keyword">INSERT<span class="hljs-operator">/<span class="hljs-keyword">UPDATE<span class="hljs-operator">/<span class="hljs-keyword">DELETE<br><span class="line"><span class="hljs-keyword">ON tbl name <span class="hljs-keyword">FOR <span class="hljs-keyword">EACH <span class="hljs-type">ROW <span class="hljs-comment">--行级触发器BEGIN<br><span class="line">trigger_stmt;<br><span class="line"><span class="hljs-keyword">END;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>查看：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">SHOW TRIGGERS;<br></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>删除：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">DROP <span class="hljs-keyword">TRIGGER trigger_name; <span class="hljs-comment">--如果没有指定 schema name，默认为当前数据库<br></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p><strong>案例：</strong></p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-comment">-- 插入数据触发器<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">trigger tb_user_insert_trigger<br><span class="line">after <span class="hljs-keyword">insert <span class="hljs-keyword">on tb_user <span class="hljs-keyword">for <span class="hljs-keyword">each <span class="hljs-type">row<br><span class="line"><span class="hljs-keyword">begin <br><span class="line"><span class="hljs-keyword">insert <span class="hljs-keyword">into user_logs(id, operation, operate_time, operate_id, operate_params)<span class="hljs-keyword">values<br><span class="line">(<span class="hljs-keyword">null, <span class="hljs-string">'insert', now(), new.id, concat(<span class="hljs-string">'插入的数据内容为：id=', new.id, <span class="hljs-string">',name=', new.name, <span class="hljs-string">', phone=', new.phone, <span class="hljs-string">', email=', new.email, <span class="hljs-string">', profession=', new.profession));<br><span class="line"><span class="hljs-keyword">end;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 查看<br><span class="line"><span class="hljs-keyword">show triggers;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 删除<br><span class="line"><span class="hljs-keyword">drop <span class="hljs-keyword">trigger tb_user_insert_trigger;<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 插入数据tb_user<br><span class="line"><span class="hljs-keyword">insert <span class="hljs-keyword">into tb_user(id, name, phtone, email, profession, age, gender, status, createtime) <span class="hljs-keyword">values(<span class="hljs-number">25, <span class="hljs-string">'二皇子', <span class="hljs-string">'1880901212', <span class="hljs-string">'erhuangzi@163.com', <span class="hljs-string">'软件工程', <span class="hljs-number">23, <span class="hljs-string">'1', <span class="hljs-string">'1'<span class="hljs-number">1, now());<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 修改数据触发器<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">trigger tb_user_update_trigger<br><span class="line">after <span class="hljs-keyword">update <span class="hljs-keyword">on tb_user <span class="hljs-keyword">for <span class="hljs-keyword">each <span class="hljs-type">row<br><span class="line"><span class="hljs-keyword">begin <br><span class="line"><span class="hljs-keyword">insert <span class="hljs-keyword">into user_logs(id, operation, operate_time, operate_id, operate_params)<span class="hljs-keyword">values<br><span class="line">(<span class="hljs-keyword">null, <span class="hljs-string">'update', now(), new.id, <br><span class="line"> concat(<span class="hljs-string">'更新之前的数据：id=', old.id, <span class="hljs-string">',name=', old.name, <span class="hljs-string">', phone=', old.phone, <span class="hljs-string">', email=', old.email, <span class="hljs-string">', profession=', old.profession,<br><span class="line"> <span class="hljs-string">'更新之后的数据：id=', new.id, <span class="hljs-string">',name=', new.name, <span class="hljs-string">', phone=', new.phone, <span class="hljs-string">', email=', new.email, <span class="hljs-string">', profession=', new.profession));<br><span class="line"><span class="hljs-keyword">end;<br><span class="line"><br><span class="line"><span class="hljs-keyword">update tb_user <span class="hljs-keyword">set age <span class="hljs-operator">= <span class="hljs-number">32 <span class="hljs-keyword">where id <span class="hljs-operator">= <span class="hljs-number">23;<br><span class="line"><span class="hljs-keyword">update tb_user <span class="hljs-keyword">set age <span class="hljs-operator">= <span class="hljs-number">32 <span class="hljs-keyword">where id <span class="hljs-operator"><= <span class="hljs-number">5; <span class="hljs-comment">-- 触发器为行级触发器，所以更改几行数据则出发几次，该语句出发5次<br><span class="line"><br><span class="line"><span class="hljs-comment">-- 删除数据触发器<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">trigger tb_user_delete_trigger<br><span class="line">after <span class="hljs-keyword">delete <span class="hljs-keyword">on tb_user <span class="hljs-keyword">for <span class="hljs-keyword">each <span class="hljs-type">row<br><span class="line"><span class="hljs-keyword">begin <br><span class="line"><span class="hljs-keyword">insert <span class="hljs-keyword">into user_logs(id, operation, operate_time, operate_id, operate_params)<span class="hljs-keyword">values<br><span class="line">(<span class="hljs-keyword">null, <span class="hljs-string">'insert', now(), old.id, <br><span class="line"> concat(<span class="hljs-string">'删除之前的数据：id=', new.id, <span class="hljs-string">',name=', old.name, <span class="hljs-string">', phone=', old.phone, <span class="hljs-string">', email=', old.email, <span class="hljs-string">', profession=', old.profession));<br><span class="line"><span class="hljs-keyword">end;<br><span class="line"><br><span class="line"><span class="hljs-keyword">delete <span class="hljs-keyword">from tb_user <span class="hljs-keyword">where id <span class="hljs-operator">= <span class="hljs-number">26;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h2 id="锁">锁</h2>
<p>介绍：</p>
<p>锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、I/0)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。</p>
<p>分类：</p>
<p>MySQL中的锁，按照锁的粒度分，分为一下三类：</p>
<ol>
<li>全局锁：锁定数据库中的所有表。</li>
<li>表级锁：每次操作锁住整张表。</li>
<li>行级锁：每次操作锁住对应的行数据。</li>
</ol>
<h3 id="全局锁">全局锁</h3>
<p>介绍：</p>
<p>全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。</p>
<p>其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。</p>
<p>基本操作：</p>
<p>使用全局锁：<code>flush tables with read lock</code><br>释放全局锁：<code>unlock tables</code></p>
<p>演示图：</p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/51083972978.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/fqweqwegqg.png"></p>
<p>特点：</p>
<p>数据库中加全局锁，是一个比较重的操作，存在以下问题:</p>
<ol>
<li>如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。</li>
<li>如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，会导致主从延迟。（该结构会在后续主从复制讲解）</li>
</ol>
<p>解决方法：</p>
<p>在InnoDB引擎中，我们可以在备份时加上参数 –single-transaction 参数来完成不加锁的一致性数据备份。</p>
<p><code>mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql</code>（只适用于支持「可重复读隔离级别的事务」的存储引擎）</p>
<p>原理补充：通过加上这个参数，确保了在备份开始时创建一个一致性的快照，通过启动一个新的事务来实现这一点。（该事务的隔离级别是Repeatable Read级别），从而实现在该事务读取下一直读取的是创建时的数据，而不影响其他事务的读写操作。</p>
<h3 id="表级锁">表级锁</h3>
<p>每次操作锁住整张表。锁定粒度大，发生锁的冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。</p>
<p>对于表级锁，主要分为一下三类：</p>
<ol>
<li>表锁</li>
<li>元数据锁（meta data lock，MDL）</li>
<li>意向锁</li>
</ol>
<h4 id="表锁">表锁</h4>
<p>对于表锁，分为两类：</p>
<ol>
<li>表共享读锁（read lock）</li>
<li>表独占写锁（write lock）</li>
</ol>
<p><strong>读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。</strong></p>
<p>语法：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-operator">/<span class="hljs-operator">/表级别的共享锁，也就是读锁；<br><span class="line"><span class="hljs-operator">/<span class="hljs-operator">/允许当前会话读取被锁定的表，但阻止其他会话对这些表进行写操作。<br><span class="line">lock tebles t_student read;<br><span class="line"><br><span class="line"><span class="hljs-operator">/<span class="hljs-operator">/表级锁的独占锁，也是写锁；<br><span class="line"><span class="hljs-operator">/<span class="hljs-operator">/允许当前会话对表进行读写操作，但阻止其他会话对这些表进行任何操作（读或写）。<br><span class="line">lock tables t_stuent write;<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p>释放所有锁：</p>
<p><code>unlock tables</code> （会话退出，也会释放所有锁）</p>
<h4 id="元数据锁">元数据锁</h4>
<p>MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表<br>上有活动事务的时候，不可以对元数据进行写入操作。<strong>为了避免DML与DDL冲突，保证读写的正确性。</strong></p>
<ul>
<li>对一张表进行 CRUD 操作时，加的是 <strong>MDL 读锁</strong>；</li>
<li>对一张表做结构变更操作的时候，加的是 <strong>MDL 写锁</strong>；</li>
</ul>
<div class="table-container">
<table>
<thead>
<tr><th align="center">对应SQL</th><th align="center">锁类型</th><th align="center">说明</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">lock tables xxx read /write</td>
<td align="center">SHARED_READ_ONLY/SHARED_NO_READ_WRITE</td>
<td align="center"> </td>
</tr>
<tr>
<td align="center">select 、 select … lock in share mode</td>
<td align="center">SHARED_READ</td>
<td align="center">与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥</td>
</tr>
<tr>
<td align="center">insert 、update、delete、select …for update</td>
<td align="center">SHARED_WRITE</td>
<td align="center">与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥</td>
</tr>
<tr>
<td align="center">alter table …</td>
<td align="center">EXCLYSIVE</td>
<td align="center">与其他的MDL都互斥</td>
</tr>
</tbody>
</table>
</div>
<p>查看元数据锁：</p>
<p><code>select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;</code></p>
<h4 id="意向锁">意向锁</h4>
<p>为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减<br>少表锁的检查。</p>
<p><strong>意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read)和独占表锁（lock tables … write）发生冲突</strong></p>
<p>如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。</p>
<p>那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。</p>
<p><strong>意向锁的目的是为了快速判断表里是否有记录被加锁</strong>。</p>
<p>加锁方式：</p>
<p>意向共享锁：（先在表上加上意向共享锁，然后对读取的记录加共享锁）<br>由 <code>select ... lock in share mode</code> 添加</p>
<p>意向独占锁：（先表上加上意向独占锁，然后对读取的记录加独占锁）<br>由 <code>insert、update、delete、select ... for update </code>添加</p>
<h4 id="AUTO-INC锁（补充）">AUTO-INC锁（补充）</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/qrer231r123r.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/qewf23f.png"></p>
<h3 id="行级锁">行级锁</h3>
<p>行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。</p>
<ol>
<li>行锁(Record Lock):锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。</li>
<li>间隙锁(GapLock):锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。</li>
<li>临键锁(Next-Key Lock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。</li>
</ol>
<h4 id="Record-Lock（行锁）">Record Lock（行锁）</h4>
<p>Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分。</p>
<p>InnoDB实现了以下两种类型的行锁：</p>
<ol>
<li>共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。</li>
<li>排他锁(X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。</li>
</ol>
<div class="table-container">
<table>
<thead>
<tr><th align="center"> </th><th align="center">S(共享锁)</th><th align="center">X(排他锁)</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">S(共享锁)</td>
<td align="center">兼容</td>
<td align="center">冲突</td>
</tr>
<tr>
<td align="center">X(排他锁)</td>
<td align="center">冲突</td>
<td align="center">冲突</td>
</tr>
</tbody>
</table>
</div>
<p>行锁类型：</p>
<div class="table-container">
<table>
<thead>
<tr><th align="center">SQL</th><th align="center">行锁类型</th><th align="center">说明</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">insert，update，delete …</td>
<td align="center">排他锁</td>
<td align="center">自动加锁</td>
</tr>
<tr>
<td align="center">select</td>
<td align="center">不加任何锁</td>
<td align="center"> </td>
</tr>
<tr>
<td align="center">select … lock in share mode</td>
<td align="center">共享锁</td>
<td align="center">需要手动select之后加上lock in share mode</td>
</tr>
<tr>
<td align="center">select … for update</td>
<td align="center">排他锁</td>
<td align="center">需要手动在select之后for update</td>
</tr>
</tbody>
</table>
</div>
<p>默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。</p>
<ol>
<li>针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。</li>
<li>InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么!nnoDB将对表中的所有记录加锁，此时 <strong>就会升级为表锁</strong>。</li>
</ol>
<p>查看意向锁及行锁的加锁情况：</p>
<p><code>select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from peformance_schema.data_locks;</code></p>
<h4 id="Gap-Lock（间隙锁）">Gap Lock（间隙锁）</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/qweg2431123qw.png"></p>
<h4 id="Next-Key-Lock（临键锁）">Next-Key Lock（临键锁）</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/qwegfqew24.png"></p>
<p>默认情况下，InnODB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。</p>
<ol>
<li>索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁 。</li>
<li>索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock退化为间隙锁。</li>
<li>索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。</li>
</ol>
<h2 id="InnoDB引擎">InnoDB引擎</h2>
<h3 id="逻辑存储结构">逻辑存储结构</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/ewqqeg423.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/ewqg1341.png"></p>
<h3 id="架构">架构</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/qewg13g423f.png"></p>
<h4 id="内存架构">内存架构</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/qwef12t132r.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/910310644164.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/2633356152.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/21810694716.png"></p>
<h4 id="磁盘结构">磁盘结构</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/71896219103.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/6345845897.png"></p>
<h4 id="后台线程">后台线程</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/1192969375.png"></p>
<h3 id="事务原理">事务原理</h3>
<p>事务：</p>
<p>事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。</p>
<p>特征：</p>
<ul>
<li>原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。</li>
<li>一致性(Consistency) ：事务完成时，必须使所有的数据都保持一致状态。</li>
<li>隔离性(lsolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。</li>
<li>持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。</li>
</ul>
<p>特性原理分类图：</p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/10723932987.png"></p>
<h4 id="redo-log">redo log</h4>
<p>重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的<strong>持久性</strong>。</p>
<p>该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。</p>
<p>Buffer Pool在产生脏页数据的时候，会先将数据存储到 redo log buffer 再存储到 redo log 中进行磁盘持久化存储，在内存出现异常（比如突然断电）时，通过redo log中持久化的数据进行回滚。过程如下图：</p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/7521579557.png"></p>
<p>redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举?</p>
<p>写入 redo log 的方式使用了追加操作，所以磁盘操作是<strong>顺序写</strong>，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是<strong>随机写</strong>。</p>
<h4 id="undo-log">undo log</h4>
<p>回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚 和 MVCC(多版本并发控制)。</p>
<p>undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当 delete 一条记录时，undo
log中会记录一条对应的insert记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。当执行
rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。</p>
<p>Undo log 销毁：undo log 在事务执行时产生，事务提交时，并不会立即删除undol0g，因为这些日志可能还用于 MVCC。</p>
<p>Undo log 存储：undo log 采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个 undo log segment.</p>
<h3 id="MVCC">MVCC</h3>
<p><strong>当前读：</strong></p>
<p>读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select…lock
in share mode(共享锁)，select… for update、update、insert、delete(排他锁)都是一种当前读。</p>
<p><strong>快照读：</strong></p>
<p>简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。</p>
<ul>
<li>Read committed:每次select，都生成一个快照读。</li>
<li>Repeatable Read:开启事务后第一个select语句才是快照读的地方。</li>
<li>Serializable:快照读会退化为当前读。</li>
</ul>
<p><strong>MVCC：</strong></p>
<p>全称 Multi-Version Concurrency
Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MVSOL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo
log日志、read View。</p>
<h4 id="三个隐藏字段">三个隐藏字段</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/9225237599.png"></p>
<h4 id="undo-log-1">undo log</h4>
<p>回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。</p>
<p>当insert的时候，产生的undoloq日志只在回滚时需要，在事务提交后，可被立即删除。</p>
<p>而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。</p>
<p>那么何时删除？</p>
<ul>
<li><strong>事务提交后</strong>：
<ul>
<li>对于<code>INSERT</code>操作，事务提交后，undo log可以被立即删除，因为不再需要用于回滚。</li>
<li>对于<code>UPDATE</code>和<code>DELETE</code>操作，undo log不会立即被删除，因为它们可能在后续的快照读取中被使用。</li>
</ul>
</li>
<li><strong>快照读取结束</strong>：
<ul>
<li>当所有依赖于该undo log的快照读取操作结束后，undo log才会被删除。这意味着如果有一个事务正在进行快照读取，并且依赖于某个undo log，那么这个undo log会一直保留直到该事务结束。</li>
</ul>
</li>
</ul>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/921810810932.png"></p>
<h4 id="readview">readview</h4>
<p>ReadView(读视图)是 快照读 SOL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id。</p>
<p>ReadView中包含了四个核心字段:</p>
<div class="table-container">
<table>
<thead>
<tr><th align="center">字段</th><th align="center">含义</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">m_ids</td>
<td align="center">当前活跃的事务ID集合</td>
</tr>
<tr>
<td align="center">min_trx_id</td>
<td align="center">最小活跃事务ID</td>
</tr>
<tr>
<td align="center">max_trx_id</td>
<td align="center">预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）</td>
</tr>
<tr>
<td align="center">creator_trx_id</td>
<td align="center">ReadView创建者的事务ID</td>
</tr>
</tbody>
</table>
</div>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/84711022753.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/6934998817.png"></p>
<p>依次比较 undo log 日志中版本数据链，找到可以进行访问的版本数据。</p>
<h2 id="MySQL管理">MySQL管理</h2>
<h3 id="系统数据库介绍">系统数据库介绍</h3>
<p>Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下：</p>
<div class="table-container">
<table>
<thead>
<tr><th align="center">数据库</th><th>含义</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">mysql</td>
<td>存储MVSQL服务器正常运行所需要的各种信息(时区、主从、用户、权限等)</td>
</tr>
<tr>
<td align="center">information_schema</td>
<td>提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等</td>
</tr>
<tr>
<td align="center">performance_schema</td>
<td>为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数</td>
</tr>
<tr>
<td align="center">sys</td>
<td>包含了一系列方便 DBA和开发人员利用 performance_schema性能数据库进行性能调优和诊断的视图</td>
</tr>
</tbody>
</table>
</div>
<h3 id="常用工具">常用工具</h3>
<h4 id="mysql">mysql</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/3233394951.png"></p>
<h4 id="mysqladmin">mysqladmin</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/10103131010198.png"></p>
<h4 id="mysqlbinlog">mysqlbinlog</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/1285451886.png"></p>
<h4 id="mysqlshow">mysqlshow</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/8210310352105.png"></p>
<h4 id="mysqldump">mysqldump</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/479211610103.png"></p>
<h4 id="mysqlimport-source">mysqlimport/source</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/34108898837.png"></p>
<h1 id="运维篇">运维篇</h1>
<h2 id="日志">日志</h2>
<h3 id="错误日志">错误日志</h3>
<p>错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。</p>
<p>该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置：</p>
<p><code>show variables like '%log_error%'</code></p>
<h3 id="二进制日志">二进制日志</h3>
<h4 id="介绍">介绍</h4>
<p>二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句，但不包括数据查询(SELECT、SHOW)语句。</p>
<p>作用：</p>
<ol>
<li>灾难时的数据恢复；</li>
<li>MySQL的主从复制。</li>
</ol>
<p>在MVSOL8版本中，默认二进制日志是开启着的，涉及到的参数如下：</p>
<p><code>show variables like '%log_bin%'</code></p>
<h4 id="日志格式">日志格式</h4>
<p>MySQL服务器中提供了多种格式来记录二进制记录，具体格式及特点如下：</p>
<div class="table-container">
<table>
<thead>
<tr><th align="center">日志格式</th><th align="center">含义</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">statement</td>
<td align="center">基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。</td>
</tr>
<tr>
<td align="center">row</td>
<td align="center">基于行的日志记录，记录的是每一行的数据变更。(默认)</td>
</tr>
<tr>
<td align="center">mined</td>
<td align="center">混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。</td>
</tr>
</tbody>
</table>
</div>
<p>查看参数方式：<code>show variables like '%binlog_format%';</code></p>
<h4 id="日志查看">日志查看</h4>
<p>由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看，具体语法：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">mysqlbinlog[参数选项]logfilename<br><span class="line"><br><span class="line">参数选项：<br><span class="line"> -d 指定数据库名称，只列出指定的数据库相关的操作。<br><span class="line"> -o 忽略掉日志中的前n行命令。<br><span class="line"> -v 将行事件(数据变更)重构为SOL语句。<br><span class="line"> -w 将行事件(数据变更)重构为SQL语句，并输出注释信息<br></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h4 id="日志删除">日志删除</h4>
<p>对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：</p>
<div class="table-container">
<table>
<thead>
<tr><th>指令</th><th>含义</th></tr>
</thead>
<tbody>
<tr>
<td>reset master</td>
<td>删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001重新开始</td>
</tr>
<tr>
<td>purge master logs to ‘binlog.***’</td>
<td>删除 *** 编号之前的所有日志</td>
</tr>
<tr>
<td>purge master logs before ‘yyyy-mm-dd hh24:mi:ss’</td>
<td>删除日志为”yyyy-mm-dd hh24:mi:ss”之前产生的所有日志</td>
</tr>
</tbody>
</table>
</div>
<p>也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除.</p>
<p><code>show variables like '%binlog_expire_logs_seconds%'</code></p>
<h3 id="查询日志">查询日志</h3>
<p>查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置一下配置：</p>
<p>修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">#该选项用来开启查询日志，可选值：0或者1；0代表关闭，1代表开启<br><span class="line">general_log=1<br><span class="line">#设置日志的文件名 ， 如果没有指定，默认的文件名为 host_name.log<br><span class="line">general_log_file=mysql_query.log<br></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="慢查询日志-1">慢查询日志</h3>
<p>慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit的所有的SQL语句的日志，默认未开启。long_query_time 默认为 10 秒，最小为0，精度可以到微秒。</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">#慢查询日志<br><span class="line">slow_query_log<span class="hljs-operator">=<span class="hljs-number">1<br><span class="line">#执行时间参数<br><span class="line">long_query_time<span class="hljs-operator">=<span class="hljs-number">2<br></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p>默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes，如下所述。</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">#记录执行较慢的管理语句<br><span class="line">log_slow_admin_statements <span class="hljs-operator">= <span class="hljs-number">1<br><span class="line">#记录执行较慢的未使用索引的语句<br><span class="line">log_queries_not_using_indexes <span class="hljs-operator">= <span class="hljs-number">1<br></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h2 id="主从复制">主从复制</h2>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/58131011726.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/4454682776.png"></p>
<h3 id="原理">原理</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/7410105910882.png"></p>
<h3 id="搭建实现">搭建实现</h3>
<h4 id="主库配置">主库配置</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/21103533211.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/24794193710.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/91294866110.png"></p>
<h4 id="从库配置">从库配置</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/10617439921.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/21053341798.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/6153782135.png"></p>
<h4 id="测试">测试</h4>
<p>1、在主库上创建数据库、表，并插入数据</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line"><span class="hljs-keyword">create database db01;<br><span class="line">use db01;<br><span class="line"><span class="hljs-keyword">create <span class="hljs-keyword">table tb_use(<br><span class="line"> id <span class="hljs-type">int(<span class="hljs-number">11) <span class="hljs-keyword">primary key <span class="hljs-keyword">not <span class="hljs-keyword">null auto_increment,<br><span class="line"> name <span class="hljs-type">varchar(<span class="hljs-number">50) <span class="hljs-keyword">not <span class="hljs-keyword">null,<br><span class="line"> sex <span class="hljs-type">varchar(<span class="hljs-number">1)<br><span class="line">)engine<span class="hljs-operator">=innodb <span class="hljs-keyword">default charset<span class="hljs-operator">=utf8mb4;<br><span class="line"><span class="hljs-keyword">insert <span class="hljs-keyword">into tb_user(id, name, sex) valurs (<span class="hljs-keyword">null, <span class="hljs-string">'Tom', <span class="hljs-string">'1'), (<span class="hljs-keyword">null, <span class="hljs-string">'Trigger', <span class="hljs-string">'0'), (<span class="hljs-keyword">null, <span class="hljs-string">'Dawn', <span class="hljs-string">'1');<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<p>2、在从库中查询数据，验证主从是否同步。</p>
<h2 id="分库分表">分库分表</h2>
<h3 id="介绍-1">介绍</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/4765397657.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/35239910325.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/106169108266.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/4616215143.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/86213810658.png"></p>
<h3 id="Mycat概述">Mycat概述</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/81010325101039.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/6382138721.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/101795210673.png"></p>
<h3 id="Mycat入门">Mycat入门</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/96457210797.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/1891042610310.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/qwe1324t14t.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/10197188896.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/59710648531.png"></p>
<h3 id="Mycat配置">Mycat配置</h3>
<h4 id="schema-xml">schema.xml</h4>
<p>schema.xml作为MyCat中最重要的配置文件之一,涵盖了MyCat的逻辑库 、逻辑表 、分片规则、分片节点及数据源的配置.</p>
<p>主要包含以下三组标签:</p>
<ul>
<li>schema标签</li>
<li>datanode标签</li>
<li>datahost标签</li>
</ul>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/32368131027.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/7484479134.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/10769359339.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/91315810623.png"></p>
<h4 id="rule-xml">rule.xml</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/951010415971.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/84271093639.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/3853211256.png"></p>
<h3 id="Mycat分片">Mycat分片</h3>
<h4 id="垂直分库">垂直分库</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/9325751819.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/21931016227.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/9852726692.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/58109378671.png"></p>
<h4 id="水平分表">水平分表</h4>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/1269361453.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/9977715254.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/131081045236.png"></p>
<h4 id="分片规则">分片规则</h4>
<h5 id="范围分片">范围分片</h5>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/34510781883.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/2654319698.png"></p>
<h5 id="取模分片">取模分片</h5>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/22173110191.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/7347361194.png"></p>
<h5 id="一致性hash算法">一致性hash算法</h5>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/73610917872.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/3171425417.png"></p>
<h5 id="枚举分片">枚举分片</h5>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/667103121103.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/11088699372.png"></p>
<h5 id="应用指定算法">应用指定算法</h5>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/2632725279.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/1452119317.png"></p>
<h5 id="固定hash算法">固定hash算法</h5>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/2375113118.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/1368697562.png"></p>
<h5 id="字符串hash解析">字符串hash解析</h5>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/53710618932.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/8619959324.png"></p>
<h5 id="按天分片">按天分片</h5>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/1010769110977.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/109109981999.png"></p>
<h5 id="按自然月分片">按自然月分片</h5>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/4762746916.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/22673141012.png"></p>
<h3 id="Mycat管理及监控">Mycat管理及监控</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/73451065142.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/7348156477.png"></p>
<h2 id="读写分离">读写分离</h2>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/52510452433.png"></p>
<h3 id="介绍-2">介绍</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/10918829811.png"></p>
<h3 id="一主一从读写分离">一主一从读写分离</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/52287271210.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/4564143383.png"></p>
<h3 id="双主双从">双主双从</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/245101066682.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/6338368756.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/2677654314.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/105101448841.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/7237222812.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/6246592973.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/12919109752.png"></p>
<p>测试：</p>
<p>分别在两台主库Master1、Master2上执行DDL、DML语句，查看涉及到的数据库服务器的数据同步情况。</p>
<div class="table-container"> </div>
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">create database db01;<br><span class="line">use db01;<br><span class="line">create table tb_user(<br><span class="line"> id in(11)not null primary key,<br><span class="line"> name varchar(50) not null,<br><span class="line"> sex varcahr(1)<br><span class="line">)engine=innodb default charset=utf8mb4<br><span class="line"><br><span class="line">insert into tb user(id,name,sex) values(l,'Tom','1');<br><span class="line">insert into tb user(id,name,sex) values(2,'Trigger','0');<br><span class="line">insert into tb user(id,name,sex) values(3,'Dawn','1');<br><span class="line">insert into tb user(id,name,sex) values(4,"ack Ma','1');<br><span class="line">insertinto tb user(id,name,sex) values(5,'Coco','0');<br><span class="line">insert into tb user(id,name,sex) values(6,'erry','1');<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
<h3 id="双主双从读写分离">双主双从读写分离</h3>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/8536419478.png"></p>
<p><img src="https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/15610682864.png"></p>
<p>测试：</p>
<p>登录MyCat，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。</p>
<p>当主库挂掉一个之后，是否能够自动切换。</p>
<h1 id="数据类型">数据类型</h1>
<h2 id="整型">整型</h2>
<div class="table-container">
<table>
<thead>
<tr><th>类型名称</th><th>取值范围</th><th>大小</th></tr>
</thead>
<tbody>
<tr>
<td>TINYINT</td>
<td>-128〜127</td>
<td>1个字节</td>
</tr>
<tr>
<td>SMALLINT</td>
<td>-32768〜32767</td>
<td>2个宇节</td>
</tr>
<tr>
<td>MEDIUMINT</td>
<td>-8388608〜8388607</td>
<td>3个字节</td>
</tr>
<tr>
<td>INT (INTEGHR)</td>
<td>-2147483648〜2147483647</td>
<td>4个字节</td>
</tr>
<tr>
<td>BIGINT</td>
<td>-9223372036854775808〜9223372036854775807</td>
<td>8个字节</td>
</tr>
</tbody>
</table>
</div>
<p>无符号在数据类型后加 unsigned 关键字。</p>
<h2 id="浮点型">浮点型</h2>
<div class="table-container">
<table>
<thead>
<tr><th>类型名称</th><th>说明</th><th>存储需求</th></tr>
</thead>
<tbody>
<tr>
<td>FLOAT</td>
<td>单精度浮点数</td>
<td>4 个字节</td>
</tr>
<tr>
<td>DOUBLE</td>
<td>双精度浮点数</td>
<td>8 个字节</td>
</tr>
<tr>
<td>DECIMAL (M, D)，DEC</td>
<td>压缩的“严格”定点数</td>
<td>M+2 个字节</td>
</tr>
</tbody>
</table>
</div>
<h2 id="日期和时间">日期和时间</h2>
<div class="table-container">
<table>
<thead>
<tr><th>类型名称</th><th>日期格式</th><th>日期范围</th><th>存储需求</th></tr>
</thead>
<tbody>
<tr>
<td>YEAR</td>
<td>YYYY</td>
<td>1901 ~ 2155</td>
<td>1 个字节</td>
</tr>
<tr>
<td>TIME</td>
<td>HH:MM:SS</td>
<td>-838:59:59 ~ 838:59:59</td>
<td>3 个字节</td>
</tr>
<tr>
<td>DATE</td>
<td>YYYY-MM-DD</td>
<td>1000-01-01 ~ 9999-12-3</td>
<td>3 个字节</td>
</tr>
<tr>
<td>DATETIME</td>
<td>YYYY-MM-DD HH:MM:SS</td>
<td>1000-01-01 00:00:00 ~ 9999-12-31 23:59:59</td>
<td>8 个字节</td>
</tr>
<tr>
<td>TIMESTAMP</td>
<td>YYYY-MM-DD HH:MM:SS</td>
<td>1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC</td>
<td>4 个字节</td>
</tr>
</tbody>
</table>
</div>
<h2 id="字符串">字符串</h2>
<div class="table-container">
<table>
<thead>
<tr><th>类型名称</th><th>说明</th><th>存储需求</th></tr>
</thead>
<tbody>
<tr>
<td>CHAR(M)</td>
<td>固定长度非二进制字符串</td>
<td>M 字节，1<=M<=255</td>
</tr>
<tr>
<td>VARCHAR(M)</td>
<td>变长非二进制字符串</td>
<td>L+1字节，在此，L< = M和 1<=M<=255</td>
</tr>
<tr>
<td>TINYTEXT</td>
<td>非常小的非二进制字符串</td>
<td>L+1字节，在此，L<2^8</td>
</tr>
<tr>
<td>TEXT</td>
<td>小的非二进制字符串</td>
<td>L+2字节，在此，L<2^16</td>
</tr>
<tr>
<td>MEDIUMTEXT</td>
<td>中等大小的非二进制字符串</td>
<td>L+3字节，在此，L<2^24</td>
</tr>
<tr>
<td>LONGTEXT</td>
<td>大的非二进制字符串</td>
<td>L+4字节，在此，L<2^32</td>
</tr>
<tr>
<td>ENUM</td>
<td>枚举类型，只能有一个枚举字符串值</td>
<td>1或2个字节，取决于枚举值的数目 (最大值为65535)</td>
</tr>
<tr>
<td>SET</td>
<td>一个设置，字符串对象可以有零个或 多个SET成员</td>
<td>1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）</td>
</tr>
</tbody>
</table>
</div>
<h2 id="二进制类型">二进制类型</h2>
<div class="table-container">
<table>
<thead>
<tr><th>类型名称</th><th>说明</th><th>存储需求</th></tr>
</thead>
<tbody>
<tr>
<td>BIT(M)</td>
<td>位字段类型</td>
<td>大约 (M+7)/8 字节</td>
</tr>
<tr>
<td>BINARY(M)</td>
<td>固定长度二进制字符串</td>
<td>M 字节</td>
</tr>
<tr>
<td>VARBINARY (M)</td>
<td>可变长度二进制字符串</td>
<td>M+1 字节</td>
</tr>
<tr>
<td>TINYBLOB (M)</td>
<td>非常小的BLOB</td>
<td>L+1 字节，在此，L<2^8</td>
</tr>
<tr>
<td>BLOB (M)</td>
<td>小 BLOB</td>
<td>L+2 字节，在此，L<2^16</td>
</tr>
<tr>
<td>MEDIUMBLOB (M)</td>
<td>中等大小的BLOB</td>
<td>L+3 字节，在此，L<2^24</td>
</tr>
<tr>
<td>LONGBLOB (M)</td>
<td>非常大的BLOB</td>
<td>L+4 字节，在此，L<2^32</td>
</tr>
</tbody>
</table>
</div>
<h1 id="权限一览表">权限一览表</h1>
<blockquote>
<p>具体权限的作用详见官方文档</p>
</blockquote>
<p>GRANT 和 REVOKE 允许的静态权限</p>
<div class="table-container">
<table>
<thead>
<tr><th align="left">Privilege</th><th align="left">Grant Table Column</th><th align="left">Context</th></tr>
</thead>
<tbody>
<tr>
<td align="left"><code>ALL </code></td>
<td align="left">Synonym for “all privileges”</td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>ALTER</code></td>
<td align="left"><code>Alter_priv</code></td>
<td align="left">Tables</td>
</tr>
<tr>
<td align="left"><code>ALTER ROUTINE</code></td>
<td align="left"><code>Alter_routine_priv</code></td>
<td align="left">Stored routines</td>
</tr>
<tr>
<td align="left"><code>CREATE</code></td>
<td align="left"><code>Create_priv</code></td>
<td align="left">Databases, tables, or indexes</td>
</tr>
<tr>
<td align="left"><code>CREATE ROLE</code></td>
<td align="left"><code>Create_role_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>CREATE ROUTINE</code></td>
<td align="left"><code>Create_routine_priv</code></td>
<td align="left">Stored routines</td>
</tr>
<tr>
<td align="left"><code>CREATE TABLESPACE</code></td>
<td align="left"><code>Create_tablespace_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>CREATE TEMPORARY TABLES</code></td>
<td align="left"><code>Create_tmp_table_priv</code></td>
<td align="left">Tables</td>
</tr>
<tr>
<td align="left"><code>CREATE USER</code></td>
<td align="left"><code>Create_user_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>CREATE VIEW</code></td>
<td align="left"><code>Create_view_priv</code></td>
<td align="left">Views</td>
</tr>
<tr>
<td align="left"><code>DELETE</code></td>
<td align="left"><code>Delete_priv</code></td>
<td align="left">Tables</td>
</tr>
<tr>
<td align="left"><code>DROP</code></td>
<td align="left"><code>Drop_priv</code></td>
<td align="left">Databases, tables, or views</td>
</tr>
<tr>
<td align="left"><code>DROP ROLE</code></td>
<td align="left"><code>Drop_role_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>EVENT</code></td>
<td align="left"><code>Event_priv</code></td>
<td align="left">Databases</td>
</tr>
<tr>
<td align="left"><code>EXECUTE</code></td>
<td align="left"><code>Execute_priv</code></td>
<td align="left">Stored routines</td>
</tr>
<tr>
<td align="left"><code>FILE</code></td>
<td align="left"><code>File_priv</code></td>
<td align="left">File access on server host</td>
</tr>
<tr>
<td align="left"><code>GRANT OPTION</code></td>
<td align="left"><code>Grant_priv</code></td>
<td align="left">Databases, tables, or stored routines</td>
</tr>
<tr>
<td align="left"><code>INDEX</code></td>
<td align="left"><code>Index_priv</code></td>
<td align="left">Tables</td>
</tr>
<tr>
<td align="left"><code>INSERT</code></td>
<td align="left"><code>Insert_priv</code></td>
<td align="left">Tables or columns</td>
</tr>
<tr>
<td align="left"><code>LOCK TABLES</code></td>
<td align="left"><code>Lock_tables_priv</code></td>
<td align="left">Databases</td>
</tr>
<tr>
<td align="left"><code>PROCESS</code></td>
<td align="left"><code>Process_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>PROXY</code></td>
<td align="left">See <code>proxies_priv</code> table</td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>REFERENCES</code></td>
<td align="left"><code>References_priv</code></td>
<td align="left">Databases or tables</td>
</tr>
<tr>
<td align="left"><code>RELOAD</code></td>
<td align="left"><code>Reload_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>REPLICATION CLIENT</code></td>
<td align="left"><code>Repl_client_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>REPLICATION SLAVE</code></td>
<td align="left"><code>Repl_slave_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>SELECT</code></td>
<td align="left"><code>Select_priv</code></td>
<td align="left">Tables or columns</td>
</tr>
<tr>
<td align="left"><code>SHOW DATABASES</code></td>
<td align="left"><code>Show_db_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>SHOW VIEW</code></td>
<td align="left"><code>Show_view_priv</code></td>
<td align="left">Views</td>
</tr>
<tr>
<td align="left"><code>SHUTDOWN</code></td>
<td align="left"><code>Shutdown_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>SUPER</code></td>
<td align="left"><code>Super_priv</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>TRIGGER</code></td>
<td align="left"><code>Trigger_priv</code></td>
<td align="left">Tables</td>
</tr>
<tr>
<td align="left"><code>UPDATE</code></td>
<td align="left"><code>Update_priv</code></td>
<td align="left">Tables or columns</td>
</tr>
<tr>
<td align="left"><code>USAGE</code></td>
<td align="left">Synonym for “no privileges”</td>
<td align="left">Server administration</td>
</tr>
</tbody>
</table>
</div>
<p>GRANT 和 REVOKE 允许的动态权限</p>
<div class="table-container">
<table>
<thead>
<tr><th align="left">Privilege</th><th align="left">Context</th></tr>
</thead>
<tbody>
<tr>
<td align="left"><code>APPLICATION_PASSWORD_ADMIN</code></td>
<td align="left">Dual password administration</td>
</tr>
<tr>
<td align="left"><code>AUDIT_ABORT_EXEMPT</code></td>
<td align="left">Allow queries blocked by audit log filter</td>
</tr>
<tr>
<td align="left"><code>AUDIT_ADMIN</code></td>
<td align="left">Audit log administration</td>
</tr>
<tr>
<td align="left"><code>AUTHENTICATION_POLICY_ADMIN</code></td>
<td align="left">Authentication administration</td>
</tr>
<tr>
<td align="left"><code>BACKUP_ADMIN</code></td>
<td align="left">Backup administration</td>
</tr>
<tr>
<td align="left"><code>BINLOG_ADMIN</code></td>
<td align="left">Backup and Replication administration</td>
</tr>
<tr>
<td align="left"><code>BINLOG_ENCRYPTION_ADMIN</code></td>
<td align="left">Backup and Replication administration</td>
</tr>
<tr>
<td align="left"><code>CLONE_ADMIN</code></td>
<td align="left">Clone administration</td>
</tr>
<tr>
<td align="left"><code>CONNECTION_ADMIN</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>ENCRYPTION_KEY_ADMIN</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>FIREWALL_ADMIN</code></td>
<td align="left">Firewall administration</td>
</tr>
<tr>
<td align="left"><code>FIREWALL_EXEMPT</code></td>
<td align="left">Firewall administration</td>
</tr>
<tr>
<td align="left"><code>FIREWALL_USER</code></td>
<td align="left">Firewall administration</td>
</tr>
<tr>
<td align="left"><code>FLUSH_OPTIMIZER_COSTS</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>FLUSH_STATUS</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>FLUSH_TABLES</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>FLUSH_USER_RESOURCES</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>GROUP_REPLICATION_ADMIN</code></td>
<td align="left">Replication administration</td>
</tr>
<tr>
<td align="left"><code>GROUP_REPLICATION_STREAM</code></td>
<td align="left">Replication administration</td>
</tr>
<tr>
<td align="left"><code>INNODB_REDO_LOG_ARCHIVE</code></td>
<td align="left">Redo log archiving administration</td>
</tr>
<tr>
<td align="left"><code>NDB_STORED_USER</code></td>
<td align="left">NDB Cluster</td>
</tr>
<tr>
<td align="left"><code>PASSWORDLESS_USER_ADMIN</code></td>
<td align="left">Authentication administration</td>
</tr>
<tr>
<td align="left"><code>PERSIST_RO_VARIABLES_ADMIN</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>REPLICATION_APPLIER</code></td>
<td align="left"><code>PRIVILEGE_CHECKS_USER</code> for a replication channel</td>
</tr>
<tr>
<td align="left"><code>REPLICATION_SLAVE_ADMIN</code></td>
<td align="left">Replication administration</td>
</tr>
<tr>
<td align="left"><code>RESOURCE_GROUP_ADMIN</code></td>
<td align="left">Resource group administration</td>
</tr>
<tr>
<td align="left"><code>RESOURCE_GROUP_USER</code></td>
<td align="left">Resource group administration</td>
</tr>
<tr>
<td align="left"><code>ROLE_ADMIN</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>SESSION_VARIABLES_ADMIN</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>SET_USER_ID</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>SHOW_ROUTINE</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>SYSTEM_USER</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>SYSTEM_VARIABLES_ADMIN</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>TABLE_ENCRYPTION_ADMIN</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>VERSION_TOKEN_ADMIN</code></td>
<td align="left">Server administration</td>
</tr>
<tr>
<td align="left"><code>XA_RECOVER_ADMIN</code></td>
<td align="left">Server administration</td>
</tr>
</tbody>
</table>
</div>
<h1 id="图形化界面工具">图形化界面工具</h1>
<ul>
<li>Workbench(免费): http://dev.mysql.com/downloads/workbench/</li>
<li>navicat(收费，试用版30天): https://www.navicat.com/en/download/navicat-for-mysql</li>
<li>Sequel Pro(开源免费，仅支持Mac OS): http://www.sequelpro.com/</li>
<li>HeidiSQL(免费): http://www.heidisql.com/</li>
<li>phpMyAdmin(免费): https://www.phpmyadmin.net/</li>
<li>SQLyog: https://sqlyog.en.softonic.com/</li>
</ul>
<h1 id="安装">安装</h1>
<h1 id="小技巧">小技巧</h1>
<ol>
<li>在SQL语句之后加上<code>\G</code>会将结果的表格形式转换成行文本形式</li>
<li>查看Mysql数据库占用空间：</li>
</ol>
<div class="table-container"> </div>
<div class="post-body animated fadeInDown">
<div class="table-container">
<table>
<tbody>
<tr>
<td class="code">
<pre><span class="line">SELECT table_schema "Database Name"<br><span class="line"> , SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB"<br><span class="line">FROM information_schema.TABLES<br><span class="line">GROUP BY table_schema;<br></span></span></span></span></pre>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="reward-container">
<div>请我一杯咖啡吧！</div>
</div>
<div class="reward-container"> </div>
<div class="post-copyright">
<ul>
<li class="post-copyright-author"><strong>本文作者： </strong>DHC</li>
<li class="post-copyright-link"><strong>本文链接：</strong> https://jimhackking.github.io/运维/MySQL学习笔记/</li>
<li class="post-copyright-license"><strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！</li>
</ul>
</div>
<div class="followme">欢迎关注我的其它发布渠道
<div class="social-list">
<div class="social-item"> <span class="icon"> <span class="label">GitHub </span></span></div>
<div class="social-item"> <span class="icon"> <span class="label">Gitee </span></span></div>
<div class="social-item"> <span class="icon"> <span class="label">BiliBili </span></span></div>
</div>
</div>
<div class="post-tags"> MySQL</div>
<div class="post-tags">MySQL学习笔记</div>
<div class="post-tags">原文地址：https://jimhackking.github.io/%E8%BF%90%E7%BB%B4/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/</div><br><br>
来源：https://www.cnblogs.com/python-learn/p/19154678
頁:
[1]