mysql 触发器中表 MySQL进阶（B）

目录

MySQL 存储引擎 存储引擎概述

数据库最核心的一点就是用来存储数据，数据存储就避免不了和磁盘打交道。那么数据以哪种方式进行存储，如何存储是存储的关键所在。所以存储引擎就相当于是数据存储的发动机，来驱动数据在磁盘层面进行存储。

MySQL 的架构可以按照三层模式来理解

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

MySQL 默认支持多种存储引擎，来适用不同数据库应用，用户可以根据需要选择合适的存储引擎，下面是 MySQL 支持的存储引擎

默认情况下，如果创建表不指定存储引擎，会使用默认的存储引擎，如果要修改默认的存储引擎，那么就可以在参数文件中设置-table-type，能够查看当前的存储引擎

show variables like 'table_type';

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

可以通过下面两种方法查询当前数据库支持的存储引擎

show engines \g

在创建新表的时候，可以通过增加关键字设置新建表的存储引擎。

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

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

如果你不知道表的存储引擎怎么办？你可以通过show table来查看

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

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

可以看到，默认的存储引擎是。

如果你的存储引擎想要更换，可以使用

alter table cxuan003 engine = myisam;

来更换，更换完成后回显示0 rows ，但其实已经操作成功

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

存储引擎特性

下面会介绍几个常用的存储引擎以及它的基本特性，这些存储引擎是 **、、 和 MERGE **

在 5.1 版本之前， 是 MySQL 的默认存储引擎， 并发性比较差，使用的场景比较少，主要特点是

自从 MySQL 5.1 之后，默认的存储引擎变成了 存储引擎，相对于 ， 存储引擎有了较大的改变，它的主要特点是

存储引擎使用存在内存中的内容来创建表。每个 表实际只对应一个磁盘文件，格式是.frm。 类型的表访问速度很快，因为其数据是存放在内存中。默认使用HASH 索引。

MERGE

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

选择合适的存储引擎

在实际开发过程中，我们往往会根据应用特点选择合适的存储引擎。

选择合适的数据类型

我们会经常遇见的一个问题就是，在建表时如何选择合适的数据类型，通常选择合适的数据类型能够提高性能、减少不必要的麻烦，下面我们就来一起探讨一下，如何选择合适的数据类型。

CHAR 和 的选择

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

值char(5)存储字节

''

' '

5个字节

'cx'

'cx '

5个字节

'cxuan'

'cxuan'

5个字节

''

'cxuan'

5个字节

可以看到，不管你的值写的是什么，一旦指定了 char 字符的长度，如果你的字符串长度不够指定字符的长度的话，那么就用空格来填补，如果超过字符串长度的话，只存储指定字符长度的字符。

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

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

值(5)存储字节

''

''

1个字节

'cx'

'cx '

3个字节

'cxuan'

'cxuan'

6个字节

''

'cxuan'

6个字节

可以看到，如果使用 的话，那么存储的字节将根据实际的值进行存储。你可能会疑惑为什么 的长度是 5 ，但是却需要存储 3 个字节或者 6 个字节，这是因为使用 数据类型进行存储时，默认会在最后增加一个字符串长度，占用1个字节（如果列声明的长度超过255，则使用两个字节）。 不会填充空余的字符串。

一般使用 char 来存储定长的字符串，比如身份证号、手机号、邮箱等；使用 来存储不定长的字符串。由于 char 长度是固定的，所以它的处理速度要比 快很多，但是缺点是浪费存储空间mysql触发器，但是随着 MySQL 版本的不断演进， 数据类型的性能也在不断改进和提高，所以在许多应用中， 类型更多的被使用。

在 MySQL 中，不同的存储引擎对 CHAR 和 的使用原则也有不同

TEXT 与 BLOB

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

BLOB 往下细分有

三种，它们最主要的区别就是存储文本长度不同和存储字节不同，用户应该根据实际情况选择满足需求的最小存储类型mysql 触发器中表，下面主要对 BLOB 和 TEXT 存在一些问题进行介绍

