服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - 草稿整理后mysql两个数据库结构对比

草稿整理后mysql两个数据库结构对比

2022-09-19 20:28xiaostudy Mysql

这篇文章主要为大家详细介绍了mysql两个数据库结构对比结果，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下，希望能够给你带来帮助

1、草稿：

-- 1.将mysql分隔符从;设置为&
DELIMITER &
-- 2.如果存在存储过程getdatabaseCount则删除
DROP PROCEDURE IF EXISTS `getdatabaseCount` &
-- 3.定义存储过程，获取特定数据库的数量
-- (传入参数database_name字符串类型，为数据库名；传出参数count_date整数类型，为数量)
CREATE DEFINER=`root`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT)
    BEGIN
    -- 4.声明变量
    DECLARE $sqltext VARCHAR(1000);
    -- 5.动态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.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 7.执行SQL语句
    EXECUTE stmt;
    -- 8.释放资源
    DEALLOCATE PREPARE stmt;
    -- 9.获取动态SQL语句返回值
    SET count_date = @count_date;
    END
-- 10.定义存储过程结束
&
-- 2.如果存在存储过程getCount则删除
DROP PROCEDURE IF EXISTS `getTableCount` &
-- 3.定义存储过程，获取特定数据库表的数量
-- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传出参数count_date整数类型，为数量)
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.动态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.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 7.执行SQL语句
    EXECUTE stmt;
    -- 8.释放资源
    DEALLOCATE PREPARE stmt;
    -- 9.获取动态SQL语句返回值
    SET count_date = @count_date;
    END
-- 10.定义存储过程结束
&


-- 2.如果存在存储过程getColumnCount则删除
DROP PROCEDURE IF EXISTS `getColumnCount` &
-- 3.定义存储过程，获取特定数据库表列的数量
-- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传出参数count_date整数类型，为数量)
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.动态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.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 7.执行SQL语句
    EXECUTE stmt;
    -- 8.释放资源
    DEALLOCATE PREPARE stmt;
    -- 9.获取动态SQL语句返回值
    SET count_date = @count_date;
    END
-- 10.定义存储过程结束
&

-- 2.如果存在存储过程getColumnInfo则删除
DROP PROCEDURE IF EXISTS `getColumnInfo` &
-- 3.定义存储过程，获取特定数据库表列的信息
-- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传入参数column_info字符串类型，列信息；传出参数result_data字符串类型，信息)
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.动态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.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 7.执行SQL语句
    EXECUTE stmt;
    -- 8.释放资源
    DEALLOCATE PREPARE stmt;
    -- 9.获取动态SQL语句返回值
    SET result_data = @column_info;
    END
-- 10.定义存储过程结束
&
-- 11.如果存在存储过程comparison则删除
DROP PROCEDURE IF EXISTS `comparison` &
-- 12.定义存储过程，获取指定数据库关键词的表列名
-- (传入参数database_n字符串类型，数据库名；传入参数collation_n字符串类型，具体编码类型；传入参数key_name字符串类型，为关键字；传出参数tableColumnNames字符串类型，表列名)
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查询出来的数据库，table_name查询出来的表名，column_name查询出来的列名，collation_name查询出来的具体编码类型
    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.定义游标结束标识，默认为0
    DECLARE stopflag INT DEFAULT 0;
    -- 15.定义游标，其实就是临时存储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.游标结束就设置为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.打开游标
        OPEN sql_resoult;
            -- 18.读取游标中数据，存储到指定变量
            FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
            -- 19.没有结束继续往下走
            WHILE (stopflag=0) DO
                BEGIN
                -- 20.判断数据库是否为输入的数据库名称，和，指定具体编码类型，和，不含.
                IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, "_", table_name)) = 0) THEN
                    -- 21.调用存储过程，获取特定表列关键词的数量
                    CALL getTableCount(database_2, table_name, resoult_count);
                    -- 22.如果数量不等于0，那么记录表列名
                    IF (resoult_count <> 0) THEN
                        CALL getColumnCount(database_2, table_name, column_name, resoult_count);
                        -- 23.拼接字符串，不可直接用传出变量设值
                        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.拼接字符串，不可直接用传出变量设值
                            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, "不一样;
