MySQL 数据库编程知识点总结

MySQL 数据库编程主要涉及到三个知识点：存储过程、函数、触发器。其中最重要、最常用的就是存储过程，编写存储过程所涉及到的变量定义、流程控制、循环遍历、游标操作等语句，同样适用于函数和触发器，因此本篇博客重点进行存储过程的总结，对于函数和触发器仅进行简单的介绍。

MySQL 的存储过程类似于编程语言中的方法，封装了一些通用处理逻辑，其本质上是经过编译并存储在数据库中的一段 SQL 语句的集合，可以进行重复调用，优点就是减少应用服务器与数据库服务器之间的网络传输流量，相比 SQL 语句每次传递都要进行编译执行来说，其数据处理执行效率和性能大大提高。

一、存储过程快速入门

MySQL 的 SQL 语句默认都是以分号（;）结束，但是存储过程是多个 SQL 语句的集合，不能遇到分号就认为存储过程编写结束了，因此我们必须使用 DELIMITER 关键字指定新的结束标识符，等存储过程编写完毕后，再将结束标识符恢复为分号。

-- MySQL 存储过程编写语法格式如下：
-- 修改分隔符为$$ ，分隔符可以随意指定
DELIMITER $$

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
	sql语句;
END$$

-- 修改分隔符为分号
DELIMITER ;

-----------------------

-- MySQL 存储过程编写示例：（计算两个数字之和）
DELIMITER $$

CREATE PROCEDURE addtest(IN num1 int,IN num2 int)
BEGIN
	/* 注意：MySQL 中所有的 DECLARE 定义变量的语句，
	必须写在存储过程的最上面，否则创建存储过程时会报错 */
	DECLARE result INT DEFAULT 0;

	SET result = num1 + num2;
	SELECT result;
END$$

DELIMITER ;

-----------------------

-- 调用存储过程
CALL addtest(100,200);

-- 查看一个数据库中有哪些存储过程，语法格式为：
-- SELECT * FROM mysql.proc WHERE db='数据库名称' AND TYPE='PROCEDURE';
SELECT * FROM mysql.proc WHERE db='testdb' AND TYPE='PROCEDURE';

-- 删除存储过程，语法格式为：
-- DROP PROCEDURE IF EXISTS 存储过程名称;
DROP PROCEDURE IF EXISTS addtest;

MySQL 没有提供修改存储过程的语法，如果想要修改存储过程，其实就是先删除原来的存储过程，然后再创建新的存储过程。

二、定义变量并赋值

-- 结束符可以随便定义，这个示例使用 // 作为存储过程结束符号
DELIMITER //

ALTER PROCEDURE vartest()
BEGIN
	/* 注意：MySQL 中所有的 DECLARE 定义变量的语句，
	必须写在存储过程的最上面，否则创建存储过程时会报错 */

	-- 定义一个变量，并赋默认值
	DECLARE vardefault INT DEFAULT 0;
	-- 定义一个变量
	DECLARE vartemp VARCHAR(50);
	-- 同时定义两个变量
	DECLARE male,female INT;

	-- 第一种变量赋值方式：通过 Set 给变量赋值
	SET vartemp = '心想事成';

	-- 第二种变量赋值方式：通过 select into 给变量赋值
	SELECT AVG(salary) INTO male FROM users WHERE gender = '男';
	SELECT AVG(salary) INTO female FROM users WHERE gender = '女';

	-- 查询出各个变量的值
	SELECT vardefault,vartemp,male,female;
END//

DELIMITER ;

-----------------------

-- 调用存储过程
CALL vartest();

三、IF语句的使用 和 存储过程参数传递

IF 流程控制语句是使用最频繁的 SQL 编程语句，其语法格式如下：

IF 判断条件1 THEN
	执行的sql语句1;
ELSEIF 判断条件2 THEN
	执行的sql语句2;
...
ELSE
	执行的sql语句n;
END IF;

MySQL 的存储过程一共有 3 种类型的参数：

• IN 代表输入参数，需要由调用者传递实际数据（这个是默认的参数类型）
• OUT 代表输出参数，存储过程运行期间给该类型的参数赋值，可以作为返回值
• INOUT 代表既可以作为输入参数，也可以作为输出参数

对于 OUT 和 INOUT 这两种类型的参数，一般很少使用。这里仅以 IN 和 OUT 两种类型的参数进行代码示例：