TEXT 和 BLOB 在删除数据后会存在一些性能上的问题，为了提高性能，建议使用 TABLE功能对表进行碎片整理。

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

首先创建一张表，表中记录 blob 字段和 hash 值

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

然后再插入两条数据

插入一条 info 为 的数据

如果想要查询 info 为 的数据，可以通过查询 hash 列来进行查询

这是合成索引的例子，如果要对 BLOB 进行模糊查询的话，就要使用前缀索引。

其他优化 BLOB 和 TEXT 的方式：

浮点数和定点数的选择

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

首先创建一个表 ，只为了测试浮点数问题，所以这里我们选择的数据类型是 float

然后分别插入两条数据

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

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

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

然后插入两条数据

执行查询操作，可以发现，浮点数相较于定点数来说，会产生误差

日期类型选择

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

MySQL 入门基础（A）

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

MySQL 字符集

下面来认识一下 MySQL 字符集，简单来说字符集就是一套文字符号和编码、比较规则的集合。1960 年美国标准化组织 ANSI 发布了第一个计算机字符集，就是著名的ASCII( Code for )。自从 ASCII 编码后，每个国家、国际组织都研究了一套自己的字符集，比如ISO-8859-1、GBK等。

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

字符集是否定长编码方式

ASCII

是

单字节 7 位编码

ISO-8859-1

是

单字节 8 位编码

GBK

是

双字节编码

UTF-8

否

1 - 4 字节编码

UTF-16

否

2 字节或 4 字节编码

UTF-32

是

4 字节编码

对数据库来说，字符集是很重要的，因为数据库存储的数据大多数都是各种文字，字符集对数据库的存储、性能、系统的移植来说都非常重要。

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

或者使用

select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;

来查看。

使用.来查看字符集和校对规则。

索引的设计和使用

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

索引概述

所有的 MySQL 类型都可以进行索引，对相关列使用索引是提高查询性能的最佳途径。 和 都是使用BTREE作为索引，MySQL 5 不支持函数索引，但是支持前缀索引。

前缀索引顾名思义就是对列字段的前缀做索引，前缀索引的长度和存储引擎有关系。 前缀索引的长度支持到 1000 字节， 前缀索引的长度支持到 767 字节，索引值重复性越低，查询效率也就越高。

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

索引可以在创建表的时候进行创建，也可以单独创建，下面我们采用单独创建的方式，我们在 上创建前缀索引

我们使用进行分析，可以看到 使用索引的情况

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

索引设计原则

创建索引的时候，要尽量考虑以下原则，便于提升索引的使用效率。

视图

MySQL 从 5.0 开始就提供了视图功能，下面我们对视图功能进行介绍。

什么是视图

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

视图相对于普通的表来说，优势包含下面这几项

对视图的操作

视图的操作包括创建或者修改视图、删除视图以及查看视图定义。

创建或修改视图

使用 view来创建视图

为了演示功能，我们先创建一张表表，有三个字段，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 视图的结构

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

视图使用

show tables;

也能看到所有的视图。

删除视图的语法是

drop view v1;

能够直接进行删除。

视图还有其他操作，比如查询操作

你还可以使用

describe v1;

查看表结构

更新视图

update v1 set name = "grape" where id = 1;

MySQL 从 5.0 开始起就支持存储过程和函数了。

那么什么是存储过程呢？

存储过程是在数据库系统中完成一组特定功能的 SQL 语句集，它存储在数据库系统中，一次编译后永久有效。那么使用存储过程有什么优点呢？

使用存储过程有什么缺点？

存储过程使用

存储过程创建

在认识到存储过程是什么之后，我们就来使用一下存储过程，这里需要先了解一个小技巧，也就是的用法， 用于自定义结束符，什么意思呢，如果你使用

delimiter $$

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

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

delimiter ;

恢复默认的执行条件再来看下

我们创建存储过程首先要把;替换为$$，下面是一个存储过程的创建语句

