通过47 张图带你 MySQL 进阶

我们在 MySQL 入门篇主要介绍了根本的 SQL 命令、数据类型和函数，在部分以上知识后，你就可以停止 MySQL 的开发工作了，但是假如要成为一个及格的开发人员，你还要具备一些更高级的技艺，下面我们就来商量一下 MySQL 都需要哪些高级的技艺

MySQL 储备引擎

储备引擎概述

数据库最中心的一点就是用来储备数据，数据储备就幸免不了和磁盘打交道。那么数据以哪种方式停止储备，怎样储备是储备的关键所在。所以储备引擎就相当于是数据储备的发动机，来驱动数据在磁盘层面停止储备。

MySQL 的架构可以依照三层模式来懂得

储备引擎也是 MySQL 的组建，它是一种软件，它所能做的和支撑的功效主要有

• 并发
• 支撑事务
• 完全性束缚
• 物理储备
• 支撑索引
• 机能帮忙

MySQL 默许支撑多种储备引擎，来适用不一样数据库利用，会员可以按照需要选中适宜的储备引擎，下面是 MySQL 支撑的储备引擎

• MyISAM
• InnoDB
• BDB
• MEMORY
• MERGE
• EXAMPLE
• NDB Cluster
• ARCHIVE
• CSV
• BLACKHOLE
• FEDERATED

默许状况下，假如创立表不指定储备引擎，会使用默许的储备引擎，假如要修改默许的储备引擎，那么就可以在参数文件中设定 default-table-type，能够查看当前的储备引擎

show variables like 'table_type';复制代码

惊奇，为什么没有了呢？网上求证一下，在 5.5.3 取消了这个参数

可以通过下面两种办法查询当前数据库支撑的储备引擎

show engines \g复制代码

在创立新表的时候，可以通过增添 ENGINE 关键字设定创建表的储备引擎。

create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;复制代码

上图我们指定了 MyISAM 的储备引擎。

假如你不知道表的储备引擎如何办？你可以通过 show create table 来查看

假如不指定储备引擎的话，从MySQL 5.1 版本之后，MySQL 的默许内置储备引擎已经是 InnoDB了。建一张表看一下

如上图所示，我们没有指定默许的储备引擎，下面查看一下表

可以看到，默许的储备引擎是 InnoDB。

假如你的储备引擎想要改换，可以使用

alter table cxuan003 engine = myisam;复制代码

来改换，改换完成后回显示 0 rows affected ，但其实已经操纵成功

我们使用 show create table 查看一下表的 sql 就知道

储备引擎特性

下面会介绍几个常用的储备引擎乃至它的根本特性，这些储备引擎是 **MyISAM、InnoDB、MEMORY 和 MERGE **

MyISAM

在 5.1 版本此前，MyISAM 是 MySQL 的默许储备引擎，MyISAM 并发性比力差，使用的场景比力少，主要特点是

• 不支撑事务操纵，ACID 的特性也就不存在了，这一设计是为了机能和效力思考的。

• 不支撑外键操纵，假如强行增添外键，MySQL 不会报错，只不外外键不起作用。

• MyISAM 默许的锁粒度是表级锁，所以并发机能比力差，加锁比力快，锁冲突比力少，不太容易发生死锁的状况。

• MyISAM 会在磁盘上储备三个文件，文件名和表名雷同，扩展名离别是 .frm(储备表定义)、.MYD(MYData,储备数据)、MYI(MyIndex,储备索引)。这里需要特殊留意的是 MyISAM 只缓存索引文件，并不缓存数据文件。

• MyISAM 支撑的索引类型有 全局索引(Full-Text)、B-Tree 索引、R-Tree 索引

  Full-Text 索引：它的显现是为理解决针对文本的含糊查询效力较低的问题。

  B-Tree 索引：所有的索引节点都依照均衡树的数据构造来储备，所有的索引数据节点都在叶节点

  R-Tree索引：它的储备方式和 B-Tree 索引有一些不同，主要设计用于储备空间和多维数据的字段做索引,当前的 MySQL 版本仅支撑 geometry 类型的字段作索引，相关于 BTREE，RTREE 的优势在于范畴查寻。

