mysql schema 同步_Python 实现 MySQL Schema 同步

前言

在日常的工作中，经常会遇到不同环境之间同步表结构的需求，特别是在生产与测试环境之间，在长时间的运行后，总会有不一致的情况。数据库表结构变更的正常流程是在测试环境验证之后，通过工单的形式在生产环境上执行，但生产环境经常会遇到索引的修改，紧急修改字段长度等操作，这些操作很多时候并没有在测试环境中执行，久而久之就造成了环境之间表结构的不一致。

SchemaObject

SchemaObject 是一个将 MySQL Schema 转换成 Python 对象的一个工具包，方便了后面的 Schema 对比。下面是 SchemaObject 大概的一个类图：

未命名文件.png

SchemaObject 将数据库中的 table, colume, index 等变成了一个 Python 类，每个类都有 create drop modify 方法，这些方法都能产生当前数据库类型的 SQL 语句。比如，TableSchema 类的 create 方法就能生成表的建表语句。这样通过比对就能快速地生成同步 Schema 的 SQL 语句了。

另外注意下 SchemaObject 依赖 PyMySQL，如果是使用的是 Python3 的版本，需要修改下源码中 connection.py 中 72 行 ：

# Python2

if sys.version_info < (3, 0):

if isinstance(values, (basestring, unicode)):

values = (values,)

else:

# Python3

if isinstance(values, (str, bytes)):

values = (values,)

比对 Schema

获取信息

在比对 Schema 之前，需要先获取不同类型的 Scheme 信息，虽然 SchemaObject 工具包都将这些 Schema 信息的采集方法封装好了，我们还是来看下它是如何获取这些信息的：

database option 信息通过 information_schema.SCHEMATA 表获取

SELECT

SCHEMA_NAME,

DEFAULT_CHARACTER_SET_NAME,

DEFAULT_COLLATION_NAME

FROM information_schema.SCHEMATA

table 信息通过 information_schema.TABLES 表获取

SELECT TABLE_NAME, ENGINE, ROW_FORMAT, AUTO_INCREMENT,

CREATE_OPTIONS, TABLE_COLLATION, TABLE_COMMENT, TABLE_SCHEMA

FROM information_schema.`TABLES`

WHERE TABLE_SCHEMA=''

AND not isnull(ENGINE)

column 信息通过 information_schema.COLUMNS 表获取

SELECT

TABLE_NAME,

COLUMN_NAME,

ORDINAL_POSITION,

COLUMN_DEFAULT,

IS_NULLABLE,

COLUMN_TYPE,

COLUMN_KEY,

CHARACTER_MAXIMUM_LENGTH,

CHARACTER_SET_NAME,

COLLATION_NAME,

EXTRA,

COLUMN_COMMENT,

TABLE_SCHEMA,

TABLE_NAME

FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = ''

AND TABLE_NAME = 'core_account'

ORDER BY ORDINAL_POSITION;

索引信息获取

SHOW INDEXES FROM ``.`

外键约束信息获取

SELECT

K.CONSTRAINT_NAME,

K.TABLE_SCHEMA,

K.TABLE_NAME,

K.COLUMN_NAME,

K.REFERENCED_TABLE_SCHEMA,

K.REFERENCED_TABLE_NAME,

K.REFERENCED_COLUMN_NAME,

K.POSITION_IN_UNIQUE_CONSTRAINT

FROM information_schema.KEY_COLUMN_USAGE K,

information_schema.TABLE_CONSTRAINTS T

WHERE K.CONSTRAINT_NAME = T.CONSTRAINT_NAME

AND T.CONSTRAINT_TYPE = 'FOREIGN KEY'

AND K.CONSTRAINT_SCHEMA = ''

AND K.TABLE_NAME = '

AND K.REFERENCED_TABLE_NAME IS NOT NULL

# 获取 FK 的 update rule, delete rule

SELECT

UPDATE_RULE,

DELETE_RULE

FROM information_schema.REFERENTIAL_CONSTRAINTS

WHERE CONSTRAINT_SCHEMA = ''

AND TABLE_NAME = '

AND CONSTRAINT_NAME = ''

对比表

当原库中表不在目标库中存在，【新建表】：

for s_t in from_tables:

if s_t not in target_tables:

# Generate Create Table SQL, 调用 SchemaObject.TableSchema create 方法生成建表语句

s_t.create()

当目标库中表不在原库中存在，【删除表】:

for t_t in target_tables:

if t_t not in from_tables:

# Generate Drop Table SQL, 调用 SchemaObject.TableSchema drop 方法生成删表语句

t_t.drop()

对比字段

当原表中的字段不在目标表中存在，【新建字段】：

for s_col in from_cols:

if s_col not in target_cols:

# Generate Add Col SQL, 调用 SchemaObject.ColumnSchema create 方法生成新增字段语句

s_col.create()

当目标表中的字段不在原表中存在，【删除字段】：

for t_col in target_cols:

if t_col not in from_cols:

# Generate Drop Col SQL, 调用 SchemaObject.ColumnSchema drop 方法生成删除字段语句

t_col.drop()

当原表中的字段在目标表中存在，但字段类型不同，【修改字段】：

for idx, s_col in enumerate(from_cols):

if s_col in to_cols and s_col != to_cols[s_col]:

# Generate Modify Col SQL, 调用 SchemaObject.ColumnSchema modify 方法生成修改字段语句

s_col.modify()

对比 Index FK

对比索引，FK 跟对比字段的流程一样，不同的是在 Drop Index 之前需要先执行 create, modify, drop FK 的操作，不然 Drop Index 会出现外键约束。

小结

根据上诉这些对比，就能生成一个 Schema 同步的 SQL 语句。同时 SchemaObject 的作者也写了一个 SchemaSync 包来实现了上诉的对比，生成 同步 SQL 的过程。更多的详情可看 SchemaSync 的源码获取。

注意，SchemaSync 同样不支持 Python3，笔者对 SchemaSync 进行了修改，支持了 Python3，同时能直接将 SQL 在目标库中执行，请见：https://github.com/hctech/mysql-schema-sync。

参考