-- IN 类型参数代码示例，其中 IN 这个关键字可以省略
DELIMITER $$

CREATE PROCEDURE salarytest(IN uid INT)
BEGIN
	/* 注意：MySQL 中所有的 DECLARE 定义变量的语句，
	必须写在存储过程的最上面，否则创建存储过程时会报错 */
	DECLARE num INT;
	DECLARE msg VARCHAR(50);

	SELECT salary INTO num FROM users WHERE user_id = uid;

	IF num >= 30000 THEN 
		SET msg = '薪资很高';
	ELSEIF num >= 20000 THEN 
		SET msg = '薪资中等';
	ELSEIF num >= 10000 THEN 
		SET msg = '薪资还行';
	ELSE 
		SET msg = '薪资偏低';
	END IF;

	SELECT num,msg;
END$$

DELIMITER ;

-----------------------

-- 调用存储过程，查询 user_id 为 3 的员工薪资水平
CALL salarytest(3);

-- OUT 类型参数代码示例
DELIMITER //

CREATE PROCEDURE salarymsg(IN uid INT, OUT msg VARCHAR(50))
BEGIN
	DECLARE num INT;

	SELECT salary INTO num FROM users WHERE user_id = uid;

	IF num >= 30000 THEN
		SET msg = '薪资很高';
	ELSEIF num >= 20000 THEN 
		SET msg = '薪资中等';
	ELSEIF num >= 10000 THEN 
		SET msg = '薪资还行';
	ELSE 
		SET msg = '薪资偏低';
	END IF;
END//

DELIMITER ;

-----------------------

-- 调用存储过程，查询 user_id 为 3 的员工薪资水平，将结果存储到 OUT 类型变量中
-- 注意：OUT 类型的参数传递格式必须为：@变量名称
-- 单个 @ 开头的变量为会话变量，在一次数据库连接访问整个过程中都有效
-- 两个 @ 开头的变量是 MySQL 的系统变量
CALL salarymsg(3,@result);

-- 查看存储过程返回的 OUT 类型参数的结果
SELECT @result AS '薪资情况';

四、case 语句的使用

case 语句有两种编写格式：

-- 第一种编写格式如下：
CASE 表达式
WHEN 值1 THEN 执行sql语句1;
WHEN 值2 THEN 执行sql语句2;
...
ELSE 执行sql语句n;
END CASE;

-----------------------

-- 代码示例（传入奖品的等级，获取奖品内容）
DELIMITER $$

CREATE PROCEDURE casetest1(IN award INT)
BEGIN
	DECLARE result VARCHAR(100);

	CASE award
	WHEN 1 THEN
		SET result = '一等奖：1200万元的迈巴赫';
	WHEN 2 THEN
		SET result = '二等奖：800万元的劳斯莱斯';
	WHEN 3 THEN
		SET result = '三等奖：300万元的宝马';
	ELSE
		SET result = '阳光普照奖：20元的红包';
	END CASE;

	SELECT result;
END$$

DELIMITER ;

-----------------------

-- 调用存储过程
CALL casetest1(1);

-- 第二种编写格式如下：
CASE
WHEN 判断条件1 THEN 执行sql语句1;
WHEN 判断条件2 THEN 执行sql语句2;
...
ELSE 执行sql语句n;
END CASE;

-----------------------

-- 代码示例
DELIMITER //

CREATE PROCEDURE casetest2(IN uid INT)
BEGIN
	DECLARE num INT;
	DECLARE msg VARCHAR(50);

	SELECT salary INTO num FROM users WHERE user_id = uid;

	CASE
	WHEN num >= 30000 THEN 
		SET msg = '薪资很高';
	WHEN num >= 20000 THEN 
		SET msg = '薪资中等';
	WHEN num >= 10000 THEN 
		SET msg = '薪资还行';
	ELSE
		SET msg = '薪资偏低';
	END CASE;

	SELECT num,msg;
END//

DELIMITER ;

-----------------------

-- 调用存储过程，查询 user_id 为 8 的员工薪资水平
CALL casetest2(8);

五、三种循环遍历

MySQL 支持三种循环遍历：While 循环、Repeat 循环、Loop 循环。最常用的是 While 循环即可，因为其编写语法是最简单的，对于 Repeat 循环和 Loop 循环只需要了解即可，在实际场景中很少使用。

-- While 循环的语法格式如下：
初始化语句;
WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;