• 数据库所在主机假如宕机，MyISAM 的数据文件容易破坏，并且难以复原。

• 增删改查机能方面：SELECT 机能较高，适用于查询较多的状况

InnoDB

自从 MySQL 5.1 之后，默许的储备引擎变成了 InnoDB 储备引擎，相关于 MyISAM，InnoDB 储备引擎有了较大的改动，它的主要特点是

• 支撑事务操纵，具有事务 ACID 隔离特性，默许的隔离级别是可反复读(repetable-read)、通过MVCC（并发版本操纵）来实现的。能够解决脏读和不成反复读的问题。
• InnoDB 支撑外键操纵。
• InnoDB 默许的锁粒度行级锁，并发机能比力好，会发生死锁的状况。
• 和 MyISAM 一样的是，InnoDB 储备引擎也有 .frm文件储备表构造 定义，但是不一样的是，InnoDB 的表数据与索引数据是储备在一起的，都位于 B+ 数的叶子节点上，而 MyISAM 的表数据和索引数据是分开的。
• InnoDB 有平安的日志文件，这个日志文件用于复原因数据库崩溃或其他状况致使的数据丧失问题，包管数据的一致性。
• InnoDB 和 MyISAM 支撑的索引类型雷同，但详细实现由于文件构造的不一样有很大差别。
• 增删改查机能方面，果施行大量的增删改操纵，引荐使用 InnoDB 储备引擎，它在删除操纵时是对行删除，不会重建表。

MEMORY

MEMORY 储备引擎使用存在内存中的内容来创立表。每个 MEMORY 表实际只对应一个磁盘文件，格局是 .frm。 MEMORY 类型的表拜访速度很快，由于其数据是存置在内存中。默许使用 HASH 索引。

MERGE

MERGE 储备引擎是一组 MyISAM 表的组合，MERGE 表本身没有数据，对 MERGE 类型的表停止查询、更新、删除的操纵，实际上是对内部的 MyISAM 表停止的。MERGE 表在磁盘上保存两个文件，一个是 .frm 文件储备表定义、一个是 .MRG 文件储备 MERGE 表的组成等。

选中适宜的储备引擎

在实际开发历程中，我们往往会按照利用特点选中适宜的储备引擎。

• MyISAM：假如利用程序平常以检索为主，只要少量的插入、更新和删除操纵，并且对事物的完全性、并发程度不是很高的话，平常倡议选中 MyISAM 储备引擎。
• InnoDB：假如使用到外键、需要并发程度较高，数据一致性要求较高，那么平常选中 InnoDB 引擎，一样互联网大厂对并发和数据完全性要求较高，所以一样都使用 InnoDB 储备引擎。
• MEMORY：MEMORY 储备引擎将所有数据留存在内存中，在需要快速定位下能够供给及其快速的拜访。MEMORY 平常用于更新不太频繁的小表，用于快速拜访取得结果。
• MERGE：MERGE 的内部是使用 MyISAM 表，MERGE 表的长处在于可以冲破对单个 MyISAM 表大小的限制，并且通过将不一样的表分布在多个磁盘上， 可以有效地改善 MERGE 表的拜访效力。

选中适宜的数据类型

我们会经常碰见的一个问题就是，在建表时怎样选中适宜的数据类型，平常选中适宜的数据类型能够提高机能、减少不必要的费事，下面我们就来一起商量一下，怎样选中适宜的数据类型。

CHAR 和 VARCHAR 的选中

char 和 varchar 是我们经常要用到的两个储备字符串的数据类型，char 一样储备定长的字符串，它属于牢固长度的字符类型，比方下面

可以看到，不管你的值写的是啥，一旦指定了 char 字符的长度，假如你的字符串长度不足指定字符的长度的话，那么就用空格来弥补，假如超越字符串长度的话，只储备指定字符长度的字符。