");
                                ELSE
                                    SET this_info=CONCAT(this_info, table_name, "表的", column_name, "列的", column_info, "不一样;
");
                                END IF;
                            END IF;
                        ELSE
                            IF (this_info IS NULL OR this_info="") THEN
                                SET this_info=CONCAT(database_2, "的", table_name, "表的", column_name, "列不存在;
");
                            ELSE
                                SET this_info=CONCAT(this_info, database_2, "的", table_name, "表的", column_name, "列不存在;
");
                            END IF;
                        END IF;
                    ELSE
                        IF (this_info IS NULL OR this_info="") THEN
                            SET this_info=CONCAT(database_2, "的", table_name, "表不存在;
");
                        ELSE
                            SET this_info=CONCAT(this_info, database_2, "的", table_name, "表不存在;
");
                        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, "列不存在;
");
                                ELSE
                                    SET this_info=CONCAT(this_info, database_1, "的", table_name, "表的", column_name, "列不存在;
");
                                END IF;
                            END IF;
                        ELSE
                            IF (this_info IS NULL OR this_info="") THEN
                                SET this_info=CONCAT(database_1, "的", table_name, "表不存在;
");
                            ELSE
                                SET this_info=CONCAT(this_info, database_1, "的", table_name, "表不存在;
");
                            END IF;
                            SET database_table_no=CONCAT(database_table_no, ";", database_1, "_", table_name, ";");
                        END IF;
                    END IF;
                END IF;
                -- 24.读取游标中数据，存储到指定变量。（和18一样）
                FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
                END;
            END WHILE;
        -- 25.关闭游标
        CLOSE sql_resoult;
    ELSE
        IF (database_count_1 = 0 AND database_count_2 = 0) THEN
            SET this_info = CONCAT(database_1, "和", database_2, "数据库不存在或为空数据库");
        ELSE
            IF (database_count_1 = 0) THEN
                SET this_info = CONCAT(database_1, "数据库不存在或为空数据库");
            ELSE
                SET this_info = CONCAT(database_2, "数据库不存在或为空数据库");
            END IF;
        END IF;
    END IF;
    -- 26.把数据放到传出参数
    SET info=this_info;
    END
-- 27.定义存储过程结束
&
-- 28.将mysql分隔符从&设置为;
DELIMITER ;
-- 29.设置变量
SET @database_1="my_test";
SET @database_2="my_test2";
SET @column_info="data_type";
SET @count="";
-- 30.调用存储过程
CALL comparison(@database_1, @database_2, @column_info, @count);
-- 31.打印
SELECT @count;
-- 32.如果存在存储过程则删除
DROP PROCEDURE IF EXISTS `comparison`;

草稿整理后mysql两个数据库结构对比

 

2、整理：

-- 1.将mysql分隔符从;设置为&
DELIMITER &
-- 2.如果存在存储过程getdatabaseCount则删除
DROP PROCEDURE IF EXISTS `getdatabaseCount` &
-- 3.定义存储过程，获取特定数据库的数量
-- (传入参数database_name字符串类型，为数据库名；传出参数count_date整数类型，为数量)
CREATE DEFINER=`root`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT)
    BEGIN
    -- 4.声明变量
    DECLARE $sqltext VARCHAR(1000);
    -- 5.动态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.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 7.执行SQL语句
    EXECUTE stmt;
    -- 8.释放资源
    DEALLOCATE PREPARE stmt;
    -- 9.获取动态SQL语句返回值
    SET count_date = @count_date;
    END
-- 10.定义存储过程结束
&
-- 11.如果存在存储过程getTableCount则删除
DROP PROCEDURE IF EXISTS `getTableCount` &
-- 12.定义存储过程，获取特定数据库表的数量
-- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传出参数count_date整数类型，为数量)
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.动态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.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 16.执行SQL语句
    EXECUTE stmt;
    -- 17.释放资源
    DEALLOCATE PREPARE stmt;
    -- 18.获取动态SQL语句返回值
    SET count_date = @count_date;
    END
-- 19.定义存储过程结束
&

-- 20.如果存在存储过程getColumnCount则删除
DROP PROCEDURE IF EXISTS `getColumnCount` &
-- 21.定义存储过程，获取特定数据库表列的数量
-- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传出参数count_date整数类型，为数量)
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.动态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.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 25.执行SQL语句
    EXECUTE stmt;
    -- 26.释放资源
    DEALLOCATE PREPARE stmt;
    -- 27.获取动态SQL语句返回值
    SET count_date = @count_date;
    END
-- 28.定义存储过程结束
&

-- 29.如果存在存储过程getColumnInfo则删除
DROP PROCEDURE IF EXISTS `getColumnInfo` &
-- 30.定义存储过程，获取特定数据库表列的信息
-- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传入参数column_info字符串类型，列信息；传出参数result_data字符串类型，信息)
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.动态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.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 34.执行SQL语句
    EXECUTE stmt;
    -- 35.释放资源
    DEALLOCATE PREPARE stmt;
    -- 36.获取动态SQL语句返回值
    SET result_data = @column_info;
    END
-- 37.定义存储过程结束
&
-- 38.如果存在存储过程comparisonTableExist则删除
DROP PROCEDURE IF EXISTS `comparisonTableExist` &
-- 39.定义存储过程，对比表是否存在
-- (传入参数database_1字符串类型，数据库名1；传入参数database_2字符串类型，数据库名2；传入参数info字符串类型，库表信息)
CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonTableExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
    BEGIN
    -- 40.声明变量。database_name查询出来的数据库，table_name查询出来的表名
    DECLARE database_name, table_name CHAR(200);
    -- this_info表不存在的记录，database_table_no表不存在的记录跳过重复查询
    DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT "";
    -- database_count_1统计数据库1存在的数量，database_count_2统计数据库2存在的数量，resoult_count统计表存在的数量。如果为0表示不存在
    DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
    -- 41.定义游标结束标识，默认为0
    DECLARE stopflag INT DEFAULT 0;
    -- 42.定义游标，其实就是临时存储sql返回的集合
    DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.COLUMNS t;
    -- 43.游标结束就设置为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
    -- 44.调用存储过程getdatabaseCount，查看两个数据库是否存在，都存在则继续
    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.打开游标
        OPEN sql_resoult;
            -- 46.读取游标中数据，存储到指定变量
            FETCH sql_resoult INTO database_name, table_name;
            -- 47.没有结束继续往下走
            WHILE (stopflag=0) DO
                BEGIN
                -- 48.判断数据库是否为输入的数据库名称1，去除已经比较过数据库2的表不存在
                IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, "_", table_name)) = 0) THEN
                    -- 49.调用存储过程getTableCount，查看表是否存在
                    CALL getTableCount(database_2, table_name, resoult_count);
                    -- 50.如果数量等于0，那么表不存在
                    IF (resoult_count = 0) THEN
                        -- 51.把不存在的表记录下来
                        IF (this_info IS NULL OR this_info="") THEN
                            SET this_info=CONCAT(database_2, "的", table_name, "表不存在;
");
                        ELSE
                            SET this_info=CONCAT(this_info, database_2, "的", table_name, "表不存在;
");
                        END IF;
                        SET database_table_no=CONCAT(database_table_no, ";", database_2, "_", table_name, ";");
                    END IF;
                ELSE
                    -- 52.判断数据库是否为输入的数据库名称2，去除已经比较过数据库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, "表不存在;
");
                            ELSE
                                SET this_info=CONCAT(this_info, database_1, "的", table_name, "表不存在;
");
                            END IF;
                            SET database_table_no=CONCAT(database_table_no, ";", database_1, "_", table_name, ";");
                        END IF;
                    END IF;
                END IF;
                -- 53.读取游标中数据，存储到指定变量。（和46一样）
                FETCH sql_resoult INTO database_name, table_name;
                END;
            END WHILE;
        -- 54.关闭游标
        CLOSE sql_resoult;
    ELSE
        IF (database_count_1 = 0 AND database_count_2 = 0) THEN
            SET this_info = CONCAT(database_1, "和", database_2, "数据库不存在或为空数据库");
        ELSE
            IF (database_count_1 = 0) THEN
                SET this_info = CONCAT(database_1, "数据库不存在或为空数据库");
            ELSE
                SET this_info = CONCAT(database_2, "数据库不存在或为空数据库");
            END IF;
        END IF;
    END IF;
    -- 55.把数据放到传出参数
    SET info=this_info;
    END
-- 56.定义存储过程结束
&

-- 57.如果存在存储过程comparisonColumnExist则删除
DROP PROCEDURE IF EXISTS `comparisonColumnExist` &
-- 58.定义存储过程，对比列是否存在
-- (传入参数database_1字符串类型，数据库名1；传入参数database_2字符串类型，数据库名2；传入参数info字符串类型，库表信息)
CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
    BEGIN
    -- 59.声明变量。database_name查询出来的数据库，table_name查询出来的表名，column_name查询出来的列名
    DECLARE database_name, table_name, column_name CHAR(200);
    -- this_info表不存在的记录，database_table_no表不存在的记录跳过重复查询
    DECLARE this_info, database_table_no TEXT DEFAULT "";
    -- database_count_1统计数据库1存在的数量，database_count_2统计数据库2存在的数量，resoult_count统计表存在的数量。如果为0表示不存在
    DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
    -- 60.定义游标结束标识，默认为0
    DECLARE stopflag INT DEFAULT 0;
    -- 61.定义游标，其实就是临时存储sql返回的集合
    DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROM information_schema.COLUMNS t;
    -- 62.游标结束就设置为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
    -- 63.调用存储过程getdatabaseCount，查看两个数据库是否存在，都存在则继续（同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.打开游标
        OPEN sql_resoult;
            -- 65.读取游标中数据，存储到指定变量
            FETCH sql_resoult INTO database_name, table_name, column_name;
            -- 66.没有结束继续往下走
            WHILE (stopflag=0) DO
                BEGIN
                -- 67.判断数据库是否为输入的数据库名称1，去除已经比较过数据库2的表不存在（同48）
                IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, "_", table_name)) = 0) THEN
                    -- 68.调用存储过程getTableCount，查看表是否存在（同49）
                    CALL getTableCount(database_2, table_name, resoult_count);
                    -- 69.如果数量不等于0，则继续
                    IF (resoult_count <> 0) THEN
                        -- 70.调用存储过程getColumnCount，查看列是否存在。为0说明不存在
                        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, "列不存在;
");
                            ELSE
                                SET this_info=CONCAT(this_info, database_2, "的", table_name, "表的", column_name, "列不存在;
");
                            END IF;
                        END IF;
                    ELSE
                        SET database_table_no=CONCAT(database_table_no, ";", database_2, "_", table_name, ";");
                    END IF;
                ELSE
                    -- 71.判断数据库是否为输入的数据库名称2，去除已经比较过数据库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, "列不存在;
");
                                ELSE
                                    SET this_info=CONCAT(this_info, database_1, "的", table_name, "表的", column_name, "列不存在;
");
                                END IF;
                            END IF;
                        ELSE
                            SET database_table_no=CONCAT(database_table_no, ";", database_1, "_", table_name, ";");
                        END IF;
                    END IF;
                END IF;
                -- 72.读取游标中数据，存储到指定变量。（和65一样）
                FETCH sql_resoult INTO database_name, table_name, column_name;
                END;
            END WHILE;
        -- 73.关闭游标
        CLOSE sql_resoult;
    END IF;
    -- 74.把数据放到传出参数
    SET info=this_info;
    END
-- 75.定义存储过程结束
&

-- 76.如果存在存储过程comparisonColumnInfo则删除
DROP PROCEDURE IF EXISTS `comparisonColumnInfo` &
-- 77.定义存储过程，对比列的不同
-- (传入参数database_1字符串类型，数据库名1；传入参数database_2字符串类型，数据库名2；传入参数info字符串类型，库表信息)
CREATE DEFINER=`root`@`localhost` PROCEDURE comparisonColumnInfo(IN database_1 CHAR(20), IN database_2 CHAR(20),OUT info MEDIUMTEXT)
    BEGIN
    -- 78.声明变量。database_name查询出来的数据库，table_name查询出来的表名，column_name查询出来的列名，collation_name查询出来的具体编码类型
    -- result_data_1数据库1的列信息，result_data_2数据库2的列信息，column_info对比的列（现在只比较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表不存在的记录跳过重复查询
    DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT "";
    -- database_count_1统计数据库1存在的数量，database_count_2统计数据库2存在的数量，resoult_count统计表存在的数量。如果为0表示不存在
    DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
    -- 79.定义游标结束标识，默认为0
    DECLARE stopflag INT DEFAULT 0;
    -- 80.定义游标，其实就是临时存储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.游标结束就设置为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
    -- 82.调用存储过程getdatabaseCount，查看两个数据库是否存在，都存在则继续（同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.打开游标
        OPEN sql_resoult;
            -- 84.读取游标中数据，存储到指定变量
            FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
            -- 85.没有结束继续往下走
            WHILE (stopflag=0) DO
                BEGIN
                -- 86.判断数据库是否为输入的数据库名称1，去除已经比较过数据库2的表不存在（同67）
                IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, "_", table_name)) = 0) THEN
                    -- 87.调用存储过程getTableCount，查看表是否存在（同68）
                    CALL getTableCount(database_2, table_name, resoult_count);
                    -- 88.如果数量不等于0，则继续
                    IF (resoult_count <> 0) THEN
                        -- 89.调用存储过程getColumnCount，查看列是否存在。为0说明不存在（同70）
                        CALL getColumnCount(database_2, table_name, column_name, resoult_count);
                        IF (resoult_count <> 0) THEN
                            -- 90.对比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, "不一样;