-----------------------

-- 代码示例（计算 1 到 100 的偶数的和）
DELIMITER $$

CREATE PROCEDURE whiletest()
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE num INT DEFAULT 1;

	WHILE num <= 100 DO

		IF num % 2 = 0 THEN
			SET result = result + num;
		END IF;

		SET num = num + 1;
	END WHILE;

	SELECT result;
END$$

DELIMITER ;

-----------------------

-- 调用存储过程
CALL whiletest();

-- Repeat 和 While 的区别为：
-- Repeat 是当条件满足时就【停止】循环
-- While 是当条件满足时就【执行】循环

-- Repeat 循环的语法格式如下：
初始化语句;
REPEAT
	循环体语句;
	条件控制语句;
	UNTIL 条件判断语句  -- 注意这句代码后面不能写分号
END REPEAT;

-----------------------

-- 代码示例（计算 1 到 100 的数字之和）
DELIMITER //

CREATE PROCEDURE repeattest()
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE num INT DEFAULT 1;

	REPEAT

		SET result = result + num;
		SET num = num + 1;

		-- 注意这句代码后面不能写分号
		UNTIL num > 100
	END REPEAT;

	SELECT result;
END//

DELIMITER ;

-----------------------

-- 调用存储过程
CALL repeattest();

-- Loop 循环需要通过 LEAVE 关键字终止循环
-- Loop 循环的语法格式如下：
初始化语句;
循环名称: LOOP
	条件判断语句
		LEAVE 循环名称;
	循环体语句;
	条件控制语句;
END LOOP 循环名称;

-----------------------

-- 代码示例（计算 1 到 50 的数字之和）
DELIMITER $$

CREATE PROCEDURE looptest()
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE num INT DEFAULT 1;

	myloop: LOOP

		IF num > 50 THEN
			LEAVE myloop;
		END IF;

		SET result = result + num;

		SET num = num + 1;
	END LOOP myloop;

	SELECT result;
END$$

DELIMITER ;

-----------------------

-- 调用存储过程
CALL looptest();

六、游标的使用

游标的主要用途是遍历循环处理从数据库中获取的每一行数据并进行处理，类似于 Java 或 C# 语言中的迭代器。

游标的使用主要分为 4 个步骤：定义游标、打开游标、获取每一行数据并进行处理，关闭游标。

使用游标处理数据，并不是批量处理数据，而是逐行处理数据，因此相对于批量处理数据而言，性能不高。

-- 游标代码示例
-- 获取所有的员工数据，给每个男员工薪资增加 1000 元，给每个女员工薪资增加 2000 元
DELIMITER //

CREATE PROCEDURE cursortest()
BEGIN
	-- 定义变量，用于临时存储从游标中获取的数据
	DECLARE uid INT;
	DECLARE sex VARCHAR(10);

	-- 定义游标结束的标记，初始化值为 0
	DECLARE flag INT DEFAULT 0;

	-- 定义游标
	DECLARE mycursor CURSOR FOR SELECT user_id,gender FROM users;

	-- 定义退出处理程序，当获取不到数据时，将 flag 值设置为 1
	DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

	-- 打开游标
	OPEN mycursor;
	-- 先尝试获取一条数据，如果获取到了，flag 值仍然为 0，反之则 flag 值会被设置为 1
	FETCH mycursor INTO uid,sex;

	-- 遍历循环 users 表中的每一行数据，并根据性别修改本行中的薪资数据
	WHILE flag = 0 DO

		IF sex = '男' THEN
			-- 给每个男员工的薪资增加 1000 元
			UPDATE users SET salary = salary + 1000 WHERE user_id = uid;
		ELSEIF sex = '女' THEN
			-- 给每个女员工的薪资增加 2000 元
			UPDATE users SET salary = salary + 2000 WHERE user_id = uid;
		END IF;

		FETCH mycursor INTO uid,sex;
	END WHILE;

	-- 关闭游标
	CLOSE mycursor;
END//

DELIMITER ;

-----------------------

-- 调用存储过程
CALL cursortest();

七、函数的使用

函数跟存储过程的使用方式很相似，函数能够做到的，存储过程都能做到。

函数和存储过程的区别在于：

• 函数必须要有返回值，存储过程可以有返回值，也可以没有。
• 函数的参数只能是输入型参数，存储过程的参数可以是输入型，也可以是输出型。
• 调用函数使用 Select 函数名(参数) ，调用存储过程使用 Call 存储过程名(参数) 。

