草稿整理后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, '不一样;\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.读取游标中数据，存储到指定变量。（和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`;

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, '表不存在;\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.判断数据库是否为输入的数据库名称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, '表不存在;\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.读取游标中数据，存储到指定变量。（和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, '列不存在;\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.判断数据库是否为输入的数据库名称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, '列不存在;\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.读取游标中数据，存储到指定变量。（和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, '不一样;\n');
                                ELSE
                                    SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n');
                                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, '不一样;\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.读取游标中数据，存储到指定变量。（和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, '\n', @columnExistInfo, '\n', @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, '表不存在;\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, '数据库不存在或为空数据库');
        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, '列不存在;\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`;

总结

本篇文章就到这里了，希望能够给你带来帮助，也希望您能够多多关注编程网的更多内容!

文章详情

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

目录

1、草稿：

2、整理：

3、无注释

总结

软考中级精品资料免费领

相关文章

猜你喜欢

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

mysql怎么对比两个数据库表结构

java实现mysql两个数据库对比表结构是否一致

MySQL实战：如何设计一个常用的后台管理系统的数据库结构