这里留意一点：假如 MySQL 使用了非 严厉模式的话，上面表格最后一行是可以储备的。假如 MySQL 使用了 严厉模式 的话，那么表格上面最后一行储备会报错。

假如使用了 varchar 字符类型，我们来看一下例子

可以看到，假如使用 varchar 的话，那么储备的字节将按照实际的值停止储备。你大概会迷惑为什么 varchar 的长度是 5 ，但是却需要储备 3 个字节或者 6 个字节，这是由于使用 varchar 数据类型停止储备时，默许会在最后增添一个字符串长度，占用1个字节（假如列声明的长度超越255，则使用两个字节）。varchar 不会填充空余的字符串。

一样使用 char 来储备定长的字符串，比方身份证号、手机号、邮箱等；使用 varchar 来储备不定长的字符串。由于 char 长度是牢固的，所以它的处置速度要比 VARCHAR 快许多，但是缺陷是白费储备空间，但是随着 MySQL 版本的不竭演进，varchar 数据类型的机能也在不竭改善和提高，所以在很多利用中，VARCHAR 类型更多的被使用。

在 MySQL 中，不一样的储备引擎对 CHAR 和 VARCHAR 的使用原则也有不一样

• MyISAM：倡议使用牢固长度的数据列替换可变长度的数据列，也就是 CHAR
• MEMORY：使用牢固长度停止处置、CHAR 和 VARCHAR 都会被当作 CHAR 处置
• InnoDB：倡议使用 VARCHAR 类型

TEXT 与 BLOB

一样在留存较少的文本的时候，我们会选中 CHAR 和 VARCHAR，在留存大数据量的文本时，我们往往选中 TEXT 和 BLOB；TEXT 和 BLOB 的主要差异是 BLOB 能够留存二进制数据；而 TEXT 只能留存字符数据，TEXT 往下细分有

• TEXT
• MEDIUMTEXT
• LONGTEXT

BLOB 往下细分有

• BLOB
• MEDIUMBLOB
• LONGBLOB

三种，它们最主要的不同就是储备文本长度不一样和储备字节不一样，会员应当按照实际状况选中知足需求的最小储备类型，下面主要对 BLOB 和 TEXT 存在一些问题停止介绍

TEXT 和 BLOB 在删除数据后会存在一些机能上的问题，为了提高机能，倡议使用 OPTIMIZE TABLE 功效对表停止碎片整理。

也可以使用合成索引来提高文本字段（BLOB 和 TEXT）的查询机能。合成索引就是按照大文本（BLOB 和 TEXT）字段的内容创立一个散列值，把这个值存在对应列中，这样就能够按照散列值查寻到对应的数据行。一样使用散列算法比方 md5() 和 SHA1() ，假如散列算法生成的字符串带有尾部空格，就不要把它们存在 CHAR 和 VARCHAR 中，下面我们就来看一下这种使用方式

第一创立一张表，表中记载 blob 字段和 hash 值

向 cxuan005 中插入数据，其中 hash 值作为 info 的散列值。

然后再插入两条数据

插入一条 info 为 cxuan005 的数据

假如想要查询 info 为 cxuan005 的数据，可以通过查询 hash 列来停止查询

这是合成索引的例子，假如要对 BLOB 停止含糊查询的话，就要使用前缀索引。

其他优化 BLOB 和 TEXT 的方式：

• 非必要的时候不要检索 BLOB 和 TEXT 索引
• 把 BLOB 或 TEXT 列别离到独自的表中。

浮点数和定点数的选中

浮点数指的就是含有小数的值，浮点数插入到指定列中超越指定精度后，浮点数会四舍五入，MySQL 中的浮点数指的就是 float 和 double，定点数指的是 decimal，定点数能够愈加准确的留存和显示数据。下面通过一个示例讲解一下浮点数准确性问题

第一创立一个表 cxuan006 ，只为了测试浮点数问题，所以这里我们选中的数据类型是 float

然后离别插入两条数据

然后施行查询，可以看到查询出来的两条数据施行的舍入不一样

