草稿整理后mysql兩個(gè)數(shù)據(jù)庫(kù)結(jié)構(gòu)對(duì)比
更新時(shí)間：2022年02月07日 10:36:41 作者：xiaostudy
這篇文章主要為大家詳細(xì)介紹了mysql兩個(gè)數(shù)據(jù)庫(kù)結(jié)構(gòu)對(duì)比結(jié)果，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來(lái)幫助
1、草稿：
-- 1.將mysql分隔符從;設(shè)置為&
DELIMITER &
-- 2.如果存在存儲(chǔ)過(guò)程getdatabaseCount則刪除
DROP PROCEDURE IF EXISTS `getdatabaseCount` &
-- 3.定義存儲(chǔ)過(guò)程，獲取特定數(shù)據(jù)庫(kù)的數(shù)量
-- (傳入?yún)?shù)database_name字符串類(lèi)型，為數(shù)據(jù)庫(kù)名；傳出參數(shù)count_date整數(shù)類(lèi)型，為數(shù)量)
CREATE DEFINER=`root`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT)
BEGIN
-- 4.聲明變量
DECLARE $sqltext VARCHAR(1000);
-- 5.動(dòng)態(tài)sql，把sql返回值放到@count_date中
SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\';');
SET @sqlcounts := $sqltext;
-- 6.預(yù)編釋?zhuān)瑂tmt預(yù)編釋變量的名稱
PREPARE stmt FROM @sqlcounts;
-- 7.執(zhí)行SQL語(yǔ)句
EXECUTE stmt;
-- 8.釋放資源
DEALLOCATE PREPARE stmt;
-- 9.獲取動(dòng)態(tài)SQL語(yǔ)句返回值
SET count_date = @count_date;
END
-- 10.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 2.如果存在存儲(chǔ)過(guò)程getCount則刪除
DROP PROCEDURE IF EXISTS `getTableCount` &
-- 3.定義存儲(chǔ)過(guò)程，獲取特定數(shù)據(jù)庫(kù)表的數(shù)量
-- (傳入?yún)?shù)database_name字符串類(lèi)型，為數(shù)據(jù)庫(kù)名；傳入?yún)?shù)table_name字符串類(lèi)型，為表名；傳出參數(shù)count_date整數(shù)類(lèi)型，為數(shù)量)
CREATE DEFINER=`root`@`localhost` PROCEDURE getTableCount(IN database_name CHAR(20), IN table_name CHAR(200), OUT count_date INT)
BEGIN
-- 4.聲明變量
DECLARE $sqltext VARCHAR(1000);
-- 5.動(dòng)態(tài)sql，把sql返回值放到@count_date中
SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\';');
SET @sqlcounts := $sqltext;
-- 6.預(yù)編釋?zhuān)瑂tmt預(yù)編釋變量的名稱
PREPARE stmt FROM @sqlcounts;
-- 7.執(zhí)行SQL語(yǔ)句
EXECUTE stmt;
-- 8.釋放資源
DEALLOCATE PREPARE stmt;
-- 9.獲取動(dòng)態(tài)SQL語(yǔ)句返回值
SET count_date = @count_date;
END
-- 10.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 2.如果存在存儲(chǔ)過(guò)程getColumnCount則刪除
DROP PROCEDURE IF EXISTS `getColumnCount` &
-- 3.定義存儲(chǔ)過(guò)程，獲取特定數(shù)據(jù)庫(kù)表列的數(shù)量
-- (傳入?yún)?shù)database_name字符串類(lèi)型，為數(shù)據(jù)庫(kù)名；傳入?yún)?shù)table_name字符串類(lèi)型，為表名；傳入?yún)?shù)column_name字符串類(lèi)型，為列名；傳出參數(shù)count_date整數(shù)類(lèi)型，為數(shù)量)
CREATE DEFINER=`root`@`localhost` PROCEDURE getColumnCount(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), OUT count_date INT)
BEGIN
-- 4.聲明變量
DECLARE $sqltext VARCHAR(1000);
-- 5.動(dòng)態(tài)sql，把sql返回值放到@count_date中
SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\' and t.`COLUMN_NAME` = \'', column_name, '\';');
SET @sqlcounts := $sqltext;
-- 6.預(yù)編釋?zhuān)瑂tmt預(yù)編釋變量的名稱
PREPARE stmt FROM @sqlcounts;
-- 7.執(zhí)行SQL語(yǔ)句
EXECUTE stmt;
-- 8.釋放資源
DEALLOCATE PREPARE stmt;
-- 9.獲取動(dòng)態(tài)SQL語(yǔ)句返回值
SET count_date = @count_date;
END
-- 10.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 2.如果存在存儲(chǔ)過(guò)程getColumnInfo則刪除
DROP PROCEDURE IF EXISTS `getColumnInfo` &
-- 3.定義存儲(chǔ)過(guò)程，獲取特定數(shù)據(jù)庫(kù)表列的信息
-- (傳入?yún)?shù)database_name字符串類(lèi)型，為數(shù)據(jù)庫(kù)名；傳入?yún)?shù)table_name字符串類(lèi)型，為表名；傳入?yún)?shù)column_name字符串類(lèi)型，為列名；傳入?yún)?shù)column_info字符串類(lèi)型，列信息；傳出參數(shù)result_data字符串類(lèi)型，信息)
CREATE DEFINER=`root`@`localhost` PROCEDURE getColumnInfo(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), IN column_info CHAR(50), OUT result_data CHAR(20))
BEGIN
-- 4.聲明變量
DECLARE $sqltext VARCHAR(1000);
-- 5.動(dòng)態(tài)sql，把sql返回值放到@count_date中
SET $sqltext = CONCAT('SELECT t.', column_info,' into @column_info FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\' and t.`COLUMN_NAME` = \'', column_name, '\';');
SET @sqlcounts := $sqltext;
-- 6.預(yù)編釋?zhuān)瑂tmt預(yù)編釋變量的名稱
PREPARE stmt FROM @sqlcounts;
-- 7.執(zhí)行SQL語(yǔ)句
EXECUTE stmt;
-- 8.釋放資源
DEALLOCATE PREPARE stmt;
-- 9.獲取動(dòng)態(tài)SQL語(yǔ)句返回值
SET result_data = @column_info;
END
-- 10.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 11.如果存在存儲(chǔ)過(guò)程comparison則刪除
DROP PROCEDURE IF EXISTS `comparison` &
-- 12.定義存儲(chǔ)過(guò)程，獲取指定數(shù)據(jù)庫(kù)關(guān)鍵詞的表列名
-- (傳入?yún)?shù)database_n字符串類(lèi)型，數(shù)據(jù)庫(kù)名；傳入?yún)?shù)collation_n字符串類(lèi)型，具體編碼類(lèi)型；傳入?yún)?shù)key_name字符串類(lèi)型，為關(guān)鍵字；傳出參數(shù)tableColumnNames字符串類(lèi)型，表列名)
CREATE DEFINER=`root`@`localhost` PROCEDURE comparison(IN database_1 CHAR(20), IN database_2 CHAR(20), IN column_info CHAR(50), OUT info TEXT)
BEGIN
-- 13.聲明變量。database_name查詢出來(lái)的數(shù)據(jù)庫(kù)，table_name查詢出來(lái)的表名，column_name查詢出來(lái)的列名，collation_name查詢出來(lái)的具體編碼類(lèi)型
DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2 CHAR(200);
DECLARE this_info, database_table_no TEXT DEFAULT '';
DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
-- 14.定義游標(biāo)結(jié)束標(biāo)識(shí)，默認(rèn)為0
DECLARE stopflag INT DEFAULT 0;
-- 15.定義游標(biāo)，其實(shí)就是臨時(shí)存儲(chǔ)sql返回的集合
DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t;
-- 16.游標(biāo)結(jié)束就設(shè)置為1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
CALL getdatabaseCount(database_1, database_count_1);
CALL getdatabaseCount(database_2, database_count_2);
IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
-- 17.打開(kāi)游標(biāo)
OPEN sql_resoult;
-- 18.讀取游標(biāo)中數(shù)據(jù)，存儲(chǔ)到指定變量
FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
-- 19.沒(méi)有結(jié)束繼續(xù)往下走
WHILE (stopflag=0) DO
BEGIN
-- 20.判斷數(shù)據(jù)庫(kù)是否為輸入的數(shù)據(jù)庫(kù)名稱，和，指定具體編碼類(lèi)型，和，不含.
IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
-- 21.調(diào)用存儲(chǔ)過(guò)程，獲取特定表列關(guān)鍵詞的數(shù)量
CALL getTableCount(database_2, table_name, resoult_count);
-- 22.如果數(shù)量不等于0，那么記錄表列名
IF (resoult_count <> 0) THEN
CALL getColumnCount(database_2, table_name, column_name, resoult_count);
-- 23.拼接字符串，不可直接用傳出變量設(shè)值
IF (resoult_count <> 0) THEN
CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
-- 23.拼接字符串，不可直接用傳出變量設(shè)值
IF (result_data_1 <> result_data_2) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一樣;\n');
ELSE
SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一樣;\n');
END IF;
END IF;
ELSE
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_2, '的', table_name, '表的', column_name, '列不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_2, '的', table_name, '表的', column_name, '列不存在;\n');
END IF;
END IF;
ELSE
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_2, '的', table_name, '表不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_2, '的', table_name, '表不存在;\n');
END IF;
SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
END IF;
ELSE
IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
CALL getTableCount(database_1, table_name, resoult_count);
IF (resoult_count <> 0) THEN
CALL getColumnCount(database_1, table_name, column_name, resoult_count);
IF (resoult_count = 0) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_1, '的', table_name, '表的', column_name, '列不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_1, '的', table_name, '表的', column_name, '列不存在;\n');
END IF;
END IF;
ELSE
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_1, '的', table_name, '表不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_1, '的', table_name, '表不存在;\n');
END IF;
SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
END IF;
END IF;
END IF;
-- 24.讀取游標(biāo)中數(shù)據(jù)，存儲(chǔ)到指定變量。（和18一樣）
FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
END;
END WHILE;
-- 25.關(guān)閉游標(biāo)
CLOSE sql_resoult;
ELSE
IF (database_count_1 = 0 AND database_count_2 = 0) THEN
SET this_info = CONCAT(database_1, '和', database_2, '數(shù)據(jù)庫(kù)不存在或?yàn)榭諗?shù)據(jù)庫(kù)');
ELSE
IF (database_count_1 = 0) THEN
SET this_info = CONCAT(database_1, '數(shù)據(jù)庫(kù)不存在或?yàn)榭諗?shù)據(jù)庫(kù)');
ELSE
SET this_info = CONCAT(database_2, '數(shù)據(jù)庫(kù)不存在或?yàn)榭諗?shù)據(jù)庫(kù)');
END IF;
END IF;
END IF;
-- 26.把數(shù)據(jù)放到傳出參數(shù)
SET info=this_info;
END
-- 27.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 28.將mysql分隔符從&設(shè)置為;
DELIMITER ;
-- 29.設(shè)置變量
SET @database_1='my_test';
SET @database_2='my_test2';
SET @column_info='data_type';
SET @count='';
-- 30.調(diào)用存儲(chǔ)過(guò)程
CALL comparison(@database_1, @database_2, @column_info, @count);
-- 31.打印
SELECT @count;
-- 32.如果存在存儲(chǔ)過(guò)程則刪除
DROP PROCEDURE IF EXISTS `comparison`;
2、整理：
-- 1.將mysql分隔符從;設(shè)置為&
DELIMITER &
-- 2.如果存在存儲(chǔ)過(guò)程getdatabaseCount則刪除
DROP PROCEDURE IF EXISTS `getdatabaseCount` &
-- 3.定義存儲(chǔ)過(guò)程，獲取特定數(shù)據(jù)庫(kù)的數(shù)量
-- (傳入?yún)?shù)database_name字符串類(lèi)型，為數(shù)據(jù)庫(kù)名；傳出參數(shù)count_date整數(shù)類(lèi)型，為數(shù)量)
CREATE DEFINER=`root`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT)
BEGIN
-- 4.聲明變量
DECLARE $sqltext VARCHAR(1000);
-- 5.動(dòng)態(tài)sql，把sql返回值放到@count_date中
SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\';');
SET @sqlcounts := $sqltext;
-- 6.預(yù)編釋?zhuān)瑂tmt預(yù)編釋變量的名稱
PREPARE stmt FROM @sqlcounts;
-- 7.執(zhí)行SQL語(yǔ)句
EXECUTE stmt;
-- 8.釋放資源
DEALLOCATE PREPARE stmt;
-- 9.獲取動(dòng)態(tài)SQL語(yǔ)句返回值
SET count_date = @count_date;
END
-- 10.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 11.如果存在存儲(chǔ)過(guò)程getTableCount則刪除
DROP PROCEDURE IF EXISTS `getTableCount` &
-- 12.定義存儲(chǔ)過(guò)程，獲取特定數(shù)據(jù)庫(kù)表的數(shù)量
-- (傳入?yún)?shù)database_name字符串類(lèi)型，為數(shù)據(jù)庫(kù)名；傳入?yún)?shù)table_name字符串類(lèi)型，為表名；傳出參數(shù)count_date整數(shù)類(lèi)型，為數(shù)量)
CREATE DEFINER=`root`@`localhost` PROCEDURE getTableCount(IN database_name CHAR(20), IN table_name CHAR(200), OUT count_date INT)
BEGIN
-- 13.聲明變量
DECLARE $sqltext VARCHAR(1000);
-- 14.動(dòng)態(tài)sql，把sql返回值放到@count_date中
SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\';');
SET @sqlcounts := $sqltext;
-- 15.預(yù)編釋?zhuān)瑂tmt預(yù)編釋變量的名稱
PREPARE stmt FROM @sqlcounts;
-- 16.執(zhí)行SQL語(yǔ)句
EXECUTE stmt;
-- 17.釋放資源
DEALLOCATE PREPARE stmt;
-- 18.獲取動(dòng)態(tài)SQL語(yǔ)句返回值
SET count_date = @count_date;
END
-- 19.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 20.如果存在存儲(chǔ)過(guò)程getColumnCount則刪除
DROP PROCEDURE IF EXISTS `getColumnCount` &
-- 21.定義存儲(chǔ)過(guò)程，獲取特定數(shù)據(jù)庫(kù)表列的數(shù)量
-- (傳入?yún)?shù)database_name字符串類(lèi)型，為數(shù)據(jù)庫(kù)名；傳入?yún)?shù)table_name字符串類(lèi)型，為表名；傳入?yún)?shù)column_name字符串類(lèi)型，為列名；傳出參數(shù)count_date整數(shù)類(lèi)型，為數(shù)量)
CREATE DEFINER=`root`@`localhost` PROCEDURE getColumnCount(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), OUT count_date INT)
BEGIN
-- 22.聲明變量
DECLARE $sqltext VARCHAR(1000);
-- 23.動(dòng)態(tài)sql，把sql返回值放到@count_date中
SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\' and t.`COLUMN_NAME` = \'', column_name, '\';');
SET @sqlcounts := $sqltext;
-- 24.預(yù)編釋?zhuān)瑂tmt預(yù)編釋變量的名稱
PREPARE stmt FROM @sqlcounts;
-- 25.執(zhí)行SQL語(yǔ)句
EXECUTE stmt;
-- 26.釋放資源
DEALLOCATE PREPARE stmt;
-- 27.獲取動(dòng)態(tài)SQL語(yǔ)句返回值
SET count_date = @count_date;
END
-- 28.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 29.如果存在存儲(chǔ)過(guò)程getColumnInfo則刪除
DROP PROCEDURE IF EXISTS `getColumnInfo` &
-- 30.定義存儲(chǔ)過(guò)程，獲取特定數(shù)據(jù)庫(kù)表列的信息
-- (傳入?yún)?shù)database_name字符串類(lèi)型，為數(shù)據(jù)庫(kù)名；傳入?yún)?shù)table_name字符串類(lèi)型，為表名；傳入?yún)?shù)column_name字符串類(lèi)型，為列名；傳入?yún)?shù)column_info字符串類(lèi)型，列信息；傳出參數(shù)result_data字符串類(lèi)型，信息)
CREATE DEFINER=`root`@`localhost` PROCEDURE getColumnInfo(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), IN column_info CHAR(50), OUT result_data CHAR(20))
BEGIN
-- 31.聲明變量
DECLARE $sqltext VARCHAR(1000);
-- 32.動(dòng)態(tài)sql，把sql返回值放到@count_date中
SET $sqltext = CONCAT('SELECT t.', column_info,' into @column_info FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\' and t.`COLUMN_NAME` = \'', column_name, '\';');
SET @sqlcounts := $sqltext;
-- 33.預(yù)編釋?zhuān)瑂tmt預(yù)編釋變量的名稱
PREPARE stmt FROM @sqlcounts;
-- 34.執(zhí)行SQL語(yǔ)句
EXECUTE stmt;
-- 35.釋放資源
DEALLOCATE PREPARE stmt;
-- 36.獲取動(dòng)態(tài)SQL語(yǔ)句返回值
SET result_data = @column_info;
END
-- 37.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 38.如果存在存儲(chǔ)過(guò)程comparisonTableExist則刪除
DROP PROCEDURE IF EXISTS `comparisonTableExist` &
-- 39.定義存儲(chǔ)過(guò)程，對(duì)比表是否存在
-- (傳入?yún)?shù)database_1字符串類(lèi)型，數(shù)據(jù)庫(kù)名1；傳入?yún)?shù)database_2字符串類(lèi)型，數(shù)據(jù)庫(kù)名2；傳入?yún)?shù)info字符串類(lèi)型，庫(kù)表信息)
CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonTableExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
BEGIN
-- 40.聲明變量。database_name查詢出來(lái)的數(shù)據(jù)庫(kù)，table_name查詢出來(lái)的表名
DECLARE database_name, table_name CHAR(200);
-- this_info表不存在的記錄，database_table_no表不存在的記錄跳過(guò)重復(fù)查詢
DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';
-- database_count_1統(tǒng)計(jì)數(shù)據(jù)庫(kù)1存在的數(shù)量，database_count_2統(tǒng)計(jì)數(shù)據(jù)庫(kù)2存在的數(shù)量，resoult_count統(tǒng)計(jì)表存在的數(shù)量。如果為0表示不存在
DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
-- 41.定義游標(biāo)結(jié)束標(biāo)識(shí)，默認(rèn)為0
DECLARE stopflag INT DEFAULT 0;
-- 42.定義游標(biāo)，其實(shí)就是臨時(shí)存儲(chǔ)sql返回的集合
DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.COLUMNS t;
-- 43.游標(biāo)結(jié)束就設(shè)置為1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
-- 44.調(diào)用存儲(chǔ)過(guò)程getdatabaseCount，查看兩個(gè)數(shù)據(jù)庫(kù)是否存在，都存在則繼續(xù)
CALL getdatabaseCount(database_1, database_count_1);
CALL getdatabaseCount(database_2, database_count_2);
IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
-- 45.打開(kāi)游標(biāo)
OPEN sql_resoult;
-- 46.讀取游標(biāo)中數(shù)據(jù)，存儲(chǔ)到指定變量
FETCH sql_resoult INTO database_name, table_name;
-- 47.沒(méi)有結(jié)束繼續(xù)往下走
WHILE (stopflag=0) DO
BEGIN
-- 48.判斷數(shù)據(jù)庫(kù)是否為輸入的數(shù)據(jù)庫(kù)名稱1，去除已經(jīng)比較過(guò)數(shù)據(jù)庫(kù)2的表不存在
IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
-- 49.調(diào)用存儲(chǔ)過(guò)程getTableCount，查看表是否存在
CALL getTableCount(database_2, table_name, resoult_count);
-- 50.如果數(shù)量等于0，那么表不存在
IF (resoult_count = 0) THEN
-- 51.把不存在的表記錄下來(lái)
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_2, '的', table_name, '表不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_2, '的', table_name, '表不存在;\n');
END IF;
SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
END IF;
ELSE
-- 52.判斷數(shù)據(jù)庫(kù)是否為輸入的數(shù)據(jù)庫(kù)名稱2，去除已經(jīng)比較過(guò)數(shù)據(jù)庫(kù)1的表不存在
IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
CALL getTableCount(database_1, table_name, resoult_count);
IF (resoult_count = 0) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_1, '的', table_name, '表不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_1, '的', table_name, '表不存在;\n');
END IF;
SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
END IF;
END IF;
END IF;
-- 53.讀取游標(biāo)中數(shù)據(jù)，存儲(chǔ)到指定變量。（和46一樣）
FETCH sql_resoult INTO database_name, table_name;
END;
END WHILE;
-- 54.關(guān)閉游標(biāo)
CLOSE sql_resoult;
ELSE
IF (database_count_1 = 0 AND database_count_2 = 0) THEN
SET this_info = CONCAT(database_1, '和', database_2, '數(shù)據(jù)庫(kù)不存在或?yàn)榭諗?shù)據(jù)庫(kù)');
ELSE
IF (database_count_1 = 0) THEN
SET this_info = CONCAT(database_1, '數(shù)據(jù)庫(kù)不存在或?yàn)榭諗?shù)據(jù)庫(kù)');
ELSE
SET this_info = CONCAT(database_2, '數(shù)據(jù)庫(kù)不存在或?yàn)榭諗?shù)據(jù)庫(kù)');
END IF;
END IF;
END IF;
-- 55.把數(shù)據(jù)放到傳出參數(shù)
SET info=this_info;
END
-- 56.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 57.如果存在存儲(chǔ)過(guò)程comparisonColumnExist則刪除
DROP PROCEDURE IF EXISTS `comparisonColumnExist` &
-- 58.定義存儲(chǔ)過(guò)程，對(duì)比列是否存在
-- (傳入?yún)?shù)database_1字符串類(lèi)型，數(shù)據(jù)庫(kù)名1；傳入?yún)?shù)database_2字符串類(lèi)型，數(shù)據(jù)庫(kù)名2；傳入?yún)?shù)info字符串類(lèi)型，庫(kù)表信息)
CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
BEGIN
-- 59.聲明變量。database_name查詢出來(lái)的數(shù)據(jù)庫(kù)，table_name查詢出來(lái)的表名，column_name查詢出來(lái)的列名
DECLARE database_name, table_name, column_name CHAR(200);
-- this_info表不存在的記錄，database_table_no表不存在的記錄跳過(guò)重復(fù)查詢
DECLARE this_info, database_table_no TEXT DEFAULT '';
-- database_count_1統(tǒng)計(jì)數(shù)據(jù)庫(kù)1存在的數(shù)量，database_count_2統(tǒng)計(jì)數(shù)據(jù)庫(kù)2存在的數(shù)量，resoult_count統(tǒng)計(jì)表存在的數(shù)量。如果為0表示不存在
DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
-- 60.定義游標(biāo)結(jié)束標(biāo)識(shí)，默認(rèn)為0
DECLARE stopflag INT DEFAULT 0;
-- 61.定義游標(biāo)，其實(shí)就是臨時(shí)存儲(chǔ)sql返回的集合
DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROM information_schema.COLUMNS t;
-- 62.游標(biāo)結(jié)束就設(shè)置為1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
-- 63.調(diào)用存儲(chǔ)過(guò)程getdatabaseCount，查看兩個(gè)數(shù)據(jù)庫(kù)是否存在，都存在則繼續(xù)（同44）
CALL getdatabaseCount(database_1, database_count_1);
CALL getdatabaseCount(database_2, database_count_2);
IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
-- 64.打開(kāi)游標(biāo)
OPEN sql_resoult;
-- 65.讀取游標(biāo)中數(shù)據(jù)，存儲(chǔ)到指定變量
FETCH sql_resoult INTO database_name, table_name, column_name;
-- 66.沒(méi)有結(jié)束繼續(xù)往下走
WHILE (stopflag=0) DO
BEGIN
-- 67.判斷數(shù)據(jù)庫(kù)是否為輸入的數(shù)據(jù)庫(kù)名稱1，去除已經(jīng)比較過(guò)數(shù)據(jù)庫(kù)2的表不存在（同48）
IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
-- 68.調(diào)用存儲(chǔ)過(guò)程getTableCount，查看表是否存在（同49）
CALL getTableCount(database_2, table_name, resoult_count);
-- 69.如果數(shù)量不等于0，則繼續(xù)
IF (resoult_count <> 0) THEN
-- 70.調(diào)用存儲(chǔ)過(guò)程getColumnCount，查看列是否存在。為0說(shuō)明不存在
CALL getColumnCount(database_2, table_name, column_name, resoult_count);
IF (resoult_count = 0) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_2, '的', table_name, '表的', column_name, '列不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_2, '的', table_name, '表的', column_name, '列不存在;\n');
END IF;
END IF;
ELSE
SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
END IF;
ELSE
-- 71.判斷數(shù)據(jù)庫(kù)是否為輸入的數(shù)據(jù)庫(kù)名稱2，去除已經(jīng)比較過(guò)數(shù)據(jù)庫(kù)1的表不存在（同52）
IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
CALL getTableCount(database_1, table_name, resoult_count);
IF (resoult_count <> 0) THEN
CALL getColumnCount(database_1, table_name, column_name, resoult_count);
IF (resoult_count = 0) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_1, '的', table_name, '表的', column_name, '列不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_1, '的', table_name, '表的', column_name, '列不存在;\n');
END IF;
END IF;
ELSE
SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
END IF;
END IF;
END IF;
-- 72.讀取游標(biāo)中數(shù)據(jù)，存儲(chǔ)到指定變量。（和65一樣）
FETCH sql_resoult INTO database_name, table_name, column_name;
END;
END WHILE;
-- 73.關(guān)閉游標(biāo)
CLOSE sql_resoult;
END IF;
-- 74.把數(shù)據(jù)放到傳出參數(shù)
SET info=this_info;
END
-- 75.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 76.如果存在存儲(chǔ)過(guò)程comparisonColumnInfo則刪除
DROP PROCEDURE IF EXISTS `comparisonColumnInfo` &
-- 77.定義存儲(chǔ)過(guò)程，對(duì)比列的不同
-- (傳入?yún)?shù)database_1字符串類(lèi)型，數(shù)據(jù)庫(kù)名1；傳入?yún)?shù)database_2字符串類(lèi)型，數(shù)據(jù)庫(kù)名2；傳入?yún)?shù)info字符串類(lèi)型，庫(kù)表信息)
CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnInfo(IN database_1 CHAR(20), IN database_2 CHAR(20),OUT info MEDIUMTEXT)
BEGIN
-- 78.聲明變量。database_name查詢出來(lái)的數(shù)據(jù)庫(kù)，table_name查詢出來(lái)的表名，column_name查詢出來(lái)的列名，collation_name查詢出來(lái)的具體編碼類(lèi)型
-- result_data_1數(shù)據(jù)庫(kù)1的列信息，result_data_2數(shù)據(jù)庫(kù)2的列信息，column_info對(duì)比的列（現(xiàn)在只比較DATA_TYPE、CHARACTER_SET_NAME）
DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2, column_info CHAR(200);
-- this_info表不存在的記錄，database_table_no表不存在的記錄跳過(guò)重復(fù)查詢
DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';
-- database_count_1統(tǒng)計(jì)數(shù)據(jù)庫(kù)1存在的數(shù)量，database_count_2統(tǒng)計(jì)數(shù)據(jù)庫(kù)2存在的數(shù)量，resoult_count統(tǒng)計(jì)表存在的數(shù)量。如果為0表示不存在
DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
-- 79.定義游標(biāo)結(jié)束標(biāo)識(shí)，默認(rèn)為0
DECLARE stopflag INT DEFAULT 0;
-- 80.定義游標(biāo)，其實(shí)就是臨時(shí)存儲(chǔ)sql返回的集合
DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t;
-- 81.游標(biāo)結(jié)束就設(shè)置為1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
-- 82.調(diào)用存儲(chǔ)過(guò)程getdatabaseCount，查看兩個(gè)數(shù)據(jù)庫(kù)是否存在，都存在則繼續(xù)（同63）
CALL getdatabaseCount(database_1, database_count_1);
CALL getdatabaseCount(database_2, database_count_2);
IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
-- 83.打開(kāi)游標(biāo)
OPEN sql_resoult;
-- 84.讀取游標(biāo)中數(shù)據(jù)，存儲(chǔ)到指定變量
FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
-- 85.沒(méi)有結(jié)束繼續(xù)往下走
WHILE (stopflag=0) DO
BEGIN
-- 86.判斷數(shù)據(jù)庫(kù)是否為輸入的數(shù)據(jù)庫(kù)名稱1，去除已經(jīng)比較過(guò)數(shù)據(jù)庫(kù)2的表不存在（同67）
IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
-- 87.調(diào)用存儲(chǔ)過(guò)程getTableCount，查看表是否存在（同68）
CALL getTableCount(database_2, table_name, resoult_count);
-- 88.如果數(shù)量不等于0，則繼續(xù)
IF (resoult_count <> 0) THEN
-- 89.調(diào)用存儲(chǔ)過(guò)程getColumnCount，查看列是否存在。為0說(shuō)明不存在（同70）
CALL getColumnCount(database_2, table_name, column_name, resoult_count);
IF (resoult_count <> 0) THEN
-- 90.對(duì)比DATA_TYPE是否相同
SET column_info = 'DATA_TYPE';
CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
IF (result_data_1 <> result_data_2) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一樣;\n');
ELSE
SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一樣;\n');
END IF;
END IF;
-- 91.對(duì)比CHARACTER_SET_NAME是否相同
SET column_info = 'CHARACTER_SET_NAME';
CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
IF (result_data_1 <> result_data_2) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一樣;\n');
ELSE
SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一樣;\n');
END IF;
END IF;
END IF;
ELSE
SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
END IF;
ELSE
IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
CALL getTableCount(database_1, table_name, resoult_count);
IF (resoult_count = 0) THEN
SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
END IF;
END IF;
END IF;
-- 92.讀取游標(biāo)中數(shù)據(jù)，存儲(chǔ)到指定變量。（和84一樣）
FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
END;
END WHILE;
-- 93.關(guān)閉游標(biāo)
CLOSE sql_resoult;
END IF;
-- 94.把數(shù)據(jù)放到傳出參數(shù)
SET info=this_info;
END
-- 95.定義存儲(chǔ)過(guò)程結(jié)束
&
-- 96.將mysql分隔符從&設(shè)置為;
DELIMITER ;
-- 97.設(shè)置變量
SET @database_1='my_test1';
SET @database_2='my_test2';
SET @tableExistInfo='';
SET @columnExistInfo='';
SET @columnInfo='';
-- 98.調(diào)用存儲(chǔ)過(guò)程
CALL comparisonTableExist(@database_1, @database_2, @tableExistInfo);
CALL comparisonColumnExist(@database_1, @database_2, @columnExistInfo);
CALL comparisonColumnInfo(@database_1, @database_2, @columnInfo);
SET @info=CONCAT(@tableExistInfo, '\n', @columnExistInfo, '\n', @columnInfo);
-- 99.打印
SELECT @info;
-- 100.如果存在存儲(chǔ)過(guò)程則刪除
DROP PROCEDURE IF EXISTS `comparisonColumnInfo`;
DROP PROCEDURE IF EXISTS `comparisonColumnExist`;
DROP PROCEDURE IF EXISTS `comparisonTableExist`;
DROP PROCEDURE IF EXISTS `getColumnInfo`;
DROP PROCEDURE IF EXISTS `getColumnCount`;
DROP PROCEDURE IF EXISTS `getTableCount`;
DROP PROCEDURE IF EXISTS `getdatabaseCount`;3、無(wú)注釋
DELIMITER &
DROP PROCEDURE IF EXISTS `getdatabaseCount` &
CREATE DEFINER=`root`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT)
BEGIN
DECLARE $sqltext VARCHAR(1000);
SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\';');
SET @sqlcounts := $sqltext;
PREPARE stmt FROM @sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET count_date = @count_date;
END
&
DROP PROCEDURE IF EXISTS `getTableCount` &
CREATE DEFINER=`root`@`localhost` PROCEDURE getTableCount(IN database_name CHAR(20), IN table_name CHAR(200), OUT count_date INT)
BEGIN
DECLARE $sqltext VARCHAR(1000);
SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\';');
SET @sqlcounts := $sqltext;
PREPARE stmt FROM @sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET count_date = @count_date;
END
&
DROP PROCEDURE IF EXISTS `getColumnCount` &
CREATE DEFINER=`root`@`localhost` PROCEDURE getColumnCount(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), OUT count_date INT)
BEGIN
DECLARE $sqltext VARCHAR(1000);
SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\' and t.`COLUMN_NAME` = \'', column_name, '\';');
SET @sqlcounts := $sqltext;
PREPARE stmt FROM @sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET count_date = @count_date;
END
&
DROP PROCEDURE IF EXISTS `getColumnInfo` &
CREATE DEFINER=`root`@`localhost` PROCEDURE getColumnInfo(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), IN column_info CHAR(50), OUT result_data CHAR(20))
BEGIN
DECLARE $sqltext VARCHAR(1000);
SET $sqltext = CONCAT('SELECT t.', column_info,' into @column_info FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\' and t.`COLUMN_NAME` = \'', column_name, '\';');
SET @sqlcounts := $sqltext;
PREPARE stmt FROM @sqlcounts;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET result_data = @column_info;
END
&
DROP PROCEDURE IF EXISTS `comparisonTableExist` &
CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonTableExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
BEGIN
DECLARE database_name, table_name CHAR(200);
DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';
DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
DECLARE stopflag INT DEFAULT 0;
DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.COLUMNS t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
CALL getdatabaseCount(database_1, database_count_1);
CALL getdatabaseCount(database_2, database_count_2);
IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
OPEN sql_resoult;
FETCH sql_resoult INTO database_name, table_name;
WHILE (stopflag=0) DO
BEGIN
IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
CALL getTableCount(database_2, table_name, resoult_count);
IF (resoult_count = 0) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_2, '的', table_name, '表不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_2, '的', table_name, '表不存在;\n');
END IF;
SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
END IF;
ELSE
IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
CALL getTableCount(database_1, table_name, resoult_count);
IF (resoult_count = 0) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_1, '的', table_name, '表不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_1, '的', table_name, '表不存在;\n');
END IF;
SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
END IF;
END IF;
END IF;
FETCH sql_resoult INTO database_name, table_name;
END;
END WHILE;
CLOSE sql_resoult;
ELSE
IF (database_count_1 = 0 AND database_count_2 = 0) THEN
SET this_info = CONCAT(database_1, '和', database_2, '數(shù)據(jù)庫(kù)不存在或?yàn)榭諗?shù)據(jù)庫(kù)');
ELSE
IF (database_count_1 = 0) THEN
SET this_info = CONCAT(database_1, '數(shù)據(jù)庫(kù)不存在或?yàn)榭諗?shù)據(jù)庫(kù)');
ELSE
SET this_info = CONCAT(database_2, '數(shù)據(jù)庫(kù)不存在或?yàn)榭諗?shù)據(jù)庫(kù)');
END IF;
END IF;
END IF;
SET info=this_info;
END
&
DROP PROCEDURE IF EXISTS `comparisonColumnExist` &
CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
BEGIN
DECLARE database_name, table_name, column_name CHAR(200);
DECLARE this_info, database_table_no TEXT DEFAULT '';
DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
DECLARE stopflag INT DEFAULT 0;
DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROM information_schema.COLUMNS t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
CALL getdatabaseCount(database_1, database_count_1);
CALL getdatabaseCount(database_2, database_count_2);
IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
OPEN sql_resoult;
FETCH sql_resoult INTO database_name, table_name, column_name;
WHILE (stopflag=0) DO
BEGIN
IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
CALL getTableCount(database_2, table_name, resoult_count);
IF (resoult_count <> 0) THEN
CALL getColumnCount(database_2, table_name, column_name, resoult_count);
IF (resoult_count = 0) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_2, '的', table_name, '表的', column_name, '列不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_2, '的', table_name, '表的', column_name, '列不存在;\n');
END IF;
END IF;
ELSE
SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
END IF;
ELSE
IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
CALL getTableCount(database_1, table_name, resoult_count);
IF (resoult_count <> 0) THEN
CALL getColumnCount(database_1, table_name, column_name, resoult_count);
IF (resoult_count = 0) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(database_1, '的', table_name, '表的', column_name, '列不存在;\n');
ELSE
SET this_info=CONCAT(this_info, database_1, '的', table_name, '表的', column_name, '列不存在;\n');
END IF;
END IF;
ELSE
SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
END IF;
END IF;
END IF;
FETCH sql_resoult INTO database_name, table_name, column_name;
END;
END WHILE;
CLOSE sql_resoult;
END IF;
SET info=this_info;
END
&
DROP PROCEDURE IF EXISTS `comparisonColumnInfo` &
CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnInfo(IN database_1 CHAR(20), IN database_2 CHAR(20),OUT info MEDIUMTEXT)
BEGIN
DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2, column_info CHAR(200);
DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';
DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
DECLARE stopflag INT DEFAULT 0;
DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
CALL getdatabaseCount(database_1, database_count_1);
CALL getdatabaseCount(database_2, database_count_2);
IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
OPEN sql_resoult;
FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
WHILE (stopflag=0) DO
BEGIN
IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
CALL getTableCount(database_2, table_name, resoult_count);
IF (resoult_count <> 0) THEN
CALL getColumnCount(database_2, table_name, column_name, resoult_count);
IF (resoult_count <> 0) THEN
SET column_info = 'DATA_TYPE';
CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
IF (result_data_1 <> result_data_2) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一樣;\n');
ELSE
SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一樣;\n');
END IF;
END IF;
SET column_info = 'CHARACTER_SET_NAME';
CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
IF (result_data_1 <> result_data_2) THEN
IF (this_info IS NULL OR this_info='') THEN
SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一樣;\n');
ELSE
SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一樣;\n');
END IF;
END IF;
END IF;
ELSE
SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
END IF;
ELSE
IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
CALL getTableCount(database_1, table_name, resoult_count);
IF (resoult_count = 0) THEN
SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
END IF;
END IF;
END IF;
FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
END;
END WHILE;
CLOSE sql_resoult;
END IF;
SET info=this_info;
END
&
DELIMITER ;
SET @database_1='my_test3';
SET @database_2='my_test4';
SET @tableExistInfo='';
SET @columnExistInfo='';
SET @columnInfo='';
CALL comparisonTableExist(@database_1, @database_2, @tableExistInfo);
CALL comparisonColumnExist(@database_1, @database_2, @columnExistInfo);
CALL comparisonColumnInfo(@database_1, @database_2, @columnInfo);
SET @info=CONCAT(@tableExistInfo, '\n', @columnExistInfo, '\n', @columnInfo);
SELECT @info;
DROP PROCEDURE IF EXISTS `comparisonColumnInfo`;
DROP PROCEDURE IF EXISTS `comparisonColumnExist`;
DROP PROCEDURE IF EXISTS `comparisonTableExist`;
DROP PROCEDURE IF EXISTS `getColumnInfo`;
DROP PROCEDURE IF EXISTS `getColumnCount`;
DROP PROCEDURE IF EXISTS `getTableCount`;
DROP PROCEDURE IF EXISTS `getdatabaseCount`;總結(jié)
本篇文章就到這里了，希望能夠給你帶來(lái)幫助，也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容!
您可能感興趣的文章:
- MySql數(shù)據(jù)庫(kù)單表查詢與多表連接查詢效率對(duì)比
- Mysql數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎對(duì)比
- php使用mysqli和pdo擴(kuò)展，測(cè)試對(duì)比mysql數(shù)據(jù)庫(kù)的執(zhí)行效率完整示例
- php使用mysqli和pdo擴(kuò)展，測(cè)試對(duì)比連接mysql數(shù)據(jù)庫(kù)的效率完整示例
- MySQL數(shù)據(jù)庫(kù)三種常用存儲(chǔ)引擎特性對(duì)比
- PHP數(shù)據(jù)庫(kù)連接mysql與mysqli對(duì)比分析
相關(guān)文章
Mysql中Table ‘XXX’ is marked as crashed and last (automatic?)
這篇文章主要介紹了Mysql中Table ‘XXX’ is marked as crashed and last (automatic?)問(wèn)題解決方法,需要的朋友可以參考下2014-05-05
這篇文章主要介紹了MySQL 全文索引的原理與缺陷，幫助大家更好的理解和使用MySQL中的索引，感興趣的朋友可以了解下2020-12-12
mysql函數(shù)group_concat()返回結(jié)果不全問(wèn)題
文章介紹了MySQL中使用group_concat()函數(shù)拼接返回字段時(shí)遇到的字符長(zhǎng)度問(wèn)題,分析了問(wèn)題原因,并提供了兩種解決方法：臨時(shí)修改group_concat_max_len配置和永久修改MySQL配置文件2024-11-11
MySQL是一種關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng),最常用的版本是5.6和5.7,mysql5.7是5.6的新版本,在沒(méi)有減少功能的情況下新增了功能與進(jìn)行了優(yōu)化,例如新增了新的優(yōu)化器、原生JSON支持、多源復(fù)制,還優(yōu)化了整體的性能、GIS空間擴(kuò)展、InnoDB...2024-03-03
MySQL(community版)壓縮包安裝的超詳細(xì)教程
這篇文章詳細(xì)介紹了如何在Windows系統(tǒng)上手動(dòng)安裝MySQL?8.0.26的壓縮包版本,包括下載、解壓、配置環(huán)境變量、初始化、啟動(dòng)服務(wù)、設(shè)置密碼等步驟,同時(shí),還提供了一個(gè)簡(jiǎn)單的SQL基礎(chǔ)教程,需要的朋友可以參考下2025-03-03
這篇文章主要介紹MySQL 主鍵與索引的聯(lián)系與區(qū)別，使用mysql的朋友可以看下2013-02-02
將mysql腳本轉(zhuǎn)化為oracle腳本的攻略與細(xì)節(jié)點(diǎn)
前段時(shí)間公司項(xiàng)目數(shù)據(jù)庫(kù)需要從mysql轉(zhuǎn)為oracle,所以需要修改下原有的mysql腳本,這篇文章主要給大家介紹了關(guān)于將mysql腳本轉(zhuǎn)化為oracle腳本的攻略與細(xì)節(jié)點(diǎn),需要的朋友可以參考下2023-09-09
SQL Server數(shù)據(jù)庫(kù)錯(cuò)誤5123解決方案
這篇文章主要介紹了SQL Server數(shù)據(jù)庫(kù)錯(cuò)誤5123解決方案,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-06-06
在SQL查詢中,經(jīng)常需要按多個(gè)字段對(duì)結(jié)果進(jìn)行排序,本文主要介紹了MySql查詢中按多個(gè)字段排序的方法,具有一定的參考價(jià)值,感興趣的可以了解一下2024-03-03
在實(shí)際的項(xiàng)目開(kāi)發(fā)中有時(shí)會(huì)有對(duì)數(shù)據(jù)庫(kù)某字段截取部分的需求,這種場(chǎng)景有時(shí)直接通過(guò)數(shù)據(jù)庫(kù)操作來(lái)實(shí)現(xiàn)比通過(guò)代碼實(shí)現(xiàn)要更方便快捷些,這篇文章主要給大家介紹了關(guān)于MySQL實(shí)現(xiàn)字符串截取的相關(guān)資料,需要的朋友可以參考下2022-03-03