当前位置：首页 > ds >正文

板凳-------Mysql cookbook学习（十二--------1)

ds 2025/7/18 8:14:37

第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
BEGINDECLARE 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
BEGINSET 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> update auction set bid = 9.00 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> update auction set bid = 10.00 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> 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 THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = 'Invalid state code';END IF;-- 验证电子邮件格式IF INSTR(NEW.email, '@') = 0 THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = 'Invalid email address';END IF;-- 清理URLSET 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: 0mysql>
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)