为了清楚的看分明浮点数与定点数的精度问题，再来看一个例子

先修改 cxuan006 的两个字段为雷同的长度和小数位数

然后插入两条数据

施行查询操纵，可以发明，浮点数相较于定点数来说，会发生误差

日期类型选中

在 MySQL 中，用来表示日期类型的有 DATE、TIME、DATETIME、TIMESTAMP，在

138 张图带你 MySQL 入门

这篇文中介绍过了日期类型的不同，我们这里就不再论述了。下面主要介绍一下选中

• TIMESTAMP 和时区相关，更能反映当前时间，假如记载的日期需要让不一样时区的人使用，最好使用 TIMESTAMP。
• DATE 用于表示年月日，假如实际利用值需要留存年月日的话就可以使用 DATE。
• TIME 用于表示时分秒，假如实际利用值需要留存时分秒的话就可以使用 TIME。
• YEAR 用于表示年份，YEAR 有 2 位（最好使用4位）和 4 位格局的年。 默许是4位。假如实际利用只留存年份，那么用 1 bytes 留存 YEAR 类型完全可以。不单能够节省储备空间，还能提高表的操纵效力。

MySQL 字符集

下面来认识一下 MySQL 字符集，简便来说字符集就是一套文字符号和编码、比力规则的汇合。1960 年老美标准化组织 ANSI 公布了第一个运算机字符集，就是闻名的 ASCII(American Standard Code for Information Interchange) 。自从 ASCII 编码后，每个我国、国际组织都研讨了一套本人的字符集，比方 ISO-8859-1、GBK 等。

但是每个我国都使用本人的字符集为移植性带来了很大的艰难。所以，为了统一字符编码，国际标准化组织(ISO) 指定了统一的字符标准 - Unicode 编码，它容纳了几乎所有的字符编码。下面是一些常见的字符编码

对数据库来说，字符集是很重要的，由于数据库储备的数据大多数都是各种文字，字符集对数据库的储备、机能、系统的移植来说都非常重要。

MySQL 支撑多种字符集，可以使用 show character set; 来查看所有可用的字符集

或者使用

select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;复制代码

来查看。

使用 information_schema.character_set 来查看字符集和校订规则。

索引的设计和使用

我们上面介绍到了索引的几品种型并对不一样的索引类型做了论述，说明了优缺陷等等，下面我们从设计角度来聊一下索引，关于索引，你必需要知道的一点就是：索引是数据库用来提高机能的最常用工具。

索引概述

所有的 MySQL 类型都可以停止索引，对相关列使用索引是提高 SELECT 查询机能的最好途径。MyISAM 和 InnoDB 都是使用 BTREE 作为索引，MySQL 5 不支撑函数索引，但是支撑 前缀索引。

前缀索引望文生义就是对列字段的前缀做索引，前缀索引的长度和储备引擎有关系。MyISAM 前缀索引的长度支撑到 1000 字节，InnoDB 前缀索引的长度支撑到 767 字节，索引值反复性越低，查询效力也就越高。

在 MySQL 中，主要有下面这几种索引

• 全局索引(FULLTEXT)：全局索引，当前只要 MyISAM 引擎支撑全局索引，它的显现是为理解决针对文本的含糊查询效力较低的问题，并且只限于 CHAR、VARCHAR 和 TEXT 列。
• 哈希索引(HASH)：哈希索引是 MySQL 中用到的独一 key-value 键值对的数据构造，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查寻，但是这种查寻适合利用于查寻单个键的状况，关于范畴查寻，HASH 索引的机能就会很低。默许状况下，MEMORY 储备引擎使用 HASH 索引，但也支撑 BTREE 索引。
• B-Tree 索引：B 就是 Balance 的意思，BTree 是一种均衡树，它有许多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。
• R-Tree 索引：R-Tree 在 MySQL 很少使用，仅支撑 geometry 数据类型，支撑该类型的储备引擎只要MyISAM、BDb、InnoDb、NDb、Archive几种，相关于 B-Tree 来说，R-Tree 的优势在于范畴查寻。

