1. 存储过程定义

存储过程特点：
– 预编译并存储在数据库中
– 可接受参数
– 可返回结果集和输出参数
– 可包含流程控制语句

优势：
– 减少网络传输
– 提高执行效率
– 增强安全性
– 代码复用

劣势：
– 调试困难
– 可移植性差
– 版本控制不便
– 业务逻辑分散

2. 创建存储过程

基本语法：
CREATE PROCEDURE procedure_name([parameters])
BEGIN
— SQL语句
END

创建简单存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE get_all_orders()
BEGIN
SELECT * FROM orders;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 调用存储过程

调用语法：
CALL procedure_name([parameters]);

调用示例：
mysql> CALL get_all_orders();

输出示例：
+—-+———+——–+———————+
| id | status | amount | created_at |
+—-+———+——–+———————+
| 1 | pending | 100.00 | 2026-04-01 10:00:00 |
| 2 | pending | 200.00 | 2026-04-01 11:00:00 |
+—-+———+——–+———————+

4. 查看存储过程

查看存储过程列表：
mysql> SHOW PROCEDURE STATUS WHERE Db = ‘production_db’;

输出示例：
+—————+——————+———–+—————-+———————+———————+—————+———+———————-+———————-+——————–+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+—————+——————+———–+—————-+———————+———————+—————+———+———————-+———————-+——————–+
| production_db | get_all_orders | PROCEDURE | root@localhost | 2026-04-01 10:00:00 | 2026-04-01 10:00:00 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+—————+——————+———–+—————-+———————+———————+—————+———+———————-+———————-+——————–+

查看存储过程定义：
mysql> SHOW CREATE PROCEDURE get_all_orders\G