");
                                ELSE
                                    SET this_info=CONCAT(this_info, table_name, "表的", column_name, "列的", column_info, "不一样;
");
                                END IF;
                            END IF;
                            -- 91.对比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, "不一样;
");
                                ELSE
                                    SET this_info=CONCAT(this_info, table_name, "表的", column_name, "列的", column_info, "不一样;
");
                                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.读取游标中数据，存储到指定变量。（和84一样）
                FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
                END;
            END WHILE;
        -- 93.关闭游标
        CLOSE sql_resoult;
    END IF;
    -- 94.把数据放到传出参数
    SET info=this_info;
    END
-- 95.定义存储过程结束
&
-- 96.将mysql分隔符从&设置为;
DELIMITER ;
-- 97.设置变量
SET @database_1="my_test1";
SET @database_2="my_test2";
SET @tableExistInfo="";
SET @columnExistInfo="";
SET @columnInfo="";
-- 98.调用存储过程
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, "
", @columnExistInfo, "
", @columnInfo);
-- 99.打印
SELECT @info;
-- 100.如果存在存储过程则删除
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、无注释

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, "表不存在;
");
                        ELSE
                            SET this_info=CONCAT(this_info, database_2, "的", table_name, "表不存在;
");
                        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, "表不存在;
");
                            ELSE
                                SET this_info=CONCAT(this_info, database_1, "的", table_name, "表不存在;
");
                            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, "数据库不存在或为空数据库");
        ELSE
            IF (database_count_1 = 0) THEN
                SET this_info = CONCAT(database_1, "数据库不存在或为空数据库");
            ELSE
                SET this_info = CONCAT(database_2, "数据库不存在或为空数据库");
            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, "列不存在;
");
                            ELSE
                                SET this_info=CONCAT(this_info, database_2, "的", table_name, "表的", column_name, "列不存在;
");
                            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, "列不存在;
");
                                ELSE
                                    SET this_info=CONCAT(this_info, database_1, "的", table_name, "表的", column_name, "列不存在;
");
                                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, "不一样;
");
                                ELSE
                                    SET this_info=CONCAT(this_info, table_name, "表的", column_name, "列的", column_info, "不一样;
");
                                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, "不一样;
");
                                ELSE
                                    SET this_info=CONCAT(this_info, table_name, "表的", column_name, "列的", column_info, "不一样;
");
                                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, "
", @columnExistInfo, "
", @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`;

 

总结

本篇文章就到这里了，希望能够给你带来帮助，也希望您能够多多关注服务器之家的更多内容!  

原文链接：https://www.cnblogs.com/xiaostudy/p/12417151.html

  • mysql
  • 数据库
  • 对比

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 2006 　 1615 　 1215 　 1175 　 10048 　 2003 　 1366 　 28000 　 1577 　 1135 　 1786 　 1093 　 1067 　 1698 　 2002 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图