存储过程——循环复制分表

最新推荐文章于 2024-08-15 22:02:46 发布

张中文

最新推荐文章于 2024-08-15 22:02:46 发布

阅读量419

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/qq_26075861/article/details/81113234

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文旨在介绍如何使用MySQL存储过程将ejex_questions_jn表的数据复制到ejex_questions_item1和ejex_questions1两个表中。过程中涉及字段拼接、拆分、排序及处理中文字符长度计算、max_allowed_packet错误等问题。存储过程中要注意避免变量名与查询字段冲突，并解决报错问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

目的

把表（ejex_questions_jn）的数据分别添加到表（ejex_questions_item1）与表（ejex_questions1）中

数据格式

ejex_questions_jn（表A）

ejex_questions_item1（表B）

ejex_questions1（表C）

把表A数据中的first、second、third字段拼接成一个字段与topic、type、diff数据复制到表C中；表A数据中的options以##拆分开复制到表B中，并把ABCD提出来以及排序，表B表C相关联

存储过程

drop procedure if exists insert_questions;
DELIMITER $$
CREATE PROCEDURE insert_questions()
BEGIN
	DECLARE qt_type VARCHAR(10);
	DECLARE qt_diff VARCHAR(20);
	DECLARE qt_topic TExt;
	DECLARE qt_options TExt;
    DECLARE qt_point1 TEXT; -- 知识点
    DECLARE qt_point2 TEXT;
    DECLARE qt_point3 TEXT;
    DECLARE qt_points TEXT;
	
	DECLARE item_title TExt;
	DECLARE qt_id int;
	declare item_abc varchar(20);-- A B C D
	declare item_seq int(10);-- 选项顺序

	-- 遍历数据结束标志
	DECLARE done INT DEFAULT FALSE;
	-- 游标
	DECLARE cur_que CURSOR FOR (select topic,type,diff,options,first,second,third from ejex_questions_jn);
	-- 将结束标志绑定到游标
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	-- 打开游标
	OPEN  cur_que;      
		-- 遍历
		my_loop: LOOP
			FETCH cur_que INTO qt_topic,qt_type,qt_diff,qt_options,qt_point1,qt_point2,qt_point3;  -- 取值 取多个字段
            SET qt_points = concat(qt_point1,'、',qt_point2,'、',qt_point3); -- 知识点拼接成一个字符串
            
			IF done THEN
				LEAVE my_loop;
			END IF;

			-- 插入试题
			INSERT INTO ejex_questions1(title,questions_type,degree,point) values(qt_topic,qt_type,qt_diff,qt_points);
            SET qt_id = LAST_INSERT_ID(); -- 试题id
            
            # 选项
			set item_seq = 1;-- 选项顺序
			SET item_title = SUBSTRING_INDEX(qt_options,'##',1);
			WHILE trim(qt_options) is not null && char_length(trim(qt_options)) <> 0
				DO 
				# set item_abc = substring_index(item_title,'、',1);
                set item_abc = LEFT(item_title,1); -- 截取选项中第一个字符，（A,B,C,D）
				SET qt_options = RIGHT(qt_options,char_length(qt_options)-char_length(item_title)-2); -- 剩余值
				set item_title = RIGHT(item_title,char_length(item_title)-2);-- 去掉前面的 A、B、 等
				INSERT INTO ejex_questions_item1(question_id,title,abc,seq) VALUES (qt_id,item_title,item_abc,item_seq);
				SET item_title = SUBSTRING_INDEX(qt_options,'##',1);
				set item_seq = item_seq + 1;
			END WHILE;
            
		END LOOP my_loop;
	CLOSE cur_que;

END$$
DELIMITER 

call insert_questions(); -- 调用

期间遇到问题

中文字符计算长度：char_length() 并非 length()
不能使定义的变量（declare）与select中查询的字段名相同，否则数据为空
报错：Result of replace() was larger than max_allowed_packet(1024)-truncated：解决办法

直接用sql命令修改：set global max_allowed_packet = 25600;再show VARIABLES like '%max_allowed_packet%';重启mysqld服务
报错：Commands out of sync; you can't run this command now，未解决，
快速删除数据：delete from table_name;
自增字段重置：alter table table_name auto_increment= 1;