记一次使用mysql存储过程时，游标取值为空问题

call modify_collation(@num,@count_num)
> 1146 - Table 'test.table_name' doesn't exist
> 时间: 0.009s

 我在使用mysql存储过程时，打印时游标取值为空，报错找不到表。我的过程语句是这样的：

drop procedure if exists modify_collation;

DELIMITER //
create PROCEDURE modify_collation(out origin_num int(10),out count_num int(10))
COMMENT 'modify_collation' 
SQL SECURITY DEFINER
BEGIN
	DECLARE table_name VARCHAR(100);
	declare column_name VARCHAR(100);
	declare done int(10) default 0;
	declare collation_cursor cursor for SELECT table_name, column_name FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_SCHEMA = 'test' and collation_name = 'utf8mb4_0900_ai_ci';
	
	declare continue handler for not found set done = 1;
	
	select count(1) into origin_num from INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_SCHEMA = 'test' and collation_name = 'utf8mb4_0900_ai_ci';
	
	select origin_num;
	
	open collation_cursor ;
	set count_num := 0;
	mylp:loop
		FETCH collation_cursor into table_name,column_name;
		
		select table_name,column_name;
 		ALTER TABLE table_name MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
		if done = 1 then
		 leave mylp;
		end if;
		set count_num := count_num +1;
	end loop;

	close collation_cursor;
	select count_num;
end//
DELIMITER ;

 执行了几遍，进入了loop循环，总是打印游标取值为空：

我执行定义游标时后面的 select 语句也能查询到数据，

SELECT table_name, column_name FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_SCHEMA = 'test' and collation_name = 'utf8mb4_0900_ai_ci';

最后发现，我定义的变量名字和查询的字段名字一样，所以单独查询sql的时候有数据，执行存储过程时，select后面查询的是变量，而不是表中的字段，变量没有赋值就查询的是空。

把定义的变量名字修改后，就正常了。

drop procedure if exists modify_collation;

DELIMITER //
create PROCEDURE modify_collation(out origin_num int(10),out count_num int(10))
COMMENT 'modify_collation' 
SQL SECURITY DEFINER
BEGIN
	DECLARE t_name VARCHAR(100);
	declare c_name VARCHAR(100);
	declare done int(10) default 0;
	declare collation_cursor cursor for SELECT table_name, column_name FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_SCHEMA = 'test' and collation_name = 'utf8mb4_0900_ai_ci';
	
	declare continue handler for not found set done = 1;
	
	select count(1) into origin_num from INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_SCHEMA = 'test' and collation_name = 'utf8mb4_0900_ai_ci';
	
	select origin_num;
	
	open collation_cursor ;
	set count_num := 0;
	mylp:loop
		FETCH collation_cursor into t_name,c_name;
		
		select t_name,c_name;
-- 		ALTER TABLE t_name MODIFY c_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
		if done = 1 then
		 leave mylp;
		end if;
		set count_num := count_num +1;
	end loop;

	close collation_cursor;
	select count_num;
end//
DELIMITER ;