初涉MySQL操作（四）

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;