MySQL进阶—从零到入土

1. 存储引擎

1). 建表时指定存储引擎

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

2). 查询当前数据库支持的存储引擎

show engines;

A. 查询建表语句 --- 默认存储引擎 : InnoDB

show create table account;

-- 创建表 my_myisam , 并指定MyISAM存储引擎
create table my_myisam(
id int,
name varchar(10)
) engine = MyISAM ;

-- 创建表 my_memory , 指定Memory存储引擎
create table my_memory(
id int,
name varchar(10)
) engine = Memory ;

存储引擎特点

InnoDB 引擎与 MyISAM 引擎的区别 ?

① . InnoDB 引擎 , 支持事务 , 而 MyISAM 不支持。

② . InnoDB 引擎 , 支持行锁和表锁 , 而 MyISAM 仅支持表锁 , 不支持行锁。

③ . InnoDB 引擎 , 支持外键 , 而 MyISAM 是不支持的。

 存储引擎选择

• InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。  

2. 索引

2.1 索引概述

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引的优缺点

2.2 索引结构

2.2.1 概述

MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构

的支持情况。  

  注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。 

2.2.2 B-Tree 

B-Tree ， B 树是一种多叉路衡查找树，相对于二叉树， B 树每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为 5(5 阶 ) 的 b-tree 为例，那这个 B 树每个节点最多存储 4 个 key，5个指针：

2.2.3 B+Tree

B+Tree 是 B-Tree 的变种，我一颗最大度数（ max-degree ）为 4 （ 4 阶）的 b+tree为例，其结构示意图：

绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。

红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

B+Tree 与 B-Tree 相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree ，提高区间访问的性能，利于排序。

2.2.4 Hash

• Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）
• 无法利用索引完成排序操作
•  查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

为什么 InnoDB 存储引擎选择使用 B+tree 索引结构 ?

A. 相对于二叉树，层级更少，搜索效率高；

B. 对于 B-tree ，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储

的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

C. 相对 Hash 索引， B+tree 支持范围匹配及排序操作；

2.3 索引分类

2.3.1 索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

2.3.2 聚集索引&二级索引

而在在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则 :

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

 聚集索引和二级索引的具体结构如下：

• 聚集索引的叶子节点下挂的是这一行的数据 。
• 二级索引的叶子节点下挂的是该字段值对应的主键值。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

以下两条 SQL 语句，那个执行效率高 ? 为什么 ?

A. select * from user where id = 10 ;

B. select * from user where name = 'Arm' ;

备注 : id 为主键， name 字段创建的有索引；

解答： A 语句的执行性能要高于 B 语句。

因为 A 语句直接走聚集索引，直接返回数据。 而 B 语句需要先查询 name 字段的二级索引，然

后再查询聚集索引，也就是需要进行回表查询。

InnoDB 主键索引的 B+tree 高度为多高呢 ?

假设 : 一行数据大小为1k ，一页中可以存储 16 行这样的数据。 InnoDB 的指针占用 6 个字节的空间，主键即使为bigint ，占用字节数为 8 。

高度为2 ：

        n * 8 + (n + 1) * 6 = 16*1024 , 算出 n 约为 1170

        1171* 16 = 18736

也就是说，如果树的高度为 2 ，则可以存储 18000 多条记录。

高度为3 ：

        1171 * 1171 * 16 = 21939856

也就是说，如果树的高度为 3 ，则可以存储 2200w 左右的记录。

2.4 索引语法

 1). 创建索引

create [ uniqe | felltext ] index index_name on table_name (index_col_name,... ) ;

2). 查看索引

show index from table_name ;

3). 删除索引

drop index index_name ON table_name ;

2.5 SQL性能分析

--查看MySQL服务器配置参数
show variables;

2.5.1 SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的insert、update、delete、select的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
show global status like 'Com_______';

Com_delete: 删除次数

Com_insert: 插入次数

Com_select: 查询次数

Com_update: 更新次数

2.5.2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认 10 秒）的所有

SQL 语句的日志。

MySQL 的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log 。

--慢查询日志的开关查询
show variables like 'slow_query_log';

如果要开启慢查询日志，需要在MySQL 的配置文件（ /etc/my.cnf ）中配置如下信息

vi /etc/my.cnf
-- 按i到最后添加下面语句

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

-- 重启数据库服务器
systemctl restart mysql

