mysql - 比较两个mysql数据库使用过程添加缺少的表和列
问题描述
我已经编写了一个 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 ;
解决方案
**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 ;
推荐阅读
- xml - 将 XML 数据的选定部分附加到另一个 XML 数据
- python - DataFrame Pandas - 如何将字典列表从每一行拆分为单独的列?
- node.js - 方法“collection.find()”最多接受两个参数
- java - RabbitMQ Spring Java - RPC 模式
- stored-procedures - 如何检查存储过程参数是否具有默认值(是否可为空)、数据类型等
- amazon-redshift - Postgres | 使用促销代码后获取所有 ID
- c++ - QLabel 不更新图像内容
- javascript - JavaScript 费用跟踪器计算器 - 减法
- quota - xfs_quota - 属性显示默认值并删除与配额相关的元数据
- java - Java Servlet 读取 URL 重定向后的参数