索引可以在创立表的时候停止创立，也可以独自创立，下面我们采纳独自创立的方式，我们在 cxuan004 上创立前缀索引

我们使用 explain 停止剖析，可以看到 cxuan004 使用索引的状况

假如不想使用索引，可以删除索引，索引的删除语法是

索引设计原则

创立索引的时候，要尽量思考以下原则，便于晋升索引的使用效力。

• 选中索引位置，选中索引最适宜的位置是显现在 where 语句中的列，而不是 select 关键字后的选中列表中的列。
• 选中使用独一索引，望文生义，独一索引的值是独一的，可以更快速确实定某笔记录，例如学生的学号就适合使用独一性索引，而学生的性别则不适合使用，由于不管搜索哪个值，都差不多有一半的行。
• 为经常使用的字段创立索引，假如某个字段经常用作查询前提，那么这个字段的查询速度在极大程度上影响整个表的查询速度，因此为这样的字段创立索引，可以提高整个表的查询速度。
• 不要过度索引，限制索引数目，索引的数目不是多多益善，每个索引都会占据磁盘空间，索引越多，需要的磁盘空间就越大。
• 尽量使用前缀索引，假如索引的值很长，那么查询速度会受到影响，这个时候应当使用前缀索引，对列的某几个字符停止索引，可以提高检索效力。
• 利用最左前缀，在创立一个 n 列的索引时，实际上是创立了 MySQL 可利用的 n 个索引。多列索引可以起到几个索引的作用，利用索引最左边的列来匹配行，这样的列称为最左前缀。
• 关于使用 InnoDB 储备引擎的表来说，记载会依照必然的次序留存。假如有明白的主键定义，那么会依照主键的次序停止留存；假如没有主键，但是有独一索引，那么就依照独一索引的次序停止留存。假如既没有主键又没有独一索引，那么表中会主动生成一个内部列，依照这个列的次序停止留存。一样来说，使用主键的次序是最快的
• 删除不再使用或者很少使用的索引

视图

MySQL 从 5.0 开端就供给了视图功效，下面我们对视图功效停止介绍。

什么是视图

视图的英文名称是 view，它是一种虚拟存在的表。视图关于会员来说是透亮的，它并不在数据库中实际存在，视图是使用数据库行和列动态组成的表，那么视图相关于数据库表来说，优势表现在哪里？

视图相关于一般的表来说，优势包括下面这几项

• 使用视图可以简化操纵：使用视图我们不消关注表构造的定义，我们可以把经常使用的数据汇合定义成视图，这样能够简化操纵。
• 平安性：会员对视图不成以随便的更换和删除，可以包管数据的平安性。
• 数据独立性：一旦视图的构造 肯定了， 可以屏蔽表构造转变对会员的影响， 数据库表增添列对视图没有影响；具有必然的独立性

对视图的操纵

视图的操纵包罗创立或者修改视图、删除视图乃至查看视图定义。

创立或修改视图

使用 create view 来创立视图

为了演示功效，我们先创立一张表 product 表，有三个字段，id，name，price，下面是建表语句

create table product(id int(11),name varchar(20),price float(10,2));复制代码

然后我们向其中插入几条数据

insert into product values(1, "apple","3.5"),(2,"banana","4.2"),(3,"melon","1.2");复制代码

插入完成后的表构造如下

然后我们创立视图

create view v1 as select * from product;复制代码

然后我们查看一下 v1 视图的构造

可以看到我们把 product 中的数据放在了视图中，也相当于是创立了一个 product 的副本，只不外这个副本跟表无关。

视图使用

show tables;复制代码

也能看到所有的视图。

删除视图的语法是

drop view v1;复制代码

能够直接停止删除。

视图还有其他操纵，比方查询操纵

你还可以使用

describe v1;复制代码

查看表构造

更新视图

update v1 set name = "grape" where id = 1;复制代码

储备历程

MySQL 从 5.0 开端起就支撑储备历程和函数了。

