点击上方蓝字“数据库干货铺” 解锁更多精彩内容
存储过程可能是很多人都比较喜欢使用的,但MySQL不建议使用存储过程,如果临时用的话可以考虑。
本次简单介绍今天协助一位读者的实现的简单需求的存储过程,其中涉及循环、动态sql等主要的MySQL存储过程知识点。
需求:
遍历所有【test_ondev_[0-9]】开头的表,将其中的所有数据按表中的col字段存储到【test_ondev_history_ + col的值】的表里面,当【test_ondev_history_ + col】不存在的时候复制test_ondev_history的结构创建该表,并将数据存储进表里。
程序思路:
-
遍历表名
-
将每张表的col字段的内容统计出来
-
创建 test_ondev_history_ + col 的表
-
将数据插入进去test_ondev_history_ + col表中
-
继续遍历其他表
使用存储过程实现:
DELIMITER $$
CREATE PROCEDURE `p_copy_data`()
BEGIN
DECLARE i INT;
DECLARE v_sql_col VARCHAR(200);
DECLARE j INT;
DECLARE max_id_s INT;
DECLARE max_id INT;
DECLARE v_tbname VARCHAR(50);
DECLARE v_tbname_s VARCHAR(50);
DECLARE v_col VARCHAR(50);
DECLARE v_sql_create VARCHAR(200);
DECLARE v_sql_insert VARCHAR(200);
CREATE TABLE IF NOT EXISTS temp_col(id INT PRIMARY KEY AUTO_INCREMENT,col VARCHAR(50));
CREATE TABLE IF NOT EXISTS tbnames(id INT PRIMARY KEY AUTO_INCREMENT,tbname VARCHAR(50));
TRUNCATE TABLE tbnames;
INSERT INTO tbnames(tbname)
SELECT table_name FROM information_schema.`TABLES` WHERE table_name REGEXP 'test_ondev_[0-9]';
SET i = (SELECT MIN(id) FROM tbnames);
SET max_id_s = (SELECT MAX(id) FROM tbnames);
WHILE i <=max_id_s
DO
TRUNCATE TABLE temp_col;
SET v_tbname_s = (SELECT tbname FROM tbnames WHERE id = i);
SET v_sql_col = CONCAT('insert into temp_col(col) select distinct col from ',v_tbname_s);
SET @v_sql_col := v_sql_col;
PREPARE stmt FROM @v_sql_col;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
SET j =1;
SET max_id = (SELECT MAX(id) FROM temp_col );
WHILE j <= max_id
DO
SET v_col = (SELECT col FROM temp_col WHERE id =j);
SET v_tbname = CONCAT('test_ondev_history_',v_col );
SET v_sql_create = CONCAT('create table if not exists ',v_tbname,' like test_ondev_history;');
SET @v_sql_create := v_sql_create;
PREPARE stmt FROM @v_sql_create;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
SET v_sql_insert = CONCAT('insert into ',v_tbname,' select * from ',v_tbname_s," where col='", v_col ,"';");
SET @v_sql_insert := v_sql_insert;
PREPARE stmt FROM @v_sql_insert;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
SET j = j + 1;
END WHILE;
SET i = i + 1;
END WHILE;
END$$
TIPS:
MySQL不建议使用存储过程,如果临时用的话可以考虑临时使用。
另外,该过程是第一版实现历史数据的迁移,如果需要重复执行还有不少优化空间,例如:
-
遍历表的时候需要指定对应库;
-
上述过程不能重复执行,如果重复执行,需要怎么改进;
-
中途涉及创建表,如果不创建哪些临时表改如何调整;
这些就留给大家思考了,也欢迎来和我沟通。文章如果有误,欢迎斧正 ,非常感谢。
往期精彩回顾
1. 升级python,就是这么简单
2. mysql8.0新增用户及加密规则修改的那些事
3. 比hive快10倍的大数据查询利器-- presto
4. 监控利器出鞘:Prometheus+Grafana监控MySQL、Redis数据库
5. PostgreSQL主从复制--物理复制
6. MySQL传统点位复制在线转为GTID模式复制
扫
码
关
注