mysql> delimiter $$
mysql> create procedure sp_product()
    -> begin
    -> select * from product;
    -> end $$

存储过程实际上是一种函数，所以创建完毕后，我们可以使用call方法来调用这个存储过程

因为我们上面定义了使用 $$ 来结尾，所以这里也应该使用。

存储过程也可以接受参数，比如我们定义一种接收参数的情况

然后我们使用call调用这个存储过程

可以看到，当我们调用 id = 2 的时候，存储过程的 SQL 语句相当于是

select * from product where id = 2;

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

存储过程删除

一次只能删除一个存储过程，删除存储过程的语法如下

drop procedure sp_product ;

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

存储过程查看

存储过程创建后，用户可能需要需要查看存储过程的状态等信息，便于了解存储过程的基本情况

我们可以使用

show create procedure proc_name;

变量的使用

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

用户变量

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

set @myId = "cxuan";

然后使用查询条件可以查询出我们刚刚设置的用户变量

用户变量是和客户端有关系，当我们退出后，这个变量会自动消失，现在我们退出客户端

exit

现在我们重新登陆客户端，再次使用条件查询

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

局部变量

MySQL 中的局部变量与 Java 很类似 ，Java 中的局部变量是 Java 所在的方法或者代码块，而 MySQL 中的局部变量作用域是所在的存储过程。MySQL 局部变量使用来声明。

会话变量

服务器会为每个连接的客户端维护一个会话变量。可以使用

show session variables;

显示所有的会话变量。

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

set session auto_increment_increment=1;
或者使用
set @@session.auto_increment_increment=2;

然后进行查询，查询会话变量使用

或者使用

全局变量

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

可以使用

show global variables;

查看全局变量

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

set global sql_warnings=ON;        -- global不能省略
/** 或者 **/
set @@global.sql_warnings=OFF;

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

或者是

MySQL 流程语句介绍

MySQL 支持下面这些控制语句

IF 用于实现逻辑判断，满足不同条件执行不同的 SQL 语句

IF ... THEN ...

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

CASE ...
	WHEN ... THEN...
	...
END CASE

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

LOOP 用于实现简单的循环

label:LOOP
     ...
END LOOP label;

如果...中不写 SQL 语句的话，那么就是一个简单的死循环语句

用来表示从标注的流程构造中退出，通常和 BEGIN...END 或者循环一起使用

语句必须用在循环中，作用是跳过当前循环的剩下的语句，直接进入下一轮循环。

带有条件的循环控制语句，当满足条件的时候退出循环。

REPEAT
   ...
   UNTIL
END REPEAT;

WHILE 语句表示的含义和 相差无几，WHILE 循环和 循环的区别在于：WHILE 是满足条件才执行循环， 是满足条件退出循环；

触发器

MySQL 从 5.0 开始支持触发器，触发器一般作用在表上mysql 触发器中表，在满足定义条件时触发，并执行触发器中定义的语句集合，下面我们就来一起认识一下触发器。

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

创建触发器

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

create trigger triggername triggertime triggerevent on tbname for each row triggerstmt

上面涉及到几个参数，我知道你有点懵逼，解释一下。

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

、AFTER 、 、AFTER 、 、AFTER

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

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

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

create table product_info(p_info varchar(20)); 

然后我们创建一个

我们在 表中插入一条数据

insert into product values(4,"pineapple",15.3);

我们进行 查询，可以看到现在 表中有四条数据

我们没有向 表中插入数据，现在我们来看一下 表中，我们预想到是有数据的，具体来看下

这条数据是什么时候插入的呢？我们在创建触发器的时候插入了的这条数据。

删除触发器

触发器可以使用drop进行删除，具体删除语法如下

drop trigger tg_pinfo;

和删除表的语法是一样的

查看触发器

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

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

触发器的作用

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

触发程序不能调用将数据返回客户端的存储程序。也不能使用 CALL 语句的动态 SQL 语句。不能在触发器中开始和结束语句，例如 START

（编辑：徐州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!