2.5.3 profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过 have_profiling 参数，能够看到当前MySQL是否支持profile操作：

select @@have_profiling ;

显示 YES，代表当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句 session/global 级别开启profiling：

-- 查看profile操作是否开着
select @@profiling;

-- 打开
set profiling = 1;

 执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

-- 查看每一条SQL的耗时基本情况
show profiles;

-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

2.5.4 explain

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

语法 :

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

desc select * from tb_user WHERE id =1;

2.6 索引使用

2.6.1 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

在 上面  tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为： profession ， age， status 。

explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';

当执行 SQL 语句 : explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程 ' ； 时，是否满足最左前缀法则，走不走上述的联合索引，索引长度？

可以看到，是完全满足最左前缀法则的，索引长度 54 ，联合索引是生效的。

注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段 ( 即是第一个字段) 必须存在，与我们编写 SQL 时，条件编写的先后顺序无关。

2.6.2 范围查询

联合索引中，出现范围查询 (>,<) ，范围查询右侧的列索引失效。

explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';

• 当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为49，就说明范围查询右边的status字段是没有走索引的。

• 当范围查询使用>= 或 <= 时，走联合索引了，但是索引的长度为54，就说明所有的字段都是走索引的。

2.6.3 索引失效情况

• 不要在索引列上进行运算操作， 索引将失效。

-- 当根据phone字段进行等值匹配查询时, 索引生效。
explain select * from tb_user where phone = '17799990015';
-- 当根据phone字段进行函数运算操作之后，索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';

• 字符串类型字段使用时，不加引号，索引将失效。

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;

•  如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

explain select * from tb_user where profession like '软件%';

explain select * from tb_user where profession like '%工程';

explain select * from tb_user where profession like '%工%';

在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，索引将会失效。

• 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

explain select * from tb_user where id = 10 or age = 23;

explain select * from tb_user where phone = '17799990017' or age = 23;

• 如果MySQL评估使用索引比全表更慢，则不使用索引。  

select * from tb_user where phone >= '17799990005';

select * from tb_user where phone >= '17799990015';

is null 、is not null是否走索引，得具体情况具体分析，并不是固定的

2.6.4 SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

1). use index ： 建议 MySQL 使用哪一个索引完成此次查询（仅仅是建议， mysql 内部还会再次进

行评估）。

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

2). ignore index ： 忽略指定的索引。

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

3). force index ： 强制使用索引。

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

2.6.5 覆盖索引

尽量使用覆盖索引，减少select  *。

覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:

select id,username,password from tb_user where username = 'itcast';

答案 : 针对于 username, password 建立联合索引 , sql 为 : create index idx_user_name_pass on tb_user(username,password);

这样可以避免上述的 SQL 语句，在查询的过程中，出现回表查询。

2.6.6 前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

1). 语法

create index idx_xxxx on 表名(列名(n)); 

2). 前缀长度、

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

2.6.7 单列索引与联合索引

单列索引：即一个索引只包含单个列。

联合索引：即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

2.7 索引设计原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。

2). 针对于常作为查询条件（ where ）、排序（ order by ）、分组（ goup b ）操作的字段建立索引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，影响增删改的效率。

7). 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

3. SQL优化

3.1 插入数据

3.1.1 insert

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

1). 优化方案一

-- 批量插入数据
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

2). 优化方案二

-- 手动控制事务
start transaction;

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');

commit;

3). 优化方案三

-- 主键顺序插入，性能要高于乱序插入。
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

3.1.2 大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

-- 客户端连接服务端时，加上参数 -–local-infile
mysql --local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/home/mysql/桌面/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;

select count(*) from tb_user;

3.2 主键优化

1). 数据组织方式

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表 (index organized table IOT)。

2). 页分裂

页可以为空，也可以填充一半，也可以填充 100% 。每个页包含了 2-N 行数据 ( 如果一行数据过大，会行溢出) ，根据主键排列。

A. 主键顺序插入效果

① . 从磁盘中申请页， 主键顺序插入

B. 主键乱序插入效果 

3). 页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（ flaged）为删除并且它的空间变得允许被其他记录声明使用。 当页中删除的记录达到 MERGE_THRESHOLD （默认为页的 50% ）， InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

4). 索引设计原则

• 满足业务需求的情况下，尽量降低主键的长度。
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

• 尽量不要使用 uuid 做主键或者是其他自然主键，如身份证号。
• 业务操作时，避免对主键的修改。

