Mysql 升级如何对比表结构差异

最近在升级公司的一个项目时，由于需要升级的版本与现场版本的时间跨度过大，研发在进行开发的时候没有记录sql变更，导致一时间难以弄清楚，需要做哪些sql调整，梳理下怎么比较处数据库结构的差异。

比较表差异

• 查出所有表 (测试环境和现场环境)

SELECT table_name, table_schema FROM information_schema.tables 
where table_schema = 'your database';

• 解析数据（node.js）

const { STR, FILE } = require("../../libs");


function compare() {
    let nowTable = readTable("tables1.sql");
    let oldTables = readTable("tables.sql");
    let diff = []
    nowTable.forEach(x => {
        if (!oldTables.includes(x)) {
            diff.push(x);
        }
    })

    FILE.writeJson(`${__dirname}/table_diff.json`, diff, true);
}

function readTable(file) {
    let content = FILE.read(`${__dirname}/${file}`)
    let tables = []
    STR.splitToLines(content, "\r\n").forEach(x => {
        let names = x.split("|");
        tables.push(names[1].trim());
    })

    return tables;

}

compare();

• 输出差异结果    

[
	"evs_template_page_language",
	"evs_ai_archive_bind",
	"evs_ai_store",
	"evs_content_property_language",
	"evs_content_property_rich_text_language",
	"evs_model_language",
	"evs_model_property_language",
	"evs_stream_server_log",
	"evs_template_page_language",
	"evs_video_meeting",
	"evs_video_meeting_log",
	"evs_video_raw_copy"
]

比较字段差异

• 查出所有字段   

SELECT table_name, table_schema,column_name FROM INFORMATION_SCHEMA.COLUMNS where table_schema = 'your database';

•  解析字段（js）

const { STR, FILE, ARRAY } = require("../../libs");


function compare() {
    let nowTable = readTable("log1.sql");
    let oldTables = readTable("log.sql");
    let diff = []
    nowTable.forEach(x => {
       let target= ARRAY.findFirst(oldTables,y=>y.table==x.table&&y.column==x.column);
        if (!target) {
            diff.push(x);
        }
    })

    FILE.writeJson(`${__dirname}/log_diff.json`, diff, true);
}

function readTable(file) {
    let content = FILE.read(`${__dirname}/${file}`)
    let tables = []
    STR.splitToLines(content, "\r\n").forEach(x => {
        let names = x.split("|");
        let data={
            table:names[1].trim(),
            column:names[3].trim(),
        }
        tables.push(data);
    })

    return tables;

}

compare();

• 输出结果

[
	{
		"table": "evs_live",
		"column": "evs_resolution"
	},
	{
		"table": "evs_live",
		"column": "evs_channel_no"
	},
	{
		"table": "evs_model_property",
		"column": "evs_support_multi_language"
	},
	{
		"table": "evs_model_property",
		"column": "evs_model_propertycol"
	},
	{
		"table": "evs_tag",
		"column": "evs_default_tag"
	}
]

上述代码仅参考，还可以检测到字段长度、非空、默认值等变更，更完善的代码请查看

JasmineProject  专业的项目管理工具  免费使用