那么什么是储备历程呢？

储备历程是在数据库系统中完成一组特定功效的 SQL 语句集，它储备在数据库系统中，一次编译后永远有效。那么使用储备历程有什么长处呢？

• 使用储备历程具有可封装性，能够潜藏复杂的 SQL 逻辑。
• 储备历程可以接收参数，并返回结果
• 储备历程机能非常高，一样用于大量施行语句

使用储备历程有什么缺陷？

• 储备历程编写复杂
• 储备历程对数据库的依靠性比力强，可移植性比力差

储备历程使用

储备历程创立

在认识到储备历程是啥之后，我们就来使用一下储备历程，这里需要先理解一个小技巧，也就是 delimiter 的用途，delimiter 用于自定义完毕符，什么意思呢，假如你使用

delimiter ?复制代码

的话，那么你在 sql 语句末使用 ; 是不克不及使 SQL 语句施行的，不信？我们可以看下

可以看到，我们在 SQL 语句的行末使用了 ; 但是我们却没有看到施行结果。下面我们使用

delimiter ;复制代码

复原默许的施行前提再来看下

我们创立储备历程第一要把 ; 更换为 ?，下面是一个储备历程的创立语句

mysql> delimiter ?
mysql> create procedure sp_product()
    -> begin
    -> select * from product;
    -> end ?复制代码

储备历程实际上是一种函数，所以创立完毕后，我们可以使用 call 办法来调取这个储备历程

由于我们上面定义了使用 delimiter ? 来结尾，所以这里也应当使用。

储备历程也可以接受参数，比方我们定义一种接收参数的状况

然后我们使用 call 调取这个储备历程

可以看到，当我们调取 id = 2 的时候，储备历程的 SQL 语句相当于是

select * from product where id = 2;复制代码

所以只查询出 id = 2 的结果。

储备历程删除

一次只能删除一个储备历程，删除储备历程的语法如下

drop procedure sp_product ;复制代码

直接使用 sp_product 就可以了，不消加 ()。

储备历程查看

储备历程创立后，会员大概需要需要查看储备历程的状态等信息，便于理解储备历程的根本状况

我们可以使用

show create procedure proc_name;复制代码

变量的使用

在 MySQL 中，变量可分为两大类，即系统变量和会员变量，这是一种粗略的分法。但是按照实际利用又被细化为四品种型，即部分变量、会员变量、会话变量和全局变量。

会员变量

会员变量是基于会话变量实现的，可以暂存，会员变量与连接有关，也就是说一个客户端定义的变量不克不及被其他客户端使用看到。当客户端退出时，链接会主动开释。我们可以使用 set 语句设定一个变量

set @myId = "cxuan";复制代码

然后使用 select 查询前提可以查询出我们刚刚设定的会员变量

会员变量是和客户端有关系，当我们退出后，这个变量会主动消逝，此刻我们退出客户端

exit复制代码

此刻我们从新登陆客户端，再次使用 select 前提查询

发明已经没有这个 @myId 了。

部分变量

MySQL 中的部分变量与 Java 很相似 ，Java 中的部分变量是 Java 所在的办法或者代码块，而 MySQL 中的部分变量作用域是所在的储备历程。MySQL 部分变量使用 declare 来声明。

会话变量

效劳器会为每个连接的客户端保护一个会话变量。可以使用

show session variables;复制代码

显示所有的会话变量。

我们可以手动设定会话变量

set session auto_increment_increment=1;

或者使用

set @@session.auto_increment_increment=2;复制代码

然后停止查询，查询会话变量使用

或者使用

全局变量

当效劳启动时，它将所有全局变量初始化为默许值。其作用域为 server 的整个生命周期。

可以使用

show global variables;复制代码

查看全局变量

可以使用下面这两种方式设定全局变量

set global sql_warnings=ON;        -- global不克不及省略

/** 或者 **/

set @@global.sql_warnings=OFF;复制代码

查询全局变量时，可以使用

或者是

MySQL 流程语句介绍

MySQL 支撑下面这些操纵语句

