要比较两个 mysql 数据库，请使用过程添加缺少的表和列

Question

>我编写了一个 mysql 过程来比较两个数据库并添加缺少的表和列，但它显示错误我没有发现错误，我发现该过程请帮助我清除该错误

DELIMITER $$
CREATE PROCEDURE database_comparison(IN original_db varchar(255),IN compare_db varchar(255))
BEGIN
DECLARE table_name_o varchar(255);
DECLARE column_name_o varchar(255);
DECLARE cursor_sts int default 0;
DROP TABLE IF EXISTS temp_db.original_database_list;
create temporary  table temp_db.original_database_list SELECT TABLE_NAME,COLUMN_NAME,'table not exists' as flag_t,'column not exists' as flag_c FROM information_schema.`COLUMNS` where TABLE_SCHEMA=original_db and TABLE_NAME not like 'z%' and TABLE_NAME not like '%bk%';
DROP TABLE IF EXISTS temp_db.compare_database_list;
create temporary  table temp_db.compare_database_list SELECT TABLE_NAME,COLUMN_NAME,'table not exists' as flag_t,'column not exists' as flag_c FROM information_schema.`COLUMNS` where TABLE_SCHEMA=compare_db and TABLE_NAME not like 'z%' and TABLE_NAME not like '%bk%';
update temp_db.original_database_list a join temp_db.compare_database_list b on a.TABLE_NAME=b.TABLE_NAME set a.flag_t='',b.flag_t='';
update temp_db.original_database_list a join temp_db.compare_database_list b on a.TABLE_NAME=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME set a.flag_c='',b.flag_c='';
DECLARE table_cursor CURSOR FOR select distinct TABLE_NAME from temp_db.original_database_list where flag_t<>'';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_sts=1;
OPEN table_cursor;
table_loop:LOOP
IF cursor_sts=1 THEN
SET cursor_sts=0;
LEAVE table_loop;
END IF;
FETCH table_cursor INTO table_name_o;
create table compare_db.table_name_o like original_db.table_name_o;
END LOOP table_loop;
CLOSE  table_cursor;
SET table_name_o='';
DECLARE column_cursor CURSOR FOR select distinct TABLE_NAME,COLUMN_NAME from temp_db.original_database_list where flag_t='' and flag_c<>'';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_sts=1;
OPEN column_cursor;
column_loop:LOOP
IF cursor_sts=1 THEN
LEAVE table_loop;
END IF;
FETCH column_cursor INTO table_name_o,column_name_o;
SET @query =(SELECT concat('alter table `',compare_db,'`.`',table_name_o,'` add `',column_name_o,'` ',column_type,' not null;') FROM information_schema.`COLUMNS` where TABLE_NAME=table_name_o and a.COLUMN_NAME=column_name_o and TABLE_SCHEMA=original_db);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP column_loop;
CLOSE  column_cursor;
END $$
DELIMITER ;

Answer 1

**this is the corrected procedure it works**
DELIMITER $$
DROP PROCEDURE IF EXISTS database_comparison$$
CREATE PROCEDURE database_comparison(IN original_db varchar(255),IN compare_db varchar(255))
BEGIN
DECLARE table_name_o varchar(255);
DECLARE column_name_o varchar(255);
DECLARE cursor_sts int default 0;
DEClARE table_cursor CURSOR FOR  
select distinct TABLE_NAME from temp_db.original_database_list where flag_t<>'';
DECLARE column_cursor CURSOR FOR 
select distinct TABLE_NAME,COLUMN_NAME from temp_db.original_database_list where flag_t='' and flag_c<>'';
DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET cursor_sts=1;
DROP TABLE IF EXISTS temp_db.original_database_list;
create table temp_db.original_database_list SELECT TABLE_NAME,COLUMN_NAME,'table not exists' as flag_t,'column not exists' as flag_c FROM information_schema.`COLUMNS` where TABLE_SCHEMA=original_db and TABLE_NAME not like 'z%' and TABLE_NAME not like '%bk%';
DROP TABLE IF EXISTS temp_db.compare_database_list;
create table temp_db.compare_database_list SELECT TABLE_NAME,COLUMN_NAME,'table not exists' as flag_t,'column not exists' as flag_c FROM information_schema.`COLUMNS` where TABLE_SCHEMA=compare_db and TABLE_NAME not like 'z%' and TABLE_NAME not like '%bk%';
ALTER TABLE temp_db.original_database_list ADD index(TABLE_NAME,COLUMN_NAME);
ALTER TABLE temp_db.compare_database_list ADD index(TABLE_NAME,COLUMN_NAME);
update temp_db.original_database_list a join temp_db.compare_database_list b on a.TABLE_NAME=b.TABLE_NAME set a.flag_t='',b.flag_t='';
update temp_db.original_database_list a join temp_db.compare_database_list b on a.TABLE_NAME=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME set a.flag_c='',b.flag_c='';
OPEN table_cursor;
table_loop:LOOP
FETCH table_cursor INTO table_name_o;
IF cursor_sts=1 THEN
SET cursor_sts=0;
LEAVE table_loop;
END IF;
SET @query1 =concat('create table ',compare_db,'.',table_name_o,' like ',original_db,'.',table_name_o);
PREPARE stmt1 FROM @query1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END LOOP table_loop;
CLOSE  table_cursor;
SET table_name_o='';
OPEN column_cursor;
column_loop:LOOP
FETCH column_cursor INTO table_name_o,column_name_o;
IF cursor_sts=1 THEN
LEAVE column_loop;
END IF;
SET @query =(SELECT concat('alter table `',compare_db,'`.`',table_name_o,'` add `',column_name_o,'` ',column_type,' not null;') FROM information_schema.`COLUMNS` where TABLE_NAME=table_name_o and COLUMN_NAME=column_name_o and TABLE_SCHEMA=original_db);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP column_loop;
CLOSE  column_cursor;
END $$
DELIMITER ;