前言:
眼前姐妹们对“存储过程存在哪个表里”大体比较关切,小伙伴们都想要学习一些“存储过程存在哪个表里”的相关文章。那么小编也在网上收集了一些关于“存储过程存在哪个表里””的相关资讯,希望兄弟们能喜欢,大家快快来学习一下吧!#头条创作挑战赛#
涉及循环、动态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不建议使用存储过程,如果临时用的话可以考虑临时使用。
另外,该过程是第一版实现历史数据的迁移,如果需要重复执行还有不少优化空间,例如:
遍历表的时候需要指定对应库;上述过程不能重复执行,如果重复执行,需要怎么改进;中途涉及创建表,如果不创建哪些临时表改如何调整;
这些就留给大家思考了,也欢迎来和我沟通。文章如果有误,欢迎斧正 ,非常感谢。
版权声明:
本站文章均来自互联网搜集,如有侵犯您的权益,请联系我们删除,谢谢。
标签: #存储过程存在哪个表里