MySQL学习笔记1-基础篇

公号：码农充电站pro

主页：https://codeshellme.github.io

这 5 篇文章是我在学习 MySQL 的过程中，总结的笔记：

• 第一篇 MySQL 学习笔记1-基础篇
  • 1，关于 SQL
  • 2，一条 SQL 的执行步骤
  • 3，MySQL 存储引擎
  • 4，数据库的基本操作
  • 5，关于自增主键
  • 6，SELECT 语句顺序
  • 7，WHERE 子句
  • 8，DISTINCT 去重
  • 9，关于 COUNT(*) 操作
  • 10，MYSQL 函数
  • 11，GROUP BY 数据分组
  • 12，子查询(嵌套查询)
  • 13，JOIN 连接查询
  • 14，VIEW 视图
  • 15，存储过程
  • 16，临时表
  • 17，MySQL 权限管理
  • 18，Python 操作 MySQL 的库
• 第二篇 MySQL 学习笔记2-进阶篇-上
  • 19，MySQL 的基础架构
  • 20，数据库缓冲池
  • 21，数据库中的存储结构
  • 22，InnoDB 中表数据的存储
• 第三篇 MySQL 学习笔记3-进阶篇-中
  • 23，事务处理
  • 24，事务的隔离级别
  • 25，MySQL 中的锁
  • 26，MVCC 多版本并发控制
  • 27，MySQL 传输数据的原理
• 第四篇 MySQL 学习笔记4-进阶篇-下
  • 28，Join 语句的原理
  • 29，MySQL 如何进行排序
  • 30，MySQL 中 kill 命令的原理
  • 31，MySQL 中的 mysqldump 命令
  • 32，MySQL 主从同步
  • 33，MySQL 主备原理
• 第五篇 MySQL 学习笔记5-调优篇
  • 34，关于 MySQL 索引
  • 35，定位数据库 SQL 性能问题

1，关于 SQL

1.1，SQL 的两个标准

SQL 有两个重要的标准，分别是 SQL92 和 SQL99，它们分别代表了 92 年和 99 年颁布的 SQL 标准，今天的 SQL 语言依然遵循这些标准。

1.2，SQL 命名规范

表名、表别名、字段名、字段别名等都小写；SQL 保留字、函数名、绑定变量等都大写。

SELECT name, hp_max FROM heros WHERE role_main = '战士';

1.3，SQL 语句大小写问题

SELECT * FROM heros WHERE name = 'guanyu'; SELECT * FROM heros WHERE name = 'GUANYU';

上面两个语句，在 Oracle 中是不同的查询，而在 MySQL 中是相同的查询。

同时，可以通过修改系统参数来配置，比如在 MySQL 中可以通过参数 lower_case_table_names 来配置数据库和数据表的大小写敏感性。

2，一条 SQL 的执行步骤

一条 SQL 语句在数据库中的执行步骤如下：

注意在 MySQL 8.0 之后，查询缓存功能被取消。

3，MySQL 存储引擎

MySQL 的存储引擎是插件式的，在使用时可以选择不同的存储引擎。

在 MySQL 中查看可用的存储引擎：

show engines;

结果如下：

InnoDB 与 Memory 的区别：

• InnoDB 表的数据是有序存放的，Memory 表的数据是按照写入顺序存放的
• 当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而 Memory 表找到空位就可以插入新值
• 数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而 Memory 表需要修改所有索引
• InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，因此 Memory 表的每行数据长度相同

4，数据库基本操作

4.1，创建与删除数据库

CREATE DATABASE nba; -- 创建一个名为 nba 的数据库 DROP DATABASE nba; -- 删除一个名为 nba 的数据库

4.2，创建表结构

MySQL 官方文档 建议我们尽量将数据表的字段设置为 NOT NULL 约束，这样做的好处是可以更好地使用索引，节省空间，甚至加速 SQL 的运行。

创建表的语法如下：

CREATE TABLE [table_name](字段名 数据类型，......)

注意：如果创建的表没有主键，或者把一个表的主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。

