第9章 存储例程，触发器和计划事件 326
9.0 概述 326
9.1 创建复合语句对象 329
mysql> -- 恢复默认分隔符
mysql> DELIMITER ;
mysql>
mysql> DROP FUNCTION IF EXISTS avg_mail_size;
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER $$
mysql> CREATE FUNCTION avg_mail_size(user VARCHAR(8))
-> RETURNS FLOAT READS SQL DATA
-> BEGIN
-> IF user IS NULL THEN
-> RETURN (SELECT AVG(size) FROM mail);
-> ELSE
-> RETURN (SELECT AVG(size) FROM mail WHERE srcuser = user);
-> END IF;
-> END$$
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
mysql> select avg_mail_size(null), avg_mail_size('barb');
+---------------------+-----------------------+
| avg_mail_size(null) | avg_mail_size('barb') |
+---------------------+-----------------------+
| 237387 | 52232 |
+---------------------+-----------------------+
9.2 利用存储函数封装计算 331
DROP FUNCTION IF EXISTS sales_tax_rate;
DELIMITER $$
CREATE FUNCTION sales_tax_rate(state_code char(2))
RETURNS decimal(3, 2) READS SQL DATA
BEGIN
DECLARE rate decimal(3, 2);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET rate = 0;
SELECT tax_rate INTO rate FROM sales_tax_rate WHERE state = state_code;
RETURN rate;
END$$
DELIMITER ;
mysql> DELIMITER ;
mysql> select sales_tax_rate('VT'), sales_tax_rate('NY');
+----------------------+----------------------+
| sales_tax_rate('VT') | sales_tax_rate('NY') |
+----------------------+----------------------+
| 0.00 | 0.09 |
+----------------------+----------------------+
1 row in set (0.02 sec)
mysql> select sales_tax_rate('ZZ');
+----------------------+
| sales_tax_rate('ZZ') |
+----------------------+
| 0.00 |
+----------------------+
1 row in set (0.00 sec)
mysql> select 150*sales_tax_rate('VT'),150* sales_tax_rate('NY');
+--------------------------+---------------------------+
| 150*sales_tax_rate('VT') | 150* sales_tax_rate('NY') |
+--------------------------+---------------------------+
| 0.00 | 13.50 |
+--------------------------+---------------------------+
1 row in set (0.01 sec)
mysql> DROP FUNCTION IF EXISTS sales_tax;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> DELIMITER $$
mysql> CREATE FUNCTION sales_tax(state_code CHAR(2), sales_amount DECIMAL(10, 2))
-> RETURNS DECIMAL(10, 2) READS SQL DATA
-> BEGIN
-> RETURN sales_amount * sales_tax_rate(state_code);
-> END$$
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
mysql> select sales_tax('VT', 150), sales_tax('NY', 150);
+----------------------+----------------------+
| sales_tax('VT', 150) | sales_tax('NY', 150) |
+----------------------+----------------------+
| 0.00 | 13.50 |
+----------------------+----------------------+
1 row in set (0.00 sec)
9.3 通过存储过程返回多个值 333
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS mail_sender_stats$$
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> CREATE PROCEDURE mail_sender_stats(
-> IN sender VARCHAR(64),
-> OUT messages INT,
-> OUT total_size INT,
-> OUT avg_size INT
-> )
-> BEGIN
-> -- Count messages
-> SELECT COUNT(*) INTO messages FROM mail WHERE srcuser = sender;
->
-> -- Calculate total size
-> SELECT SUM(size) INTO total_size FROM mail WHERE srcuser = sender;
->
-> -- Calculate average size (avoid division by zero)
-> IF messages > 0 THEN
-> SET avg_size = total_size / messages;
-> ELSE
-> SET avg_size = 0;
-> END IF;
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> -- Now call the procedure
mysql> CALL mail_sender_stats('barb', @messages, @total_size, @avg_size);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @messages, @total_size, @avg_size;
+-----------+-------------+-----------+
| @messages | @total_size | @avg_size |
+-----------+-------------+-----------+
| 3 | 156696 | 52232 |
+-----------+-------------+-----------+
1 row in set (0.00 sec)
9.4 利用触发器动态设置列的默认值 334
DELIMITER $$
-- 先删除已存在的存储过程（虽然你命名的是表）
DROP PROCEDURE IF EXISTS cust_invoice$$
-- 创建表（单独执行）
CREATE TABLE cust_invoice(
id INT NOT NULL AUTO_INCREMENT,
state CHAR(2),
amount DECIMAL(10, 2),
tax_rate DECIMAL(3, 2),
PRIMARY KEY (id)
$$
-- 然后创建触发器（单独执行）
CREATE TRIGGER bi_cust_invoice
BEFORE INSERT ON cust_invoice
FOR EACH ROW
BEGIN
SET NEW.tax_rate = sales_tax_rate(NEW.state);
END$$
DELIMITER ;
关键修正点：
分开执行：CREATE TABLE 和 CREATE TRIGGER 是两个独立操作，不能嵌套
语法结构：触发器定义需要完整的 BEGIN...END 块
依赖关系：确保 sales_tax_rate() 函数已存在
mysql> insert into cust_invoice(state, amount) values('NY', 100);
Query OK, 1 row affected (0.02 sec)
mysql> select * from cust_invoice where id= last_insert_id();
+----+-------+--------+----------+
| id | state | amount | tax_rate |
+----+-------+--------+----------+
| 1 | NY | 100.00 | 0.09 |
+----+-------+--------+----------+
1 row in set (0.00 sec)
9.5 利用触发器模拟基于函数的索引 336
-- 先创建表（已完成）
mysql> CREATE TABLE expdata(
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> value FLOAT,
-> log10_value FLOAT,
-> INDEX(value),
-> INDEX(log10_value)
-> );
->
-> -- 设置分隔符
-> DELIMITER $$
->
-> -- 创建 INSERT 触发器
-> CREATE TRIGGER bi_expdata
-> BEFORE INSERT ON expdata
-> FOR EACH ROW
-> BEGIN
-> SET NEW.log10_value = LOG10(NEW.value);
-> END$$
->
-> -- 创建 UPDATE 触发器
-> CREATE TRIGGER bu_expdata
-> BEFORE UPDATE ON expdata
-> FOR EACH ROW
-> BEGIN
-> SET NEW.log10_value = LOG10(NEW.value);
-> END$$
->
-> -- 恢复分隔符
-> DELIMITER ;
mysql> insert into expdata(value) values (.01), (.1), (1), (10), (100);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from expdata;
+----+-------+-------------+
| id | value | log10_value |
+----+-------+-------------+
| 1 | 0.01 | -2 |
| 2 | 0.1 | -1 |
| 3 | 1 | 0 |
| 4 | 10 | 1 |
| 5 | 100 | 2 |
+----+-------+-------------+
5 rows in set (0.00 sec)
mysql> update expdata set value = value * 10;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from expdata;
+----+-------+-------------+
| id | value | log10_value |
+----+-------+-------------+
| 1 | 0.1 | -1 |
| 2 | 1 | 0 |
| 3 | 10 | 1 |
| 4 | 100 | 2 |
| 5 | 1000 | 3 |
+----+-------+-------------+
5 rows in set (0.00 sec)
9.6 在其他日期和时间类型中模拟TIMESTAMP属性 339
mysql> select * from ts_emulate;
+---------+------------+----------+---------------------+
| data | d | t | dt |
+---------+------------+----------+---------------------+
| axolot1 | 2025-07-15 | 21:40:48 | 2025-07-15 21:40:48 |
| dog | 2025-07-15 | 21:40:59 | 2025-07-15 21:40:59 |
+---------+------------+----------+---------------------+
2 rows in set (0.01 sec)
mysql> update ts_emulate set data = data;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql> select * from ts_emulate;
+---------+------------+----------+---------------------+
| data | d | t | dt |
+---------+------------+----------+---------------------+
| axolot1 | 2025-07-15 | 21:40:48 | 2025-07-15 21:40:48 |
| dog | 2025-07-15 | 21:40:59 | 2025-07-15 21:40:59 |
+---------+------------+----------+---------------------+
2 rows in set (0.00 sec)
9.7 利用触发器记录变更 341
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> update auction set bid = 7.50 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update auction set bid = 9.00 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update auction set bid = 10.00 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from auction where id = 1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from auction_log where id = 1 order by ts;
+--------+----+---------------------+----------------+-------+
| action | id | ts | item | bid |
+--------+----+---------------------+----------------+-------+
| delete | 1 | NULL | chintz pillows | 10.00 |
| create | 1 | 2025-07-16 20:33:13 | chintz pillows | 5.00 |
| update | 1 | 2025-07-16 20:37:51 | chintz pillows | 7.50 |
| update | 1 | 2025-07-16 20:37:51 | chintz pillows | 9.00 |
| update | 1 | 2025-07-16 20:37:51 | chintz pillows | 10.00 |
+--------+----+---------------------+----------------+-------+
5 rows in set (0.00 sec)
9.8 使用事件来计划数据库操作 344
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS exec_stmt$$
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> CREATE PROCEDURE exec_stmt(IN stmt_str TEXT)
-> BEGIN
-> SET @_stmt_str = stmt_str;
-> PREPARE stmt FROM @_stmt_str;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql> -- 设置变量
mysql> SET @tb1_name = 'test_table';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @val = 123; -- 设置要插入的值
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> -- 创建表
mysql> CALL exec_stmt(CONCAT('CREATE TABLE IF NOT EXISTS ', @tb1_name, '(i INT)'));
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> -- 插入数据（注意正确的INSERT语法）
mysql> CALL exec_stmt(CONCAT('INSERT INTO ', @tb1_name, '(i) VALUES(', @val, ')'));
Query OK, 0 rows affected (0.01 sec)
注意事项
1. 安全性：这种动态SQL容易受SQL注入攻击，不要用于用户输入
2. 错误处理：建议在存储过程中添加错误处理
3. 调试：可以先SELECT查看生成的SQL语句是否正确
9.9 编写辅助例程动态执行SQL 346
```sql
mysql> DROP FUNCTION IF EXISTS quote_identifier;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> DELIMITER $$
mysql> CREATE FUNCTION quote_identifier(id TEXT)
-> RETURNS TEXT DETERMINISTIC
-> RETURN CONCAT('`', REPLACE(id, '`', '``'), '`')$$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS create_and_insert$$
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> CREATE PROCEDURE create_and_insert(
-> IN tb_name TEXT,
-> IN val INT
-> )
-> BEGIN
-> -- 引用标识符
-> SET @quoted_tb_name = quote_identifier(tb_name);
-> SET @quoted_val = QUOTE(val);
->
-> -- 创建表
-> SET @create_sql = CONCAT('CREATE TABLE IF NOT EXISTS ', @quoted_tb_name, ' (i INT)');
-> CALL exec_stmt(@create_sql);
->
-> -- 插入数据
-> SET @insert_sql = CONCAT('INSERT INTO ', @quoted_tb_name, ' (i) VALUES (', val, ')');
-> CALL exec_stmt(@insert_sql);
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql> -- 设置变量
mysql> SET @tb_name = 'test_table';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @val = 123;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> -- 调用存储过程
mysql> CALL create_and_insert(@tb_name, @val);
Query OK, 0 rows affected (0.02 sec)
关键修正点
将函数和过程分开定义
使用参数传递而不是会话变量
修正INSERT语句语法（使用INTO而不是TABLE）
添加IF NOT EXISTS防止表已存在错误
移除了不必要的QUOTE()调用（数值不需要引号）
9.10. 处理存储程序中的错误 347
mysql> DROP procedure IF EXISTS us_population$$
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> CREATE procedure us_population()
-> BEGIN
-> declare done boolean default false;
-> declare state_pop, total_pop bigint default 0;
-> declare cur cursor for select pop from states;
-> declare continue handler for not found set done= true;
->
-> open cur;
-> fetch_loop: loop
-> fetch cur into state_pop;
-> if done then
-> leave fetch_loop;
-> end if;
-> set total_pop = total_pop + state_pop;
-> end loop;
-> close cur;
-> select total_pop as 'total U.S. Population';
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql> call us_population();
+-----------------------+
| total U.S. Population |
+-----------------------+
| 331223695 |
+-----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> select sum(pop) as 'total U.S.Population' from states;
+----------------------+
| total U.S.Population |
+----------------------+
| 331223695 |
+----------------------+
1 row in set (0.00 sec)
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS drop_user$$
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> CREATE PROCEDURE drop_user(
-> IN username VARCHAR(60),
-> IN hostname VARCHAR(60)
-> )
-> BEGIN
-> -- 检查用户是否存在
-> IF EXISTS (SELECT 1 FROM mysql.user WHERE user = username AND host = hostname) THEN
-> -- 动态生成并执行DROP USER语句
-> SET @sql = CONCAT('DROP USER ''', username, '''@''', hostname, '''');
-> PREPARE stmt FROM @sql;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
-> SELECT CONCAT('用户 ''', username, '''@''', hostname, ''' 已删除') AS message;
-> ELSE
-> SELECT CONCAT('用户 ''', username, '''@''', hostname, ''' 不存在') AS message;
-> END IF;
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql> CALL drop_user('bad_user', 'localhost');
+------------------------------------+
| message |
+------------------------------------+
| 用户 'bad_user'@'localhost' 不存在 |
+------------------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS drop_user_warn$$
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> CREATE PROCEDURE drop_user_warn(
-> IN username VARCHAR(60),
-> IN hostname VARCHAR(60)
-> )
-> BEGIN
-> DECLARE user_exists INT DEFAULT 0;
->
-> -- 检查用户是否存在
-> SELECT COUNT(*) INTO user_exists
-> FROM mysql.user
-> WHERE user = username AND host = hostname;
->
-> -- 根据检查结果执行操作
-> IF user_exists > 0 THEN
-> SET @sql = CONCAT('DROP USER ''', username, '''@''', hostname, '''');
-> PREPARE stmt FROM @sql;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
-> SELECT CONCAT('用户 ''', username, '''@''', hostname, ''' 已成功删除') AS message;
-> ELSE
-> SELECT CONCAT('用户 ''', username, '''@''', hostname, ''' 不存在，无需删除') AS message;
-> END IF;
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql> call drop_user_warn('bad_user', 'localhost');
+----------------------------------------------+
| message |
+----------------------------------------------+
| 用户 'bad_user'@'localhost' 不存在，无需删除 |
+----------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
9.11 利用触发器预处理或拒绝数据 351
mysql> CREATE TABLE cookbook.contact_info (
-> id INT not null AUTO_INCREMENT ,
-> name VARCHAR(100) NOT NULL,
-> state char(2),
-> email VARCHAR(100),
-> url varchar(255),
-> phone VARCHAR(20),
-> primary key (id)
-> );
Query OK, 0 rows affected (0.06 sec)
DELIMITER $$
DROP TRIGGER IF EXISTS bi_contact_info$$
CREATE TRIGGER bi_contact_info BEFORE INSERT ON contact_info
FOR EACH ROW
BEGIN
-- 验证州代码
IF (SELECT COUNT(*) FROM states WHERE abbrev = NEW.state) = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid state code';
END IF;
-- 验证电子邮件格式
IF INSTR(NEW.email, '@') = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid email address';
END IF;
-- 清理URL
SET NEW.url = TRIM(LEADING 'http://' FROM NEW.url);
END$$
DELIMITER ;
主要修正点：
1. SQLSTATE 值：
o 将 hy000 改为标准的 45000（通用错误状态码）
o 注意拼写：sqlstae → SQLSTATE
2. 语法修正：
o 添加了缺失的分号 ; 在 END IF 后
o 修正了 message_text 拼写为 MESSAGE_TEXT
o 修正了 mysql_errno 为正确的 MYSQL_ERRNO（但通常不需要）
3. 逻辑调整：
o 移除了不必要的错误代码设置（1525）
o 使用标准错误消息格式
mysql> -- 只使用有效的美国州缩写
mysql> INSERT INTO contact_info (name, email, phone, state, url)
-> VALUES
-> ('张三', 'zhangsan@example.com', '13800138001', 'NY', 'http://www.zhangsan.com'),
-> ('李四', 'lisi@example.com', '13900139002', 'CA', 'https://lisi.org'),
-> ('王五', 'wangwu@example.com', '13700137003', 'TX', 'http://wangwu.net'),
-> ('赵六', 'zhaoliu@example.com', '13600136004', 'FL', 'https://zhaoliu.me'),
-> ('钱七', 'qianqi@example.com', '13500135005', 'IL', 'http://qianqi.edu');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 验证插入结果
mysql> SELECT * FROM contact_info;
+----+------+-------+----------------------+--------------------+-------------+
| id | name | state | email | url | phone |
+----+------+-------+----------------------+--------------------+-------------+
| 6 | 张三 | NY | zhangsan@example.com | www.zhangsan.com | 13800138001 |
| 7 | 李四 | CA | lisi@example.com | https://lisi.org | 13900139002 |
| 8 | 王五 | TX | wangwu@example.com | wangwu.net | 13700137003 |
| 9 | 赵六 | FL | zhaoliu@example.com | https://zhaoliu.me | 13600136004 |
| 10 | 钱七 | IL | qianqi@example.com | qianqi.edu | 13500135005 |
+----+------+-------+----------------------+--------------------+-------------+
5 rows in set (0.00 sec)