1 MySQL存储过程
1.1 存储过程简介
- 当我们成功输入命令后,MySQL的引擎会首先对命令进行分析,所书写的语句是否正确,如果正确的话就会编译成MySQL可以识别的命令,最后开始执行,并将执行的结果返回给客户端
- 存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元来处理。存储过程存储在数据库内,可以由应用程序调用执行,而且允许用户声明变量以及流程控制,存储过程可以接收参数,可以接收输入类型的参数,也可以接收输出类型的参数,而且可以返回多个值。
- 存储过程比我们单一的命令执行效率高,原因在于:之前写两个语句,MySQL会对这两个语句逐一进行语法分析,逐一的编译,逐一的执行,而采用了存储过程后,只有第一次才进行语法分析和编译,以后客户端再去调用,直接来调用编译的结果就可以了。
- 存储过程的优点:
- 增强SQL语句的功能和灵活性,存储过程中可以写控制语句,就有很强的灵活性,可以完成复杂的判断以及较复杂的运算
- 实现较快的执行速度
- 减少了网络流量 ,通过客户端每个单独发送SQL语句,通过HTTP协议所提交的数据量相对较大
1.2 存储过程语法结构解析
创建存储过程,与创建自定义函数的结构类似
CREATE [DEFINER = user|CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[,…]]) [characteristic …] routine_body
- DEFINER:创建者,不写默认为当前用户
- PROCEDURE:后面是存储过程的名字,括号中可以有0个或多个参数
proc_parameter格式:
[IN | OUT | INOUT] param_name type
- IN:表示该参数的值必须在调用存储过程时指定,这个值是不能被返回的
- OUT: 表示该参数的值可以被存储过程改变,并且可以返回
- INOUT:表示该参数调用时指定,并且可以被改变或返回
characteristic
LANGUAGE SQL | [NOT] DETERMINISTIC |{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER|INVOKER} | COMMENT 'string'
LANGUAGE SQL:说明routin_body部分是由SQL语句组成的
- [NOT] DETERMINISTIC:指明存储过程执行的结果是否正确,DETERMINISTIC表示结果是确定的,每次执行,相同的输入会得到相同的输出,默认情况下,系统默认指定NOT
- CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA:指明子程序使用SQL语句的限制,默认情况下,系统指定为CONTAINS SQL
- CONTAINS SQL:表明子程序包含SQL语句,但是不包含读写数据的语句
- NO SQL:表明子程序不包含SQL语句
- READS SQL DATA:说明子程序包含读数据的语句
- MODIFIES SQL DATA:表明子程序包含写数据的语句
- SQL SECURITY {DEFINER|INVOKER}:指明谁有权限来执行
- DEFINER表示只有定义者才能执行
- INVOKER表示拥有权限的调用者可以执行,默认情况下只有定义者才能使用
- COMMENT ‘stirng’:注释信息,可以用来描述存储过程或函数
- 过程体
- 过程体由合法的SQL语句构成
- 过程体可以是任意(对于记录的增删改查和多表连接)的SQL语句,但不能通过存储过程创建数据库(或表)
- 过程体如果为复合结构则使用BEGIN…END语句
- 复合结构中可以包含声明,循环和控制结构
1.3 创建不带参数的存储过程
- 创建一个存储过程,用来查看MySQL的版本:
调用存储过程
CALL sp_name([parameter[,...]])
CALL sp_name[()] //没有参数时
1.4 创建带有IN类型参数的存储过程
删除指定id的字段,id作为参数传递,在调用存储过程时指定
(创建之前先修改定界符,创建之后再修改过来)
但是执行删除id=3的存储过程后,发现数据表为空了,因为传入的参数与数据表中的参数名相同,MySQL认为两个id都是字段,所以将所有记录都删除了,因此,在给参数的时候,参数的名字不能和数据表中字段的名字相同。
修改存储过程无法修改过程体,所以必须删除存储过程然后重建:
//修改存储过程,只能修改一些简单的特性,并不能修改过程体
ALTER PROCEDURE sp_name [characteristic...]
characteristic:
{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
//删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name
重新创建存储过程,并删除id号为6的记录:
1.5 创建带有IN和OUT类型参数的存储过程
删除指定id的记录,并返回剩余的记录数:
(INTO:将结果放入到userNums中)
调用存储过程,使用“@”声明变量,称为用户变量,只在客户端有效
1.6 创建带有多个OUT类型参数的存储过程
创建一个存储过程,根据年龄来删除记录(年龄为IN类型的参数),返回要删除的用户和剩余的用户数
- MySQL的内置函数ROW_COUNT()用来返回它前一条命令使数据表跟新修改的记录
- 创建存储过程
- 调用存储过程
- 查看结果
1.7 存储过程中的变量
1.7.1 DECLARE局部变量
DECLARE var_name[,...] type [DEFAULT value]
- 这个语句被用来声明局部变量,作用范围在它被声明的BEGIN…END快内
- 可以给变量提供一个默认值,如果没有DEFAULT子句,初始值为NULL
- 变量的值可以被指定为一个表达式,不需要为一个常数
1.7.2 变量SET语句
SET var_name = expr [,var_name = expr]...
1.8 存储过程与自定义函数的区别
- 存储过程实现的功能要复杂一些,而函数的针对性更强,实际应用中,函数很少对表来作操作,而存储过程经常对表作操作,
- 存储过程可以返回多个值,函数只能有一个返回值,且必须有
- 存储过程一般独立的来执行;而函数可以作为其他SQL语句的一部分出现,和内置函数一样
- 注意事项:
- 存储过程或者自定义函数时需要通过DELIMITER语句修改定界符
- 如果函数体或过程体有多个语句,需要包含在BEGIN…END语句块中
2 MySQL存储引擎
- MySQL可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎
- 每种存储引擎使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能
- MySQL支持的存储引擎
- MyISAM
- InnoDB
- Memory
- CSV
- Archive
2.1 并发处理
当多个连接对记录进行修改时保证记录的一致性和完整性
- 在处理并发读和并发写时,系统会使用一套锁系统来处理:
- 共享锁(读锁):在同一时间段内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化
- 排它锁(写锁):在任何时候只能有一个用户写入资源,当进行写锁时会堵塞其它的读锁或写锁操作
- 锁的力度(锁颗粒),即锁定的时候的单位,只需要对修改的数据精确加锁即可,而无需对所有的数据加锁
- 表锁,是一种开销最小的锁策略,当用户对数据表进行操作时,就获得了这张表的写锁权限,写锁会禁止其他用户进行读写的操作,每张表最多有一个锁,所以开销最小
- 行锁,是一种开销最大的锁策略 ,也是支持最大并发操作的策略。数据表中有多少条记录,都可能对每条记录都有锁,所以开销最大
2.2 事务处理
事务用于保证数据库的完整性,四个特性:原子性(Acomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)
2.3 外键和索引
- 外键: 保证数据一致性的策略
- 索引:对数据表中一列或多列的值进行排序的一种结构,使用索引可以快速访问数据表中的信息,包括普通索引,唯一索引和全文索引等
2.4 各种存储引擎的特点
特点 | MyISAM | InnoDB | Memory | Archine |
---|---|---|---|---|
存储限制 | 256TB | 64TB | 有 | 无 |
事务安全 | - | 支持 | - | - |
支持索引 | 支持 | 支持 | 支持 | - |
锁颗粒 | 表锁 | 行锁 | 表锁 | 行锁 |
数据压缩 | 支持 | - | - | 支持 |
支持外键 | - | 支持 | - | - |
备注:
- Memory将数据存储在内存中,存储的数据量由内存大小决定
- MyISAM:适用于事务的处理不多的情况
- InnoDB:适用于事务处理比较多,需要有外键支持的情况
2.5设置存储引擎
通过修改MySQL的配置文件实现
- default-storage-engine= engine
通过创建数据表命令实现
CREATE TABLE table_name( …… …… )ENGINE = engine;
通过修改数据表命令修改存储引擎
ALTER TABLE table_name ENGINE [=] engine_name;