输出示例：
*************************** 1. row ***************************
Procedure: get_all_orders
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all_orders`()
BEGIN
SELECT * FROM orders;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_general_ci

5. 删除存储过程

删除存储过程：
mysql> DROP PROCEDURE IF EXISTS get_all_orders;

输出示例：
Query OK, 0 rows affected (0.00 sec)

6. 存储过程信息

从information_schema查询：
mysql> SELECT
ROUTINE_NAME,
ROUTINE_TYPE,
DTD_IDENTIFIER,
ROUTINE_DEFINITION
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = ‘production_db’;

输出示例：
+——————+————–+—————-+——————–+
| ROUTINE_NAME | ROUTINE_TYPE | DTD_IDENTIFIER | ROUTINE_DEFINITION |
+——————+————–+—————-+——————–+
| get_all_orders | PROCEDURE | NULL | BEGIN SELECT * … |
+——————+————–+—————-+——————–+

1. 参数类型

参数模式：
– IN：输入参数（默认）
– OUT：输出参数
– INOUT：输入输出参数

创建带参数的存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE get_order_by_id(
IN p_order_id INT,
OUT p_status VARCHAR(20),
OUT p_amount DECIMAL(10,2)
)
BEGIN
SELECT status, amount INTO p_status, p_amount
FROM orders WHERE id = p_order_id;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

调用带参数的存储过程：
mysql> CALL get_order_by_id(1, @status, @amount);
mysql> SELECT @status, @amount;

输出示例：
+———-+———+
| @status | @amount |
+———-+———+
| pending | 100.00 |
+———-+———+

2. 变量声明

局部变量：
mysql> DELIMITER //
mysql> CREATE PROCEDURE variable_demo()
BEGIN
DECLARE v_count INT DEFAULT 0;
DECLARE v_name VARCHAR(100);
DECLARE v_total DECIMAL(10,2) DEFAULT 0.00;

SET v_count = 10;
SET v_name = ‘test’;

SELECT COUNT(*) INTO v_count FROM orders;
SELECT v_count AS total_orders;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

调用：
mysql> CALL variable_demo();

输出示例：
+————-+
| total_orders|
+————-+
| 100 |
+————-+

3. 条件语句

IF语句：
mysql> DELIMITER //
mysql> CREATE PROCEDURE check_order_status(IN p_order_id INT)
BEGIN
DECLARE v_status VARCHAR(20);

SELECT status INTO v_status FROM orders WHERE id = p_order_id;

IF v_status = ‘pending’ THEN
SELECT ‘Order is pending’ AS message;
ELSEIF v_status = ‘processing’ THEN
SELECT ‘Order is processing’ AS message;
ELSE
SELECT ‘Order status: ‘ + v_status AS message;
END IF;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

CASE语句：
mysql> DELIMITER //
mysql> CREATE PROCEDURE get_status_desc(IN p_status VARCHAR(20))
BEGIN
CASE p_status
WHEN ‘pending’ THEN SELECT ‘Waiting for processing’ AS description;
WHEN ‘processing’ THEN SELECT ‘Currently being processed’ AS description;
WHEN ‘completed’ THEN SELECT ‘Order completed’ AS description;
ELSE SELECT ‘Unknown status’ AS description;
END CASE;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 循环语句

WHILE循环：
mysql> DELIMITER //
mysql> CREATE PROCEDURE while_loop_demo(IN p_count INT)
BEGIN
DECLARE v_counter INT DEFAULT 0;
DECLARE v_result INT DEFAULT 0;

WHILE v_counter < p_count DO SET v_result = v_result + v_counter; SET v_counter = v_counter + 1; END WHILE; SELECT v_result AS sum_result; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

REPEAT循环：
mysql> DELIMITER //
mysql> CREATE PROCEDURE repeat_loop_demo(IN p_count INT)
BEGIN
DECLARE v_counter INT DEFAULT 0;
DECLARE v_result INT DEFAULT 0;

REPEAT
SET v_result = v_result + v_counter;
SET v_counter = v_counter + 1;
UNTIL v_counter >= p_count END REPEAT;

SELECT v_result AS sum_result;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

LOOP循环：
mysql> DELIMITER //
mysql> CREATE PROCEDURE loop_demo(IN p_count INT)
BEGIN
DECLARE v_counter INT DEFAULT 0;
DECLARE v_result INT DEFAULT 0;

add_loop: LOOP
SET v_result = v_result + v_counter;
SET v_counter = v_counter + 1;

IF v_counter >= p_count THEN
LEAVE add_loop;
END IF;
END LOOP add_loop;

SELECT v_result AS sum_result;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 游标使用

游标基本用法：
mysql> DELIMITER //
mysql> CREATE PROCEDURE cursor_demo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE v_status VARCHAR(20);
DECLARE v_amount DECIMAL(10,2);

DECLARE order_cursor CURSOR FOR
SELECT id, status, amount FROM orders;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (
order_id INT,
order_status VARCHAR(20),
order_amount DECIMAL(10,2)
);

OPEN order_cursor;

read_loop: LOOP
FETCH order_cursor INTO v_id, v_status, v_amount;

IF done THEN
LEAVE read_loop;
END IF;

INSERT INTO temp_results VALUES (v_id, v_status, v_amount);
END LOOP;

CLOSE order_cursor;

SELECT * FROM temp_results;
DROP TEMPORARY TABLE temp_results;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 系统变量

查看系统变量：
mysql> SHOW VARIABLES LIKE ‘max_sp_recursion_depth’;

输出示例：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| max_sp_recursion_depth | 0 |
+————————+——-+

设置系统变量：
mysql> SET max_sp_recursion_depth = 10;

输出示例：
Query OK, 0 rows affected (0.00 sec)

在存储过程中使用系统变量：
mysql> DELIMITER //
mysql> CREATE PROCEDURE show_system_vars()
BEGIN
SELECT @@max_connections AS max_conns;
SELECT @@innodb_buffer_pool_size AS buffer_pool;
SELECT @@transaction_isolation AS isolation_level;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 会话变量

使用会话变量：
mysql> SET @session_var = ‘test_value’;
mysql> SELECT @session_var;

输出示例：
+————–+
| @session_var |
+————–+
| test_value |
+————–+

在存储过程中使用：
mysql> DELIMITER //
mysql> CREATE PROCEDURE use_session_var()
BEGIN
SET @temp_count = (SELECT COUNT(*) FROM orders);
SELECT @temp_count AS order_count;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 错误处理

声明错误处理器：
mysql> DELIMITER //
mysql> CREATE PROCEDURE error_handling_demo(IN p_id INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@text = MESSAGE_TEXT;

SELECT CONCAT(‘Error: ‘, @errno, ‘ – ‘, @text) AS error_message;
ROLLBACK;
END;

START TRANSACTION;

DELETE FROM orders WHERE id = p_id;

COMMIT;
SELECT ‘Order deleted successfully’ AS message;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

特定错误处理：
mysql> DELIMITER //
mysql> CREATE PROCEDURE specific_error_handler()
BEGIN
DECLARE CONTINUE HANDLER FOR 1062
BEGIN
SELECT ‘Duplicate entry detected’ AS warning;
END;

INSERT INTO orders (id, status, amount) VALUES (1, ‘pending’, 100);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 条件处理

自定义条件：
mysql> DELIMITER //
mysql> CREATE PROCEDURE condition_demo(IN p_amount DECIMAL(10,2))
BEGIN
DECLARE invalid_amount CONDITION FOR SQLSTATE ‘45000’;

IF p_amount <= 0 THEN SIGNAL invalid_amount SET MESSAGE_TEXT = 'Amount must be greater than 0'; END IF; SELECT 'Valid amount' AS result; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

使用SIGNAL：
mysql> DELIMITER //
mysql> CREATE PROCEDURE validate_order(IN p_status VARCHAR(20))
BEGIN
IF p_status NOT IN (‘pending’, ‘processing’, ‘completed’, ‘cancelled’) THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘Invalid order status’,
MYSQL_ERRNO = 1001;
END IF;

SELECT ‘Valid status’ AS result;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 动态SQL

使用PREPARE和EXECUTE：
mysql> DELIMITER //
mysql> CREATE PROCEDURE dynamic_query(IN p_table_name VARCHAR(100))
BEGIN
SET @sql = CONCAT(‘SELECT * FROM ‘, p_table_name, ‘ LIMIT 10’);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

带参数的动态SQL：
mysql> DELIMITER //
mysql> CREATE PROCEDURE dynamic_query_with_param(
IN p_table_name VARCHAR(100),
IN p_column VARCHAR(100),
IN p_value INT
)
BEGIN
SET @sql = CONCAT(‘SELECT * FROM ‘, p_table_name,
‘ WHERE ‘, p_column, ‘ = ?’);
SET @value = p_value;

PREPARE stmt FROM @sql;
EXECUTE stmt USING @value;
DEALLOCATE PREPARE stmt;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 单一职责原则

问题设计：
mysql> DELIMITER //
mysql> CREATE PROCEDURE process_order(IN p_order_id INT)
BEGIN
— 验证订单
— 更新订单状态
— 更新库存
— 发送通知
— 记录日志
— 复杂业务逻辑
END //
mysql> DELIMITER ;

优化设计：
mysql> DELIMITER //
mysql> CREATE PROCEDURE validate_order(IN p_order_id INT, OUT p_valid BOOLEAN)
BEGIN
— 只负责验证
SELECT COUNT(*) > 0 INTO p_valid
FROM orders WHERE id = p_order_id;
END //

mysql> CREATE PROCEDURE update_order_status(
IN p_order_id INT,
IN p_new_status VARCHAR(20)
)
BEGIN
— 只负责更新状态
UPDATE orders SET status = p_new_status WHERE id = p_order_id;
END //

mysql> CREATE PROCEDURE update_inventory(IN p_order_id INT)
BEGIN
— 只负责更新库存
UPDATE products p
JOIN order_items oi ON p.id = oi.product_id
SET p.stock = p.stock – oi.quantity
WHERE oi.order_id = p_order_id;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 参数设计原则

合理的参数设计：
mysql> DELIMITER //
mysql> CREATE PROCEDURE search_orders(
IN p_status VARCHAR(20),
IN p_start_date DATE,
IN p_end_date DATE,
IN p_min_amount DECIMAL(10,2),
IN p_max_amount DECIMAL(10,2),
IN p_page INT,
IN p_page_size INT
)
BEGIN
DECLARE v_offset INT;
SET v_offset = (p_page – 1) * p_page_size;

SELECT * FROM orders
WHERE (p_status IS NULL OR status = p_status)
AND (p_start_date IS NULL OR created_at >= p_start_date)
AND (p_end_date IS NULL OR created_at <= p_end_date) AND (p_min_amount IS NULL OR amount >= p_min_amount)
AND (p_max_amount IS NULL OR amount <= p_max_amount) ORDER BY created_at DESC LIMIT p_page_size OFFSET v_offset; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 返回结果设计

返回多个结果集：
mysql> DELIMITER //
mysql> CREATE PROCEDURE get_order_details(IN p_order_id INT)
BEGIN
— 返回订单基本信息
SELECT * FROM orders WHERE id = p_order_id;

— 返回订单项
SELECT * FROM order_items WHERE order_id = p_order_id;

— 返回订单日志
SELECT * FROM order_log WHERE order_id = p_order_id;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

使用输出参数：
mysql> DELIMITER //
mysql> CREATE PROCEDURE get_order_summary(
IN p_order_id INT,
OUT p_total_items INT,
OUT p_total_amount DECIMAL(10,2),
OUT p_status VARCHAR(20)
)
BEGIN
SELECT COUNT(*), SUM(amount), status
INTO p_total_items, p_total_amount, p_status
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.id = p_order_id;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 事务处理原则

正确的事务处理：
mysql> DELIMITER //
mysql> CREATE PROCEDURE transfer_funds(
IN p_from_account INT,
IN p_to_account INT,
IN p_amount DECIMAL(10,2),
OUT p_success BOOLEAN
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_success = FALSE;
END;

START TRANSACTION;

UPDATE accounts SET balance = balance – p_amount
WHERE id = p_from_account;

UPDATE accounts SET balance = balance + p_amount
WHERE id = p_to_account;

COMMIT;
SET p_success = TRUE;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 命名规范

命名约定：
+——————-+————————+
| 类型 | 命名规则 |
+——————-+————————+
| 存储过程 | sp_功能描述 |
| 输入参数 | p_参数名 |
| 输出参数 | p_参数名 |
| 局部变量 | v_变量名 |
| 游标 | cur_游标名 |
| 处理器 | handler_处理器名 |
+——————-+————————+

示例：
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_get_order_by_status(
IN p_status VARCHAR(20),
OUT p_count INT
)
BEGIN
DECLARE v_total_amount DECIMAL(10,2);

SELECT COUNT(*), SUM(amount)
INTO p_count, v_total_amount
FROM orders WHERE status = p_status;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 减少数据库访问

问题代码：
mysql> DELIMITER //
mysql> CREATE PROCEDURE process_orders_slow()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE cur CURSOR FOR SELECT id FROM orders;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id;
IF done THEN LEAVE read_loop; END IF;

— 每次循环都查询
SELECT * FROM orders WHERE id = v_id;
UPDATE orders SET processed = 1 WHERE id = v_id;
END LOOP;
CLOSE cur;
END //
mysql> DELIMITER ;

优化代码：
mysql> DELIMITER //
mysql> CREATE PROCEDURE process_orders_fast()
BEGIN
— 批量处理
UPDATE orders SET processed = 1 WHERE processed = 0;

SELECT ROW_COUNT() AS affected_rows;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 使用集合操作

问题代码：
mysql> DELIMITER //
mysql> CREATE PROCEDURE update_prices_slow()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE v_price DECIMAL(10,2);
DECLARE cur CURSOR FOR SELECT id, price FROM products;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id, v_price;
IF done THEN LEAVE read_loop; END IF;

UPDATE products SET price = price * 1.1 WHERE id = v_id;
END LOOP;
CLOSE cur;
END //
mysql> DELIMITER ;

优化代码：
mysql> DELIMITER //
mysql> CREATE PROCEDURE update_prices_fast()
BEGIN
UPDATE products SET price = price * 1.1;
SELECT ROW_COUNT() AS affected_rows;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 优化游标使用

问题代码：
mysql> DELIMITER //
mysql> CREATE PROCEDURE cursor_without_batch()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE cur CURSOR FOR SELECT id FROM large_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id;
IF done THEN LEAVE read_loop; END IF;

INSERT INTO log_table (record_id) VALUES (v_id);
END LOOP;
CLOSE cur;
END //
mysql> DELIMITER ;

优化代码：
mysql> DELIMITER //
mysql> CREATE PROCEDURE cursor_with_batch()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_ids TEXT;
DECLARE v_count INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT id FROM large_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id;
IF done THEN
IF v_count > 0 THEN
SET @sql = CONCAT(‘INSERT INTO log_table (record_id) VALUES ‘, v_ids);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
LEAVE read_loop;
END IF;

IF v_count = 0 THEN
SET v_ids = CONCAT(‘(‘, v_id, ‘)’);
ELSE
SET v_ids = CONCAT(v_ids, ‘,(‘, v_id, ‘)’);
END IF;
SET v_count = v_count + 1;

IF v_count >= 1000 THEN
SET @sql = CONCAT(‘INSERT INTO log_table (record_id) VALUES ‘, v_ids);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET v_ids = ”;
SET v_count = 0;
END IF;
END LOOP;
CLOSE cur;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 避免动态SQL

问题代码：
mysql> DELIMITER //
mysql> CREATE PROCEDURE dynamic_query_slow(
IN p_table VARCHAR(100),
IN p_column VARCHAR(100),
IN p_value VARCHAR(100)
)
BEGIN
SET @sql = CONCAT(‘SELECT * FROM ‘, p_table,
‘ WHERE ‘, p_column, ‘ = ”’, p_value, ””);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
mysql> DELIMITER ;

优化代码（使用参数化）：
mysql> DELIMITER //
mysql> CREATE PROCEDURE dynamic_query_fast(
IN p_table VARCHAR(100),
IN p_column VARCHAR(100),
IN p_value VARCHAR(100)
)
BEGIN
SET @sql = CONCAT(‘SELECT * FROM ‘, p_table,
‘ WHERE ‘, p_column, ‘ = ?’);
SET @value = p_value;
PREPARE stmt FROM @sql;
EXECUTE stmt USING @value;
DEALLOCATE PREPARE stmt;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 使用临时表优化

使用临时表存储中间结果：
mysql> DELIMITER //
mysql> CREATE PROCEDURE complex_report()
BEGIN
— 创建临时表存储中间结果
CREATE TEMPORARY TABLE temp_order_summary AS
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY customer_id;

— 使用临时表进行复杂查询
SELECT
c.name,
t.order_count,
t.total_amount
FROM customers c
JOIN temp_order_summary t ON c.id = t.customer_id
ORDER BY t.total_amount DESC;

— 清理临时表
DROP TEMPORARY TABLE temp_order_summary;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 使用SELECT调试

添加调试输出：
mysql> DELIMITER //
mysql> CREATE PROCEDURE debug_demo(IN p_order_id INT)
BEGIN
DECLARE v_status VARCHAR(20);
DECLARE v_amount DECIMAL(10,2);

— 调试输出
SELECT CONCAT(‘Starting process for order: ‘, p_order_id) AS debug;

SELECT status, amount INTO v_status, v_amount
FROM orders WHERE id = p_order_id;

— 调试输出
SELECT CONCAT(‘Order status: ‘, v_status) AS debug;
SELECT CONCAT(‘Order amount: ‘, v_amount) AS debug;

— 业务逻辑
IF v_status = ‘pending’ THEN
UPDATE orders SET status = ‘processing’ WHERE id = p_order_id;
SELECT ‘Order status updated’ AS debug;
END IF;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 使用日志表

创建日志表：
mysql> CREATE TABLE procedure_log (
id INT AUTO_INCREMENT PRIMARY KEY,
procedure_name VARCHAR(100),
log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
log_level VARCHAR(20),
message TEXT
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

在存储过程中记录日志：
mysql> DELIMITER //
mysql> CREATE PROCEDURE logged_procedure(IN p_order_id INT)
BEGIN
DECLARE v_status VARCHAR(20);

— 记录开始
INSERT INTO procedure_log (procedure_name, log_level, message)
VALUES (‘logged_procedure’, ‘INFO’,
CONCAT(‘Processing order: ‘, p_order_id));

— 业务逻辑
SELECT status INTO v_status FROM orders WHERE id = p_order_id;

— 记录中间状态
INSERT INTO procedure_log (procedure_name, log_level, message)
VALUES (‘logged_procedure’, ‘DEBUG’,
CONCAT(‘Order status: ‘, v_status));

— 记录结束
INSERT INTO procedure_log (procedure_name, log_level, message)
VALUES (‘logged_procedure’, ‘INFO’, ‘Procedure completed’);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 错误捕获

完整的错误捕获：
mysql> DELIMITER //
mysql> CREATE PROCEDURE error_capture_demo(IN p_order_id INT)
BEGIN
DECLARE v_error_code CHAR(5) DEFAULT ‘00000’;
DECLARE v_error_msg TEXT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
v_error_code = RETURNED_SQLSTATE,
v_error_msg = MESSAGE_TEXT;

INSERT INTO procedure_log (procedure_name, log_level, message)
VALUES (‘error_capture_demo’, ‘ERROR’,
CONCAT(‘SQLSTATE: ‘, v_error_code, ‘, Message: ‘, v_error_msg));
END;

— 业务逻辑
DELETE FROM orders WHERE id = p_order_id;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 性能分析

使用性能分析：
mysql> DELIMITER //
mysql> CREATE PROCEDURE profile_demo()
BEGIN
— 开启性能分析
SET profiling = 1;

— 执行查询
SELECT * FROM orders WHERE status = ‘pending’;

— 查看性能
SHOW PROFILES;

— 关闭性能分析
SET profiling = 0;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

调用并查看结果：
mysql> CALL profile_demo();

输出示例：
+———-+————+——————————————-+
| Query_ID | Duration | Query |
+———-+————+——————————————-+
| 1 | 0.00012300 | SELECT * FROM orders WHERE status = … |
+———-+————+——————————————-+

5. 使用条件编译

调试模式开关：
mysql> DELIMITER //
mysql> CREATE PROCEDURE conditional_debug(
IN p_order_id INT,
IN p_debug BOOLEAN
)
BEGIN
DECLARE v_status VARCHAR(20);

SELECT status INTO v_status FROM orders WHERE id = p_order_id;

— 条件调试输出
IF p_debug THEN
SELECT CONCAT(‘Order ID: ‘, p_order_id) AS debug_info;
SELECT CONCAT(‘Status: ‘, v_status) AS debug_info;
END IF;

— 业务逻辑
UPDATE orders SET processed = 1 WHERE id = p_order_id;

IF p_debug THEN
SELECT ‘Update completed’ AS debug_info;
END IF;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 存储过程模板

标准模板：
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_template(
IN p_param1 INT,
OUT p_result INT
)
COMMENT ‘存储过程描述’
BEGIN
— 声明变量
DECLARE v_variable INT DEFAULT 0;

— 错误处理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_result = -1;
END;

— 主逻辑
START TRANSACTION;

— 业务操作

COMMIT;
SET p_result = 1;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 注释规范

完整注释：
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_process_order(
IN p_order_id INT,
OUT p_success BOOLEAN
)
COMMENT ‘
功能：处理订单
作者：DBA
日期：2026-04-01
参数：
p_order_id – 订单ID
p_success – 处理结果
返回：无
示例：CALL sp_process_order(1, @result);
‘
BEGIN
— 步骤1：验证订单
DECLARE v_status VARCHAR(20);

— 步骤2：获取订单状态
SELECT status INTO v_status
FROM orders WHERE id = p_order_id;

— 步骤3：更新订单
UPDATE orders SET status = ‘processed’
WHERE id = p_order_id;

SET p_success = TRUE;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 版本控制

创建版本表：
mysql> CREATE TABLE procedure_versions (
id INT AUTO_INCREMENT PRIMARY KEY,
procedure_name VARCHAR(100),
version VARCHAR(20),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
definition TEXT,
author VARCHAR(50),
change_log TEXT
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

记录版本：
mysql> INSERT INTO procedure_versions
(procedure_name, version, definition, author, change_log)
VALUES
(‘sp_process_order’, ‘1.0.0’,
‘CREATE PROCEDURE sp_process_order…’,
‘DBA’, ‘Initial version’);

输出示例：
Query OK, 1 row affected (0.00 sec)

4. 测试规范

创建测试表：
mysql> CREATE TABLE procedure_test_results (
id INT AUTO_INCREMENT PRIMARY KEY,
procedure_name VARCHAR(100),
test_case VARCHAR(100),
expected_result TEXT,
actual_result TEXT,
test_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
passed BOOLEAN
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

测试存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE test_sp_process_order()
BEGIN
DECLARE v_result BOOLEAN;

— 测试用例1
CALL sp_process_order(1, v_result);

INSERT INTO procedure_test_results
(procedure_name, test_case, expected_result, actual_result, passed)
VALUES
(‘sp_process_order’, ‘valid_order’, ‘TRUE’,
IF(v_result, ‘TRUE’, ‘FALSE’), v_result);

— 测试用例2
CALL sp_process_order(99999, v_result);

INSERT INTO procedure_test_results
(procedure_name, test_case, expected_result, actual_result, passed)
VALUES
(‘sp_process_order’, ‘invalid_order’, ‘FALSE’,
IF(v_result, ‘TRUE’, ‘FALSE’), NOT v_result);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 部署流程

部署检查脚本：
mysql> DELIMITER //
mysql> CREATE PROCEDURE deploy_procedure(
IN p_procedure_name VARCHAR(100),
IN p_definition TEXT
)
BEGIN
DECLARE v_exists INT DEFAULT 0;

— 检查是否存在
SELECT COUNT(*) INTO v_exists
FROM information_schema.ROUTINES
WHERE ROUTINE_NAME = p_procedure_name;

— 备份旧版本
IF v_exists > 0 THEN
INSERT INTO procedure_versions
(procedure_name, version, definition, author, change_log)
SELECT
ROUTINE_NAME,
‘backup’,
ROUTINE_DEFINITION,
‘system’,
‘Pre-deployment backup’
FROM information_schema.ROUTINES
WHERE ROUTINE_NAME = p_procedure_name;
END IF;

— 执行新定义
SET @sql = p_definition;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT ‘Deployment completed’ AS status;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 权限控制

查看存储过程权限：
mysql> SELECT
Routine_name,
Definer,
Security_type
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = ‘production_db’;

输出示例：
+——————+—————-+—————+
| Routine_name | Definer | Security_type |
+——————+—————-+—————+
| sp_process_order | root@localhost | DEFINER |
+——————+—————-+—————+

创建带权限的存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_secure_query()
SQL SECURITY DEFINER
COMMENT ‘使用定义者权限执行’
BEGIN
SELECT * FROM sensitive_data;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

使用INVOKER权限：
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_user_query()
SQL SECURITY INVOKER
COMMENT ‘使用调用者权限执行’
BEGIN
SELECT * FROM user_data WHERE user_id = CURRENT_USER();
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. SQL注入防护

问题代码（存在注入风险）：
mysql> DELIMITER //
mysql> CREATE PROCEDURE unsafe_query(IN p_name VARCHAR(100))
BEGIN
SET @sql = CONCAT(‘SELECT * FROM users WHERE name = ”’, p_name, ””);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
mysql> DELIMITER ;

安全代码（参数化查询）：
mysql> DELIMITER //
mysql> CREATE PROCEDURE safe_query(IN p_name VARCHAR(100))
BEGIN
SELECT * FROM users WHERE name = p_name;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

动态SQL安全处理：
mysql> DELIMITER //
mysql> CREATE PROCEDURE safe_dynamic_query(
IN p_table VARCHAR(100),
IN p_column VARCHAR(100),
IN p_value VARCHAR(100)
)
BEGIN
— 验证表名和列名
IF p_table NOT IN (‘orders’, ‘customers’, ‘products’) THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Invalid table name’;
END IF;

IF p_column NOT IN (‘id’, ‘name’, ‘status’) THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Invalid column name’;
END IF;

— 使用参数化
SET @sql = CONCAT(‘SELECT * FROM ‘, p_table,
‘ WHERE ‘, p_column, ‘ = ?’);
SET @value = p_value;
PREPARE stmt FROM @sql;
EXECUTE stmt USING @value;
DEALLOCATE PREPARE stmt;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 敏感数据处理

数据脱敏：
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_get_user_info(IN p_user_id INT)
BEGIN
SELECT
id,
name,
CONCAT(LEFT(phone, 3), ‘****’, RIGHT(phone, 4)) AS phone,
CONCAT(LEFT(email, 2), ‘***@’,
SUBSTRING(email, LOCATE(‘@’, email) + 1)) AS email
FROM users WHERE id = p_user_id;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 审计日志

创建审计表：
mysql> CREATE TABLE procedure_audit_log (
id INT AUTO_INCREMENT PRIMARY KEY,
procedure_name VARCHAR(100),
caller_user VARCHAR(100),
caller_host VARCHAR(100),
call_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
parameters TEXT,
execution_time_ms INT,
status VARCHAR(20)
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

带审计的存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_audited_operation(IN p_order_id INT)
BEGIN
DECLARE v_start_time TIMESTAMP;
DECLARE v_status VARCHAR(20) DEFAULT ‘success’;

SET v_start_time = NOW();

— 记录调用
INSERT INTO procedure_audit_log
(procedure_name, caller_user, caller_host, parameters)
VALUES
(‘sp_audited_operation’, CURRENT_USER(),
SUBSTRING_INDEX(CURRENT_USER(), ‘@’, -1),
CONCAT(‘p_order_id=’, p_order_id));

— 业务逻辑
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET v_status = ‘error’;

UPDATE procedure_audit_log
SET status = v_status,
execution_time_ms = TIMESTAMPDIFF(MICROSECOND, v_start_time, NOW()) / 1000
WHERE id = LAST_INSERT_ID();
END;

UPDATE orders SET status = ‘processed’ WHERE id = p_order_id;
END;

— 更新审计记录
UPDATE procedure_audit_log
SET status = v_status,
execution_time_ms = TIMESTAMPDIFF(MICROSECOND, v_start_time, NOW()) / 1000
WHERE id = LAST_INSERT_ID();
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 监控存储过程性能

创建性能监控表：
mysql> CREATE TABLE procedure_performance (
id INT AUTO_INCREMENT PRIMARY KEY,
procedure_name VARCHAR(100),
call_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
execution_time_ms INT,
rows_affected INT,
parameters TEXT
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

带性能监控的存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_monitored_query(IN p_status VARCHAR(20))
BEGIN
DECLARE v_start_time TIMESTAMP;
DECLARE v_rows INT;

SET v_start_time = NOW();

— 执行查询
SELECT * FROM orders WHERE status = p_status;

— 记录性能
SET v_rows = FOUND_ROWS();

INSERT INTO procedure_performance
(procedure_name, execution_time_ms, rows_affected, parameters)
VALUES
(‘sp_monitored_query’,
TIMESTAMPDIFF(MICROSECOND, v_start_time, NOW()) / 1000,
v_rows,
CONCAT(‘p_status=’, p_status));
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查询性能统计：
mysql> SELECT
procedure_name,
COUNT(*) AS call_count,
AVG(execution_time_ms) AS avg_time_ms,
MAX(execution_time_ms) AS max_time_ms,
MIN(execution_time_ms) AS min_time_ms
FROM procedure_performance
GROUP BY procedure_name;

输出示例：
+——————-+————+————-+————-+————-+
| procedure_name | call_count | avg_time_ms | max_time_ms | min_time_ms |
+——————-+————+————-+————-+————-+
| sp_monitored_query| 1000 | 15 | 100 | 5 |
+——————-+————+————-+————-+————-+

2. 定期检查

检查存储过程状态：
mysql> SELECT
ROUTINE_NAME,
ROUTINE_TYPE,
CREATED,
LAST_ALTERED,
DTD_IDENTIFIER
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = ‘production_db’
ORDER BY LAST_ALTERED DESC;

输出示例：
+——————+————–+———————+———————+—————-+
| ROUTINE_NAME | ROUTINE_TYPE | CREATED | LAST_ALTERED | DTD_IDENTIFIER |
+——————+————–+———————+———————+—————-+
| sp_process_order | PROCEDURE | 2026-01-01 10:00:00 | 2026-04-01 12:00:00 | NULL |
+——————+————–+———————+———————+————–