MySQL Stored Procedure

一月的时候重构了一个洗数据的代码,在 Jupyter 上用 ipython-sql 写的 sql,数据库用的 MySQL 8。

理论上更高效的做法应该是直接读到 dataframe 里再进行数据清洗,由于之前已经用 sql 完整实现了一遍再加上时间不是很充裕选择了用 Stored Procedure 来封装之前的 sql

由于大量的使用了表名作为参数,因此用 concat 函数来拼接,基本的框架就是

1
2
3
4
5
6
7
DROP PROCEDURE IF EXISTS initTable
CREATE PROCEDURE initTable(IN tb1 VARCHAR(5), IN tb2 VARCHAR(5))
BEGIN
SET @sqlstmt = CONCAT('SELECT * FROM', tb1, '', tb2, ' LIMIT 10');
PREPARE stmt FROM @sqlstmt
EXECUTE stmt
END

然后通过 call 调用

1
CALL initTable('tb', 'anothertb')

但是在同一个 procedure 里对一张表进行多次 update 的话则会出现 can’t lock table 的报错。可以利用 transaction 解决。而 transaction 在 mysql 语法中有点特殊,不是 BEGIN TRAN/COMMIT TRAN 这种形式。

假设要 update 同一张表两次:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP PROCEDURE IF EXISTS updateTable
CREATE PROCEDURE updateTable(IN tb VARCHAR(5))
BEGIN
START TRANSACTION
SET @sqlstmt = CONCAT('update', tb,'SET A = 1');
PREPARE stmt FROM @sqlstmt
EXECUTE stmt
COMMIT

START TRANSACTION
SET @sqlstmt = CONCAT('update', tb,'SET B = 2');
PREPARE stmt FROM @sqlstmt
EXECUTE stmt
COMMIT
END

(一开始还以为 mysql 不支持 transaction