3.3 order by优化 

MySQL 的排序，有两种方式：

using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index ，不需要 额外排序，操作效率高。

对于以上的两种排序方式， using index 的性能高，而 using filesort 的性能低，我们在优化排序

操作时，尽量要优化为 using index 。

order by 优化原则 :

1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
2. 尽量使用覆盖索引。
3. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规（ASC/DESC）。
4. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

3.4 group by优化

在分组操作中，我们需要通过以下两点进行优化，以提升性能：

1. 在分组操作时，可以通过索引来提高效率。
2. 分组操作时，索引的使用也是满足最左前缀法则的。

3.5 limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路 : 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

3.6 count优化

3.6.1 概述

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。
• InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

3.6.2 count用法

用法：count（*）、count（主键）、count（字段）、count（数字）

按照效率排序的话， count( 字段 ) < count( 主键 id) < count(1) ≈ count(*) ，所以尽量使用 count(*) 。

3.7 update优化

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁 , 并且该索引不能失效，否则会从行锁

升级为表锁。

4. 视图/存储过程/触发器

4.1 视图

4.1.1 介绍

视图（ View ）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的 SQL 逻辑，不保存查询结果。所以我们在创建视图的时候，主要工作就落在创建这条SQL 查询语句上。

4.1.2 语法

1). 创建

create [or replace] view 视图名称[(列名列表)] as select语句 [ with [ cascaded | local ] check option ]

2). 查询

-- 查看创建视图语句
show create view 视图名称;
-- 查看视图数据
select * from 视图名称 ...... ;

3). 修改

-- 方式一：
create or replace view 视图名称[(列名列表)] as select语句 [ with [ cascaded | local ] check option ]
-- 方式二：
alter view 视图名称[(列名列表)] as select语句 [ with [ cascaded | local ] check option ];

4). 删除

drop view [if exists] 视图名称 [,视图名称] ...

4.1.3 检查选项

当使用 WITH CHECK OPTION 子句创建视图时， MySQL 会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。 MySQL 允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： cascaded 和 local，默认值为 cascaded 。

1). cascaded(级联)

v2 视图是基于 v1 视图的，如果在 v2 视图创建的时候指定了检查选项为 cascaded ，但是 v1 视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2 ，还会级联检查 v2 的关联视图 v1 。

create or replace view 视图名称[(列名列表)] as select语句 with cascaded check option;

2).local(本地)

v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1。

create or replace view 视图名称[(列名列表)] as select语句 with local check option;

4.1.4 视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。

如果视图包含以下任何一项，则该视图不可更新：

• 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
• DISTINCT
• GROUP BY
• HAVING
• UNION 或者 UNION ALL

create view stu_v_count as select count(*) from student;
insert into stu_v_count values(10);
-- 插入失败，不是一一对应

4.1.5 视图作用

1). 简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2). 安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

3). 数据独立

视图可帮助用户屏蔽真实表结构变化带来的影响。

4.1.6 案例

1). 为了保证数据库表的安全性，开发人员在操作 tb_user 表时，只能看到的用户的基本字段，屏蔽

手机号和邮箱两个字段。

create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user;
select * from tb_user_view;

2). 查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操 作，定义一个视图。

create view tb_stu_course_view as select s.name student_name , s.no student_no,c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;
select * from tb_stu_course_view;

4.2 存储过程

4.2.1 介绍

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

4.2.2 基本语法

1). 创建

create procedure 过程名称 ([ 参数列表 ])
begin
-- SQL语句
end;

2). 调用

call 名称 ([ 参数 ]);

3). 查看

-- 查询指定数据库的存储过程及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'xxx'; 
-- 查询某个存储过程的定义
show create procedure 存储过程名称; 

4). 删除

drop procedure [if exists] 存储过程名称 ；

-- 注意: 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符
delimiter $$

4.2.3 变量

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。

4.2.3.1 系统变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

1). 查看系统变量

 -- 查看所有系统变量
show [ session| global] variables;
-- 可以通过LIKE模糊匹配方式查找变量
show [ session| global] variables like '......'; 
-- 查看指定变量的值
select @@[session| global] 系统变量名; 

2). 设置系统变量

set [ session | global] 系统变量名 = 值 ;
set @@[ session| global]系统变量名 = 值 ;