在函数中同样可以使用变量定义、流程控制、循环遍历、游标操作等编程语句，因此这里就不详细介绍函数了。

-- 创建函数的语法格式如下：
DELIMITER $$

CREATE FUNCTION 函数名称(参数 数据类型,....)
RETURNS 返回值类型
BEGIN
	执行的sql语句;
	RETURN 结果;
END$$

DELIMITER ;

-----------------------

-- 函数代码示例（计算从 1 到 total 的数字之和）
DELIMITER $$

CREATE FUNCTION functiontest(total int)
RETURNS INT
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE num INT DEFAULT 1;

	WHILE num <= total DO

		SET result = result + num;

		SET num = num + 1;
	END WHILE;

	RETURN result;
END$$

DELIMITER ;

-----------------------

-- 调用函数，计算 1 到 100 的数字之和
SELECT functiontest(100);

-- 查看一个数据库中有哪些函数，语法格式为：
-- SELECT * FROM mysql.proc WHERE db='数据库名称' AND TYPE='FUNCTION';
SELECT * FROM mysql.proc WHERE db='testdb' AND TYPE='FUNCTION';

-- 删除函数，语法格式为：
-- DROP FUNCTION IF EXISTS 函数名称;
DROP FUNCTION IF EXISTS functiontest;

八、触发器的使用

触发器是与数据库表有关的数据库对象，可以在 insert / update / delete 之前或之后，触发并执行触发器中定义的SQL语句，主要用来协助程序在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。MySQL 使用别名 old 和 new 来引用触发器中发生变化前后的数据行。触发器只支持行级触发，不支持语句级触发。

触发器类型	old 的含义	new 的含义
INSERT 型触发器	无 (因为插入前状态无数据)	表示将要或者已经新增的数据
UPDATE 型触发器	表示修改之前的数据	表示将要或已经修改后的数据
DELETE 型触发器	表示将要或者已经删除的数据	无 (因为删除后状态无数据)

-- 创建触发器的语法如下：
DELIMITER $$

CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]
BEGIN
	触发器要执行的功能;
END$$

DELIMITER ;

下面我们对员工表进行增删改操作，以记录操作日志为例，演示触发器的使用。

/*
我们对 employee 员工表进行增删改操作，该表只有 3 个字段：
id（员工id）
name（员工姓名）
money（员工薪水）

把操作日志记录到 employee_log 表中，该表有 5 个字段：
log_id（日志id，自增长）
log_type（操作类型：insert / update / delete）
employee_id（被操作的员工id）
log_time（操作时间）
log_content（操作前后的 employee 记录的完整数据内容）
*/

-----------------------

-- 创建 INSERT 触发器
DELIMITER //

CREATE TRIGGER insert_trigger
AFTER INSERT
ON employee
FOR EACH ROW
BEGIN
	INSERT INTO employee_log(log_type, employee_id, log_time, log_content)
	VALUES ('INSERT', new.id, NOW(),
	CONCAT('新增后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END//

DELIMITER ;

-----------------------

-- 创建 UPDATE 触发器
DELIMITER //

CREATE TRIGGER update_trigger
AFTER UPDATE
ON employee
FOR EACH ROW
BEGIN
	INSERT INTO employee_log(log_type, employee_id, log_time, log_content)
	VALUES ('UPDATE', new.id, NOW(),
	CONCAT('修改前{id=',old.id,',name=',old.name,',money=',old.money,'}',
               '修改后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END//

DELIMITER ;

-----------------------

-- 创建 DELETE 触发器
DELIMITER //

CREATE TRIGGER delete_trigger
AFTER DELETE
ON employee
FOR EACH ROW
BEGIN
	INSERT INTO employee_log(log_type, employee_id, log_time, log_content)
	VALUES ('DELETE', old.id, NOW(),
	CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END//

DELIMITER ;

-----------------------

-- 查看具体一个数据库中有哪些触发器
USE testdb;
SHOW TRIGGERS;

-- 删除触发器，语法格式为：
-- DROP TRIGGER IF EXISTS 触发器名称;
DROP TRIGGER IF EXISTS delete_trigger;

OK，本篇博客对 MySQL 的存储过程、函数、触发器的编程使用，做了一个简单总结，方便后续使用时，能够快速查找到。