CREATE TABLE player ( player_id int(11) NOT NULL AUTO_INCREMENT, player_name varchar(255) NOT NULL

• NOT NULL PRIMARY KEY AUTO_INCREMENT 表示自增主键，NOT NULL AUTO_INCREMENT 只表示自增而非主键。
  • 自增主键，在插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。
• int(11) 代表整数类型，显示长度为 11 位，括号中的参数 11 代表的是最大有效显示长度，与类型包含的数值范围大小无关。
  • int 默认表示有符号整数，长度未定义的话，默认是 11；
  • 无符号整数表示方法：int unsighed，长度未定义的话，默认是 10。
  • 如果插入的数值大小超出了所能表示的范围，将默认插入一个临界值。
• varchar(255) 代表的是最大长度为 255 个字符数的可变长字符串。
  • 注意单位是字符数，一个汉字与一个英文字母都是一个字符。
  • 对于 varchar(M)，M 的范围是 0 ~ 25535。
  • 在定义时，M 不可省略。
  • 如果超出了长度，超出的部分会被截断。
• char(M)：表示固定长字符串，M 的范围是 0 ~ 255。
  • 在定义时，M 可省略，默认为 1。
  • 如果超出了长度，超出的部分会被截断。
  • 固定长度的意思是，不管实际存储的数据是多大，都会占用固定的空间；容易造成空间浪费，但是检索性能比可变长的要好一些。
  • 可变长度的意思是，会根据实际的数据的大小，去占用相应的空间，不会造成空间浪费。
• float(M, N)：单精度浮点数
  • N 表示小数位的位数，小数位如果超出N，则四舍五入；如果不够N 则用 0 补齐。
  • M 表示整数位的位数与小数位的位数之和，如果超出 M，则存储一个边界值。
  • M 和 N 都可省略，表示不限制范围。
• double(M, N)：双精度浮点数
• DATETIME：占 8 字节
  • 表示的时间范围 1000-01-01 00:00:00/9999-12-31 23:59:59
• TIMESTAMP：占 4 字节
  • 表示的时间范围 1970-01-01 00:00:00/2038
• DECIMAL：常用于表示金额
• CHECK 约束：用来检查特定字段取值范围的有效性，CHECK 约束的结果不能为 FALSE
  • 比如可以对身高 height 的数值进行 CHECK 约束，必须≥0，且＜3
  • 即 CHECK(height>=0 AND height<3)

对于 int 类型的显示长度的使用，要搭配 zerofill 关键字来使用，如果不与 zerofill 一起使用，其实 int(n) 中的 n，并没有实际意义，n 是几都一样（如果数值不够 n 位，也不会用 0 补齐）。

zerofill 的使用方法：

create table t1 { t1 int(7) zerofill, t2 int(7) zerofill

此时如果插入的数值，不够 7 位数，则前边用 0 补齐；比如插入了 123，则显示如下：

从 Navicat 中导出的 SQL 语句，示例：

DROP TABLE IF EXISTS `player`; CREATE TABLE `player` ( `player_id` int(11) NOT NULL AUTO_INCREMENT, `team_id` int(11) NOT NULL, `player_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `height` float(3, 2) NULL DEFAULT 0.00, PRIMARY KEY (`player_id`) USING BTREE, UNIQUE INDEX `player_name`(`player_name`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

• SET = utf8 代表字符编码
• COLLATE = utf8_general_ci 代表排序规则
  • utf8_general_ci 代表对大小写不敏感
  • 如果设置为 utf8_bin，代表对大小写敏感
  • 还有许多其他排序规则
• PRIMARY KEY 代表主键
  • 主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE+NOT NULL
  • 一个数据表的主键只能有一个
  • 主键可以是一个字段，也可以由多个字段复合组成
• USING BTREE 表示底层使用 BTREE 作为索引结构
  • 其它的索引结构还有 HASH
• UNIQUE INDEX 代表唯一索引，可以设置为其他索引方式，比如 NORMAL INDEX（普通索引）。
  • 唯一索引和普通索引的区别在于，它对字段进行了唯一性的约束，索引方式使用了 BTREE

其它建表方式

-- A 的结构是 B 的精简版，A 中没有索引等信息 -- A 中的数据与 B 中的数据完全相同 create table A as select * from B; -- A 的结构与 B 完全相同，包括索引等其它信息 -- A 是空表，没有任何数据 create table A like B; -- 可以使用下面语句将 B 中的数据插入 A insert into A select * from B;

4.3，修改表结构

添加字段，在 player 表中添加 age 字段：

ALTER TABLE player ADD (age int(11));

修改字段名，将 player 表中的 age 字段名改为 player_age：

ALTER TABLE player RENAME COLUMN age to player_age

修改字段数据类型，将 player 表中的 player_age 字段的数据类型改为 float(3,1)：

ALTER TABLE player MODIFY (player_age float(3,1));

删除字段，将 player 表中的 player_age 字段删除：

ALTER TABLE player DROP COLUMN player_age;

5，关于自增主键

在 MySQL 里面，如果字段被定义为 AUTO_INCREMENT，则表示该字段自增。

5.1，查看自增值

命令 show create table table_name 可以看到下一个可用的自增值，如下：

5.2，自增值是如何保存的

不同的引擎对于自增值的保存策略不同：

• MyISAM 引擎的自增值保存在数据文件中
• InnoDB 引擎的自增值：
  • 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值
  • 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值

5.3，自增值的生成算法

自增值的特性如下：

• 如果插入数据时 id 字段指定为 0、NULL 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段
• 如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。假设，某次要插入的值是 X，当前的自增值是 Y：
  • 如果 X<Y，那么这个表的自增值不变；
  • 如果 X≥Y，就需要把当前自增值修改为新的自增值。

新的自增值生成算法是：以参数 auto_increment_offset 为初始值，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。

这两个参数的默认值都是 1：

• 当准备插入的值 >= 当前自增值，新的自增值就是“准备插入的值 +1”
• 否则，自增值不变

双 M 的主备架构里要求双写的时候，会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突

5.4，自增值不能保证是连续的

在这两个参数都设置为 1 的时候，自增主键 id 不能保证是连续的，有以下情况：

• 唯一键冲突是导致自增主键 id 不连续的第一种原因
• 事务回滚也会产生类似的现象，这就是第二种原因

5.5，自增 id 可能被用完

MySQL 中无符号整型 (unsigned int) 是 4 个字节，上限就是 2^32-1。

表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。这就导致，当自增 id 达到上限后，如果再插入新的值，会导致自增id 重复的错误，因此就无法再插入新的值。

因此，在建表的时候，你需要考察你的表是否有可能达到这个上限，如果有可能，就应该创建成 8 个字节的 bigint unsigned。

InnoDB 系统自增 row_id

如果创建的 InnoDB 表没有指定主键，那么 InnoDB 会创建一个不可见的，长度为 6 个字节的 row_id。

row_id 是从 0 开始到 2^48-1。达到上限后，下一个值就是 0，然后继续循环。这就会导致，当达到上限后，新的数据会覆盖旧的数据。

6，SELECT 语句顺序

关键字顺序不能颠倒：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

执行顺序：

FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

7，WHERE 子句

7.1，比较运算符

其中 BETWEEN ... AND ... 可以取到两个值的边界。

另外，在 MySQL 中空字符串 "" 与 NULL 是不同的：

• 空字符串代表有值，但是空字符；可以使用比较运算符进行比较。
• NULL 代表无值，什么都没有，未定义；只能使用 IS NULL 来筛选。

7.2，逻辑运算符

逻辑运算符用于连接多个 where 子句：

7.3，like 语句

like 语句用于模糊查询，like 根据 % 位置的不同，对索引有不同的影响：

• like '%abc%' 和 like '%abc' 都无法使用索引，是全表扫描
• like 'abc%' 可以使用索引

8，DISTINCT 去重

在 MySQL 中使用 distinct 去重时，distinct 的必须写在所有列的前面，也就是紧跟 select 关键字之后：

-- 对 a 列进行去重 select distinct a from table_name;

也可以对多列进行去重：

select distinct a, b, c from table_name;

此时 MySQL 会将 a,b,c 作为一个联合字段进行统一去重，而不是分别对三个字段进行了去重。

9，关于 COUNT(*) 操作

对于 count(*)，不同的 MySQL 引擎，有不同的实现方式：

• MyISAM 引擎：把一个表的总行数存在了磁盘上，执行 count(*) 的时候会直接返回这个数，效率很高；
  • 如果带了 where 过滤条件，就没有这么快了
  • 不支持事务

• InnoDB 引擎：它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

为什么 InnoDB 不向 MyISAM 一样将行数直接记录下来呢？

这是因为，即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC） 的原因，InnoDB 表“应该返回多少行”也是不确定的。

这和 InnoDB 的事务有关，可重复读是它默认的隔离级别，这是用 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count(*) 请求，InnoDB 只能把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

使用命令 show table status like 'tbname' 得到的结果中也有一个行数（rows），注意这个行数是不准确的，这是一个采样估算值，官方文档说误差可能达到 40% 到 50%。

几种不同的 count 用法

• count(*)：总行数
• count(1)：总行数
• count(主键id)：总行数
• count(字段名)：字段部位 NULL 的行数

性能差异：

# count(*) 是经过优化的，性能最好 count(字段名) < count(主键id) < count(1) ≈ count(*)

10，MYSQL 函数

MYSQL 函数包括内置函数和自定义函数。

内置函数可分为 5 大类：

• 算术函数
  • ABS(n)：取绝对值
  • MOD(m， n)：取余，m % n
  • ROUND(字段名称，n)：四舍五入 n 个小数位
• 字符串函数
  • CONCAT()：字符串连接
  • LENGTH()：字符长度（一个汉字3 个字符长）
  • CHAR_LENGTH()：字符串长度，汉字、数字、字母都算一个字符
  • LOWER()：字符串小写
  • UPPER()：字符串大写
  • REPLACE()：字符串替换
  • SUBSTRING()：字符串截取
• 日期函数
  • CURRENT_DATE()：当前日期
  • CURRENT_TIME()：当前时间
  • CURRENT_TIMESTAMP()：当前日期 + 时间
  • DATE()：时间的日期部分
  • YEAR()：年份
  • MONTH()：月份
  • DAY()：天
  • HOUR()：小时
  • MINUTE()：分钟
  • SECOND()：秒
• 聚集函数
  • COUNT()：总行数
  • MAX()：最大值，会忽略 NULL
  • MIN()：最小值，会忽略 NULL
  • SUM()：求和，会忽略 NULL
  • AVG()：平均值，会忽略 NULL

COUNT(*) 与 COUNT(字段名)的区别：

-- 统计所有 a 大于 100 的行 select count(*) from table_name where a > 100; -- 统计所有 a 大于 100 且 b 不为 NULL 的行 select count(b) from table_name where a > 100;

11，GROUP BY 数据分组

数据分组使用 GROUP BY 子句，需要注意 NULL 值也会被分成一组。

-- 用字段 a 进行分组，并统计每个不同值的数量 select count(*), a from table_name group by a;

也可以对多个字段进行分组：

-- 会把 a，b 两个字段的所有取值情况都进行分组 select count(*) as num, a, b from table_name group by a, b order by num;

对分组过滤 having

WHERE 子句作用于数据行，having 子句作用域分组。

-- 筛选出 num 大于 5 的分组 select count(*) as num, a, b from table_name group by a, b having num > 5 order by num;

这里如果将 having 换成 where，则会出错。

-- 这个子句更加复杂 -- 会先筛选出所有 a > 10 的行，然后再进行分组，过滤，排序 select count(*) as num, a, b from table_name where a > 10 group by a, b having num > 5 order by num;

12，子查询(嵌套查询)

会用到下面几个关键字：

• EXISTS：判断是否存在
• NOT EXISTS：
• IN：判断是否在集合中
• NOT IN：
• SOME / ANY：与子查询返回的任何值做比较，需要与比较运算符一起使用
• ALL：与子查询返回的所有值做比较，需要与比较运算符一起使用

子查询分为关联子查询与非关联子查询：

• 关联子查询：查询子句会执行多次。
• 非关联子查询：查询子句只执行一次。

-- 非关联子查询 SELECT player_name, height FROM player WHERE height = ( SELECT MAX(height) FROM player -- 关联子查询 -- 查找每个球队中大于平均身高的球员有哪些，并显示他们的球员姓名、身高以及所在球队 ID。 SELECT player_name, height, team_id FROM player a WHERE height > ( SELECT AVG(height) FROM player b WHERE a.team_id = b.team_id

EXIST 与 IN 的使用模式：

-- 下面两种含义一样 SELECT * FROM A WHERE cc IN (SELECT cc FROM B) -- B 表小时，用 IN 效率高 SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc) -- A 表小时，用 Exist 效率高

ANY 与 ALL 子句，必须与一个比较操作符一起使用：

SELECT player_id, player_name, height FROM player WHERE height > ANY ( SELECT height FROM player WHERE team_id = 1002 SELECT player_id, player_name, height FROM player WHERE height > ALL ( SELECT height FROM player WHERE team_id = 1002

13，JOIN 连接查询

根据不同的SQL 标准，连接查询是不同的，主要有 SQL92 和 SQL99 两种。

SQL 中的 5 中连接方式：

• 等值连接：连接多个表的条件是等号
• 非等值连接：连接多个表的条件不是等号
• 外连接
  • 左外连接：LEFT JOIN … ON …
  • 右外连接：RIGHT JOIN … ON …
  • 全外连接：FULL JOIN … ON … （MYSQL 不支持）

AS 为表名重命名是 SQL92 语法，SQL99 中不需要用 AS，使用空格就行。 LEFT JOIN … ON 是 SQL99 语法，SQL92 中没有该语法。 LEFT 与 RIGHT 表示主表在哪边。

SQL 举例：

-- 笛卡尔积 SQL92 语法 SELECT * FROM player, team; -- 笛卡尔积 SQL99 语法 SELECT * FROM player CROSS JOIN team; SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3; -- 三张表笛卡尔积 -- 等值连接 SQL92 语法 SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id; -- 等值连接 SQL99 语法 SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team -- 非等值连接 SQL92 语法 SELECT p.player_name, p.height, h.height_level FROM player AS p, height_grades AS h WHERE p.height BETWEEN h.height_lowest AND h.height_highest; -- 非等值连接 SQL99 语法 SELECT p.player_name, p.height, h.height_level FROM player as p JOIN height_grades as h ON height BETWEEN h.height_lowest AND h.height_highest -- 外连接 SQL99 语法 SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id; -- SQL99 USING 连接，USING 用于指定两张表中的同名字段 SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING (team_id) -- 这两个 SQL 含义相同 SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id

14，VIEW 视图

在 MySQL 里，有两个“视图”的概念：

• 一个是 view。它是一个用查询语句定义的虚拟表，是对 select 语句的封装，本身不具有数据；在调用的时候执行查询语句并生成结果。
  • 创建视图的语法是 create view … ，而它的查询方法与表一样。
• 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持读提交和可重复读隔离级别的实现。
  • 它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

创建视图：

CREATE VIEW view_name AS SELECT column1, column2 FROM table WHERE condition

当视图创建之后，它就相当于一个虚拟表，可以直接使用：

SELECT * FROM view_name;

更新视图（更改一个已有的视图）：

ALTER VIEW view_name AS SELECT column1, column2 FROM table WHERE condition

删除视图：

DROP VIEW view_name

15，存储过程

存储过程是对 SQL 语句的封装，是程序化的SQL，也就是将 SQL 写成函数（过程）的形式。存储过程由 SQL 语句和流控制语句共同组成。

定义一个存储过程：

-- 创建存储过程 CREATE PROCEDURE 存储过程名称([参数列表]) BEGIN 需要执行的语句 -- 修改存储过程 ALTER PROCEDURE -- 删除存储过程 DROP PROCEDURE

DELIMITER // CREATE PROCEDURE `add_num`(IN n INT) BEGIN DECLARE i INT; DECLARE sum INT; SET i = 1; SET sum = 0; WHILE i <= n DO SET sum = sum + i; SET i = i +1; END WHILE; SELECT sum; END // DELIMITER ; -- 调用存储过程 CALL add_num(50);

其中 DELIMITER // 用于定义结束符，在存储过程结束后，要还原 DELIMITER ;。

SET 用于对变量进行赋值。

注意在使用 Navicat 时，不需要使用 DELIMITER。

参数修饰符，IN OUT INOUT 的含义：

CREATE PROCEDURE `get_hero_scores`( OUT max_max_hp FLOAT, OUT min_max_mp FLOAT, OUT avg_max_attack FLOAT, s VARCHAR(255) BEGIN SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack; -- 调用存储过程 CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士'); SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

其中 SELECT ... INTO 是将查询结果为变量赋值。

存储过程的缺点

• 可移植性差
• 调试困难，不易维护

16，临时表

临时表不同于视图（虚拟表），临时表示真实存在的数据表，只是不会长期存在，它只为当前连接存在，连接关闭后，临时表就释放了。

临时表与内存表的区别：

• 内存表：特指用 Memory 引擎的表
  • 建表语法是 create table … engine=memory
  • 这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在
• 临时表：可以使用各种引擎类型
  • 如果使用 InnoDB / MyISAM 引擎，写数据的时候是写到磁盘上的
  • 临时表也可以使用 Memory 引擎
  • 建表语法是 create temporary table …

一个创建临时表的事例：

临时表的特征：

• 一个临时表只能被创建它的 session 访问，对其他线程不可见。（所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的）
  • 由于临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表
• 临时表可以与普通表同名
• session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表
• show tables 命令不显示临时表

在实际应用中，临时表一般用于处理比较复杂的计算逻辑。

上面介绍到的临时表都是用户自己创建的，也称为用户临时表。

17，MySQL 权限管理

创建用户命令：

create user 'ua'@'%' identified by 'pa';

这条语句的逻辑是，创建一个用户 'ua'@'%'，密码是 pa。

在 MySQL 里面，用户名 (user)+ 地址 (host) 才表示一个用户，因此 ua@ip1 和 ua@ip2 代表的是两个不同的用户。

这条命令做了两个动作：

• 磁盘上：往 mysql.user 表里插入一行，由于没有指定权限，所以这行数据上所有表示权限的字段的值都是 N；
• 内存里：往数组 acl_users 里插入一个 acl_user 对象，这个对象的 access 字段值为 0。

下图是此时用户 ua 在 user 表中的状态：

在 MySQL 中，用户权限是有不同的范围的：

• 表权限和列权限

17.1，全局权限

全局权限作用于整个 MySQL 实例，这些权限信息保存在 mysql 库的 user 表里。

要给用户 ua 赋一个最高权限的话，语句是这么写的：

grant all privileges on *.* to 'ua'@'%' with grant option;

这个 grant 命令做了两个动作：

• 磁盘上：将 mysql.user 表里，用户 ’ua’@’%' 这一行的所有表示权限的字段的值都修改为‘Y’；
• 内存里：从数组 acl_users 中找到这个用户对应的对象，将 access 值（权限位）修改为二进制的“全 1”。

注意：一般在生产环境上要合理控制用户权限的范围。如果一个用户有所有权限，一般就不应该设置为所有 IP 地址都可以访问。

revoke 命令用于回收权限：

revoke all privileges on *.* from 'ua'@'%';

revoke 命令做了如下两个动作：

• 磁盘上：将 mysql.user 表里，用户’ua’@’%‘这一行的所有表示权限的字段的值都修改为“N”；
• 内存里：从数组 acl_users 中找到这个用户对应的对象，将 access 的值修改为 0。

17.2，库级权限

如果要让用户 ua 拥有库 db1 的所有权限，可以执行下面这条命令：

grant all privileges on db1.* to 'ua'@'%' with grant option;

基于库的权限记录保存在 mysql.db 表中，在内存里则保存在数组 acl_dbs 中。

这条 grant 命令做了如下两个动作：

• 磁盘上：往 mysql.db 表中插入了一行记录，所有权限位字段设置为“Y”；
• 内存里：增加一个对象到数组 acl_dbs 中，这个对象的权限位为“全 1”。

下图是此时用户 ua 在 db 表中的状态：

每次需要判断一个用户对一个数据库读写权限的时候，都需要遍历一次 acl_dbs 数组，根据 user、host 和 db 找到匹配的对象，然后根据对象的权限位来判断。

17.3，表权限和列权限

MySQL 支持更细粒度的表权限和列权限：

• 表权限定义存放在表 mysql.tables_priv 中
• 列权限定义存放在表 mysql.columns_priv 中
• 这两类权限，组合起来存放在内存的 hash 结构 column_priv_hash 中

# 创建一个表 create table db1.t1(id int, a int); # 赋予表权限 grant all privileges on db1.t1 to 'ua'@'%' with grant option; # 赋予列权限 GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;

这两个权限每次 grant 的时候都会修改数据表，也会同步修改内存中的 hash 结构。

18，Python 操作 MySQL 的库

• mysql-connector
• MySQLdb
• mysqlclient
• PyMySQL
• peewee：一个轻量级的 ORM 框架
• SQLAIchemy：一个 ORM 框架