-- 查看系统变量
show session variables ;
show session variables like 'auto%';
show global variables like 'auto%';
select @@global.autocommit;
select @@session.autocommit;
-- 设置系统变量
set session autocommit = 1;
insert into course(id, name) VALUES (6, 'ES');
set global autocommit = 0;
select @@global.autocommit;

4.2.3.2 用户定义变量

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

1). 赋值

set @var_name = expr [, @var_name = expr] ... ;
set @var_name := expr [, @var_name := expr] ... ;

-- 赋值时，可以使用 = ，也可以使用 :=

select @var_name := expr [, @var_name := expr] ... ;
select 字段名 into @var_name FROM 表名;

2). 使用

select @var_name;

注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
-- 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;
select @abc;

4.2.3.3 局部变量

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

1). 声明

declare变量名 变量类型 [default... ] ;

2). 赋值

set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名 ...;

4.2.4 if

1). 介绍

if 用于做条件判断，具体的语法结构为：

if 条件1 then
.....
elseif 条件2 then -- 可选
.....
else-- 可选
.....
end if;

create procedure p3()
begin
    declare score int default 58;
    declare result varchar(10);
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
        select result;
end;

call p3();

4.2.5 参数

1). 介绍

参数的类型，主要分为以下三种：IN 、 OUT 、 INOUT 。 具体的含义如下：

create procedure 存储过程名称 ([ in/out/inout 参数名 参数类型 ])
begin
    -- SQL语句
end;

create procedure p4(in score int, out result varchar(10))
begin
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;

-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);

select @result;

-- 将传入的200分制的分数，进行换算，换算成百分制，然后返回。
create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;
set @score = 198;
call p5(@score);
select @score;

4.2.6 case

1). 介绍

case结构及作用，和我们在基础篇中所讲解的流程控制函数很类似。有两种语法格式：

语法1：

-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，
执行statement_list2， 否则就执行 statement_list
case case_value
    when when_value1 then statement_list1
    when when_value2 then statement_list2 ...
    else statement_list
end case;

语法 2 ：

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
立时，执行statement_list2， 否则就执行 statement_list
case
    when search_condition1 then statement_list1
    when search_condition2 then statement_list2 ...
    else statement_list
end case;

create procedure p6(in month int)
begin
    declare result varchar(10);
    case
        when month >= 1 and month <= 3 then
            set result := '第一季度';
        when month >= 4 and month <= 6 then
            set result := '第二季度';
        when month >= 7 and month <= 9 then
            set result := '第三季度';
        when month >= 10 and month <= 12 then
            set result := '第四季度';
        else
            set result := '非法参数';
    end case;    
    select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);
end;

4.2.7 while

1). 介绍

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
    SQL逻辑...
end while; 

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环
create procedure p7(in n int)
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;
        set n := n - 1;
    end while;
    select total;
end;
call p7(100);

4.2.8 repeat

1). 介绍

repeat 是有条件的循环控制语句 , 当满足 until 声明的条件的时候，则退出循环 。具体语法为：

-- 先执行一次逻辑，然后判定until 条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
    SQL逻辑...
    until 条件
end repeat;

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
create procedure p8(in n int)
begin
    declare total int default 0;
    repeat
        set total := total + n;
        set n := n - 1;
    until n <= 0
    end repeat;
select total;
end;

call p8(100);

4.2.9 loop

1). 介绍

LOOP 实现简单的循环，如果不在 SQL 逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用：

• LEAVE ：配合循环使用，退出循环。
• ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] loop
    SQL逻辑...
end loop [end_label];

leave label; -- 退出指定标记的循环体
iterate label; -- 直接进入下一次循环

上述语法中出现的 begin_label，end_label，label 指的都是我们所自定义的标记。

create procedure p9(in n int)
begin
    declare total int default 0;
    sum:loop
        if n<=0 then
            leave sum;
        end if;

        set total := total + n;
        set n := n - 1;
    end loop sum;
    select total;
end;
call p9(100);

create procedure p10(in n int)
begin
    declare total int default 0;
    sum:loop
        if n<=0 then
            -- 退出sum循环
            leave sum;
        end if;
        if n%2 = 1 then
            set n := n - 1;
            -- 直接进入下一次循环
            iterate sum;
        end if;
            set total := total + n;
            set n := n - 1;
    end loop sum;
    select total;
end;
call p10(100);

4.2.10 游标

1). 介绍

