表结构对比版本

游戏研发中常遇到跨版本执行数据库脚本的事情。一般都是采用按版本号依次准备脚本的方式。更新时依次执行所跨版本的脚本语句。如何其中出现遗落也不好确定。所以一直琢磨是否可以把数据库的表结构输出成文本。然后使用Beyond Compare 对比工具。这样改变的表结构一目了然。也可以用于确定没遗落更新。安全性大大提高。

Shell脚本：CheckDbTableData.sh

#!/bin/bash
#sh /data/sqldata/CheckDbTableData/CheckDbTableData.sh >> /data/sqldata/CheckDbTableData/check_table_data.log
echo "CheckDbTableData Begin"
cd /data/sqldata/CheckDbTableData
mysql -uroot -pyuia1981 -h 10.33.10.6 -P 3306 -Dfinal_login_db0 <CheckDbTableData.sql

echo "CheckDbTableData End"

导出表结构脚本：CheckDbTableData.sql

SET CHARACTER_SET_CLIENT=utf8;
SET CHARACTER_SET_CONNECTION=utf8;

DELIMITER $$

DROP PROCEDURE IF EXISTS `CheckDbTableDataSql`$$

CREATE PROCEDURE `CheckDbTableDataSql`()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE tempCount INT DEFAULT 0;

    DECLARE dbSql VARCHAR(512) DEFAULT '';
    DECLARE dbNameSql VARCHAR(512) DEFAULT '';
    DECLARE checkDbSql VARCHAR(512) DEFAULT '';

    DECLARE tableName VARCHAR(128) DEFAULT '';
    DECLARE checkSrceSql VARCHAR(1024) DEFAULT '';
    DECLARE runSrceSql VARCHAR(1024) DEFAULT '';
    DECLARE calTableSql VARCHAR(1024) DEFAULT '';

    DECLARE tempAllCount INT DEFAULT 0;
    DECLARE tempTableCount INT DEFAULT 0;

    -- 获取当前数据库
    prepare stmt from "SELECT database() INTO @dbSql";
    EXECUTE stmt;
    SELECT @dbSql;

    -- 循环导入数据
    SET @checkSql=CONCAT("SELECT COUNT(1) into @tempCount FROM information_schema.TABLES WHERE TABLE_SCHEMA = '",@dbSql,"'");
    prepare stmt from @checkSql;
    EXECUTE stmt;

    SET @checkSrceSql=CONCAT("SELECT table_name into @tableName FROM information_schema.TABLES WHERE TABLE_SCHEMA = '",@dbSql,"' ORDER BY lower(table_name) LIMIT [##],1");
    WHILE i < @tempCount DO
        SET @runSrceSql=REPLACE(@checkSrceSql,'[##]',i);
        prepare stmt from @runSrceSql;
        EXECUTE stmt;
        SELECT lower(@tableName);

        -- 循环输出表字段信息
        SET @calTableSql=CONCAT("SELECT lower(COLUMN_NAME),COLUMN_TYPE FROM information_schema.COLUMNS where table_name='",@tableName,"' AND TABLE_SCHEMA='",@dbSql,"' ORDER BY lower(COLUMN_NAME)");
        prepare stmt from @calTableSql;
        EXECUTE stmt;

        SET i=i+1;
    END WHILE;
END$$

DELIMITER $$

CALL CheckDbTableDataSql();