• IF

IF 用于实现逻辑推断，知足不一样前提施行不一样的 SQL 语句

IF ... THEN ...复制代码

• CASE

CASE 实现比 IF 轻微复杂，语法如下

CASE ...
	WHEN ... THEN...
	...
END CASE复制代码

CASE 语句也可以使用 IF 来完成

• LOOP

LOOP 用于实现简便的轮回

label:LOOP
     ...
END LOOP label;复制代码

假如 ... 中不写 SQL 语句的话，那么就是一个简便的死轮回语句

• LEAVE

用来表示从标注的流程结构中退出，平常和 BEGIN...END 或者轮回一起使用

• ITERATE

ITERATE 语句必需用在轮回中，作用是跳过当前轮回的剩下的语句，直接进入下一轮轮回。

• REPEAT

带有前提的轮回操纵语句，当知足前提的时候退出轮回。

REPEAT
   ...
   UNTIL
END REPEAT;复制代码

• WHILE

WHILE 语句表示的含义和 REPEAT 相差无几，WHILE 轮回和 REPEAT 轮回的不同在于：WHILE 是知足前提才施行轮回，REPEAT 是知足前提退出轮回；

触发器

MySQL 从 5.0 开端支撑触发器，触发器一样作用在表上，在知足定义前提时触发，并施行触发器中定义的语句汇合，下面我们就来一起认识一下触发器。

举个例子来认识一下触发器：比方你有一个日志表和金额表，你每录入一笔金额就要停止日志表的记载，你会如何样？同时在金额表和日志表插入数据吗？假如有了触发器，你可以直接在金额表录入数据，日志表会主动插入一条日志记载，当然，触发器不仅只要新增操纵，还有更新和删除操纵。

创立触发器

我们可以用如下的方式创立触发器

create trigger triggername triggertime triggerevent on tbname for each row triggerstmt复制代码

上面触及到几个参数，我知道你有点懵逼，说明一下。

• triggername：这个指的就是触发器的名字
• triggertime：这个指的就是触发器触发时机，是 BEFORE 还是 AFTER
• triggerevent: 这个指的就是触发器触发事件，一共有三种事件：INSERT、UPDATE 或者 DELETE。
• tbname：这个参数指的是触发器创立的表名，在哪个表上创立
• triggerstmt: 触发器的程序体，也就是 SQL 语句

所以，可以创立六种触发器

BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE

上面的 for each now 表示任何一笔记录上的操纵都会触发触发器。

下面我们通过一个例子来演示一下触发器的操纵

我们还是用上面的 procuct 表做例子，我们创立一个 product_info 产品信息表。

create table product_info(p_info varchar(20)); 
复制代码

然后我们创立一个 trigger

我们在 product 表中插入一条数据

insert into product values(4,"pineapple",15.3);复制代码

我们停止 select 查询，可以看到此刻 product 表中有四条数据

我们没有向 product_info 表中插入数据，此刻我们来看一下 product_info 表中，我们料想到是有数据的，详细来看下

这条数据是啥时候插入的呢？我们在创立触发器 tg_pinfo 的时候插入了的这条数据。

删除触发器

触发器可以使用 drop 停止删除，详细删除语法如下

drop trigger tg_pinfo;复制代码

和删除表的语法是一样的

查看触发器

我们经常会查看触发器，可以通过施行 show triggers 命令查看触发器的状态、语法等信息。

另一种查询方式是查询表中的 information_schema.triggers 表，这个可以查询指定触发器的指定信息，操纵起来利便许多

触发器的作用

• 在增加一条数据前，检查数据可否合理，例如检查邮件格局可否准确
• 删除数据后，相当于数据备份的作用
• 可以记载数据库的操纵日志，也可以作为表的施行轨迹

留意：触发器的使用有两个限制

1. 触发程序不克不及调取将数据返回客户端的储备程序。也不克不及使用 CALL 语句的动态 SQL 语句。
2. 不克不及在触发器中开端和完毕语句，例如 START TRANSACTION