游标（ CURSOR ）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN 、 FETCH 和 CLOSE ，其语法分别如下。

A. 声明游标

declare 游标名称 cursor for 查询语句;

B. 打开游标

open 游标名称;

C. 获取游标记录

fetch游标名称 into 变量 [, 变量 ];

D. 关闭游标

close 游标名称;

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;
    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    open u_cursor;
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values (null, uname, upro);
    end while;
    close u_cursor;
end;
call p11(30);

上述的存储过程，最终在调用的过程中，会报错，之所以报错是因为上面的 while循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。（需要通过 MySQL 中提供的 条件处理程序 Handler 来解决。 ）

4.2.11 条件处理程序

1). 介绍

条件处理程序（ Handler ）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体

语法为：

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;

handler_action 的取值：
    CONTINUE: 继续执行当前程序
    EXIT: 终止执行当前程序
condition_value 的取值：
    SQLSTATE sqlstate_value: 状态码，如 02000
    SQLWARNING: 所有以01开头的SQLSTATE代码的简写
    NOT FOUND: 所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
declare exit handler for SQLSTATE '02000' close u_cursor;

4.3 存储函数

1). 介绍

存储函数是有返回值的存储过程，存储函数的参数只能是 IN 类型的。具体语法如下：

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
    RETURNS type [characteristic ...]
BEGIN
    -- SQL语句
    RETURN ...;
END ;

characteristic 说明：

• DETERMINISTIC：相同的输入参数总是产生相同的结果
• NO SQL ：不包含 SQL 语句。
• READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;
        set n := n - 1;
    end while;
    return total;
end;
select fun1(50);

4.4 触发器

4.4.1 介绍

触发器是与表有关的数据库对象，指在 insert/update/delete 之前 (before) 或之后 (after)，触        发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在发器还只支持行级触发，不支持语句级触发。 ​​​​​​​​​​​​​​

4.4.2 语法

1). 创建

create table trigger_name
before/after insert/update/delete
on tbl_name for each row -- 行级触发器
begin
    trigger_stmt ;
end;

2). 查看

show TRIGGERS ;

3). 删除

drop taigger [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数
据库 。

4.4.3 案例

通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中, 包含增加, 修改 , 删除 ;

-- 准备工作 : 日志表 user_logs
create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作的ID',
    operate_params varchar(500) comment '操作参数',
    primary key(`id`)
)engine=innodb default charset=utf8;

A. 插入数据触发器

create trigger tb_user_insert_trigger
    after insert on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
    (null, 'insert', now(), new.id, concat('插入的数据内容为:id=',new.id,',name=',new.name,', phone=', NEW.phone, ', email=', NEW.email,',profession=', NEW.profession));
end;

-- 查看
show triggers ;
-- 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());

B. 修改数据触发器​​​​​​​

create trigger tb_user_update_trigger
    after update on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
    (null, 'update', now(), new.id,
        concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=',old.phone, ',email=', old.email, ', profession=', old.profession,
             '| 更新之后的数据: id=',new.id,',name=',new.name, ', phone=',NEW.phone, ', email=',NEW.email, ', profession=', NEW.profession));
end;

-- 查看
show triggers ;
-- 更新
update tb_user set profession = '会计' where id = 23;
update tb_user set profession = '会计' where id <= 5;

C. 删除数据触发器 ​​​​​​​

create trigger tb_user_delete_trigger
    after delete on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
    (null, 'delete', now(), old.id,
        concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=',old.phone, ', email=', old.email, ', profession=', old.profession));
end;

-- 查看
show triggers ;
-- 删除数据
delete from tb_user where id = 26;

5. 锁

5.1 概述

MySQL 中的锁，按照锁的粒度分，分为以下三类：

全局锁：锁定数据库中的所有表。

表级锁：每次操作锁住整张表。

行级锁：每次操作锁住对应的行数据。

5.2 全局锁

5.2.1 介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 DML 的写语句， DDL 语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

5.2.2 语法

1). 加全局锁 ​​​​​​​

flush tables with read lock;

2). 数据备份

mysqldump -uroot –p1234 itcast > itcast.sql

3). 释放锁​​​​​​​

unlock tables;

5.2.3 特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（ binlog ），会导致主从延迟。

在 InnoDB 引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

5.3 表级锁

5.3.1 介绍

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在 MyISAM 、InnoDB、 BDB 等存储引擎中。

对于表级锁，主要分为以下三类：

• 表锁
• 元数据锁（meta data lock，MDL）
• 意向锁

5.3.2 表锁

对于表锁，分为两类：

• 表共享读锁（read lock）
• 表独占写锁（write lock）

语法： ​​​​​​​

加锁：

lock tables 表名 read/write

释放锁：

unlock tables;

结论 : 读锁不会阻塞其他客户端的读，但是会阻塞写。

          写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

5.3.3 元数据锁

meta data lock , 元数据锁，简写 MDL 。

MDL 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。 MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免 DML 与 DDL 冲突，保证读写的正确性。

这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

5.3.4 意向锁

1). 介绍

为了避免 DML 在执行时，加的行锁与表锁的冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

意向共享锁(IS):

select ... lock in share mode

与表锁共享锁 (read)兼容，与表锁排他锁 (write) 互斥。

意向排他锁(IX): ​​​​​​​

insert、update、delete、select...for update

与表锁共 享锁(read) 及排他锁 (write) 都互斥，意向锁之间不会互斥

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

​​​​​​5.4 行级锁

5.4.1 介绍

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。 ​​​​​​​

5.4.2 行锁

1). 介绍

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
• 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

 两种行锁的兼容情况如下:

 常见的SQL语句，在执行时，所加的行锁如下：

5.4.3 间隙锁&临键锁

默认情况下， InnoDB 在 REPEATABLE READ 事务隔离级别运行， InnoDB 使用 next-key 锁进行搜

索和索引扫描，以防止幻读。

• 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。
• 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
• 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。 ​​​​​​​ 

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会

阻止另一个事务在同一间隙上采用间隙锁。

6. InnoDB引擎

6.1 逻辑存储结构

1). 表空间

表空间是InnoDB存储引擎逻辑结构的最高层。每张表都会有一个表空间（xxx.ibd）

一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

2). 段

 段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的 非叶子节点。段用来管理多个Extent（区）。

3). 区

区，表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一 个区中一共有64个连续的页。 ​​​​​​​

4). 页

页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB 。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

 5). 行

行，InnoDB 存储引擎数据是按行进行存放的。

在行中，默认有两个隐藏字段：

• Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
• Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

6.3 事务原理

6.3.1 事务基础

1). 事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

 2). 特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。 而持久性是通过数据库的锁，加上MVCC来保证的。

重做日志(redo log)，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

回滚日志(undo log)，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和MVCC(多版本并发控制) 。  

7. MySQL管理

7.1 系统数据库

Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下：

 7.2 常用工具

7.2.1 mysql

该mysql不是指mysql服务，而是指mysql的客户端工具。

语法：
mysql [options] [database]
选项：
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
-e, --execute=name #执行SQL语句并退出

7.2.2 mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。
​​​​​​​

通过帮助文档查看选项：
mysqladmin --help

语法:
mysqladmin [options] command ...
选项:
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口

7.2.3 mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。 ​​​​​​​

语法 ：
mysqlbinlog [options] log-files1 log-files2 ...
选项 ：
-d, --database=name 指定数据库名称，只列出指定的数据库相关操作。
-o, --offset=# 忽略掉日志中的前n行命令。
-r,--result-file=name 将输出的文本格式日志输出到指定文件。
-s, --short-form 显示简单格式， 省略掉一些信息。
--start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。
--start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。

​​​​​​​7.2.4 mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

语法 ：
mysqlshow [options] [db_name [table_name [col_name]]]
选项 ：
--count 显示数据库及表的统计信息（数据库，表 均可以不指定）
-i 显示指定数据库或者指定表的状态信息
示例：
#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count
#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count

7.2.5 mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。​​​​​​​

语法 ：
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A
连接选项 ：
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器ip或域名
-P, --port=# 指定连接端口
输出选项：
--add-drop-database 在每个数据库创建语句前加上 drop database 语句
--add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
-n, --no-create-db 不包含数据库的创建语句
-t, --no-create-info 不包含数据表的创建语句
-d --no-data 不包含数据
-T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

7.2.6 mysqlimport/source

1). mysqlimport

mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。

语法 ：
mysqlimport [options] db_name textfile1 [textfile2...]
示例 ：
mysqlimport -uroot -p2143 test /tmp/city.txt

 2). source ​​​​​​​

语法 ：
source /root/xxxxx.sql