MySQL简单技巧(三)：教你轻松用information_schema表+python实现表结构同步(上)——理论篇

最新推荐文章于 2023-10-27 10:04:38 发布
最新推荐文章于 2023-10-27 10:04:38 发布
阅读量1k 收藏 1
点赞数
分类专栏： devops mysql 文章标签： 表结构同步 information_sc MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42681866/article/details/104623358
版权

文章目录

前文

  为了打通开发、测试、生产环境的MySQL数据库表一致性，以及减少研发的操作，比如需要上线一个功能，那就需要在三个环境都修改表，一方面带来繁琐的操作；另一方面当测试测成功上线时，总发现自己遗忘了某某表的修改，这无疑给项目的整个迭代发布带来了巨大的麻烦。所以引入数据表结构同步功能，来实现三套环境的表结构一致，对加速程序的开发迭代也是重要的一环。
  于是去年在领导的要求下基于Python实现了一套表结构工具，当时未找到相关的开源软件而只能自己造轮子，并且在轮子造完后就发现了2套开源表结构同步工具，一套基于go，一套基于Python，还是比较可惜！这里分享下我基于information_schema这个库所实现的表结构功能，相关代码会放到github上。
  以下是两套开源的表结构同步：

  我实现的这套相对简单易操作，当然也有许多需要完善的地方，有兴趣的小伙伴儿可以一起探讨。这套表结构代码是基于库以表为单位，比较不同库下的表结构差异，并且仅实现了字段、索引的同步，其他比如分区相关的就未具备同步功能。

什么是information_schema

  MySQL自带四个库，分别是sys、mysql、information_schema、performance_schema，后两者分别掌管着MySQL的数据库信息和性能信息，这个数据库信息就是我们需要的，所以锁定information_schema来实现表结构同步。通过show tables可以看到：
在这里插入图片描述
  可以看到自带的表是非常多的，包括字符编码(charset)、引擎(engine)、账号信息(session)、各色的权限(privileges)等等，但这些都和表结构同步无关，所以只要围绕以下四个表就行了：

  1. SCHEMATA：即schemata，记录了所有数据库的基本信息，即库名、编码
  2. TABLES：即tables，记录了所有数据表的基本信息，包括库名、表名、编码、引擎等
  3. COLUMNS：即columns，记录了所有字段的基本信息，包括库名、表名、字段名、字段类型、字段编码等
  4. STATISTICS：即statistics，记录了所有索引的基本信息，包括库名、表名、索引名、索引字段等

  接下来我会介绍在这些表里相关的字段信息，不过一些无用的字段我就不会跳过了，如果你对所有字段都很有兴趣的话，可以查看官网：MySQL官网介绍information_schema。

SCHEMATA介绍

  首先看schemata，这个跟库相关的信息非常的简单(下面仅展示有用的字段)，仅仅是库相关的编码和库名，而库不是我们比较的单位，所以这个库基本了解就行：
在这里插入图片描述

字段介绍
SCHEMA_NAME数据库名
DEFAULT_CHARACTER_SET_NAME数据库对应的字符编码，即charset
DEFAULT_COLLATION_NAME数据库对应的比较规则，即collation
TABLES介绍

  关于tables有许多表自带的属性，比如版本(.frm file version，8.0已废弃未使用)、行格式(row_format)、行数据平均估值(avg_row_length)、当前自增值(Auto_increment)等等对于表结构对比是无用的，可以忽视。重要的都归结在表格里。
在这里插入图片描述

字段介绍
TABLE_SCHEMA数据库名
TABLE_NAME数据表名
ENGINE数据表引擎，比如memory、engine、myisam
TABLE_COLLATION数据表字符编码+字符规则，比如utf8_general_ci，前缀utf8即编码
TABLE_COMMENT数据表注释

  由于此次代码未实现关于表编码、注释、引擎的对比，所以不详细介绍这块，不过基于这块，所涉及字段较少，可以很轻松的实现表相关的字段对比！

COLUMNS介绍

  columns表即是跟MySQL字段相关的，不过其中也涉及了很多跟表结构无关的字段，比如CHARACTER_MAXIMUM_LENGTH(字符串字段，字符的最大长度)、CHARACTER_OCTET_LENGTH(字符串字段，数据的最大长度)这类的，详细如下：
在这里插入图片描述
太多字段记不住，只要注意以下几个字段即可：

字段介绍
TABLE_SCHEMA数据库名
TABLE_NAME数据表名
COLUMN_NAME字段名
ORDINAL_POSITION字段位于表里的顺序，从1、2依次往下排
COLUMN_DEFAULT字段默认值
IS_NULLABLE字段是否为空，值为yes、no
DATA_TYPE字段类型,比如varchar
CHARACTER_SET_NAME字段编码，比如utf8
COLLATION_NAME字段比较规则编码，比如utf8_general_ci
COLUMN_TYPE字段类型，相对于DATA_TYPE多出了精度和其他信息，比如varchar(32)
COLUMN_KEY当前字段的索引类型，无则空，有比如：PRI、MUL
EXTRA一些其他信息，比如auto_increment、timestamp类型on update CURRENT_TIMESTAMP
COLUMN_COMMENT字段默认值

  可以看到字段有12个之多，那如果生成的sql要根据12个字段来比较并生成可见是非常麻烦的，这里可以借助set的对称差集^来做，如果有差集则说明二者有不同的地方(也可以借助hash对所有字段哈希取值进行比较)，这个会在后文代码处介绍。然后有不同就通过modify来实现字段的变更。所以重点关注COLUMN_NAME(字段名)、COLUMN_TYPE(字段类型)、IS_NULLABLE(是否not null)、COLUMN_DEFAULT(是否有默认值)、EXTRA(是否有auto_increment或其他)、COLUMN_COMMENT(是否有字段注释)，根据这些字段，就能轻松的拼凑一条modify语句！

STATISTICS介绍

  来聊聊索引的statistics，详细字段如下图，其中比如CARDINALITY用来统计该索引的不重复值、SEQ_IN_INDEX(索引顺序)这类的对表结构帮助不大的都可剔除：
在这里插入图片描述
总结需要记录的字段如下：

字段介绍
TABLE_SCHEMA数据库名
TABLE_NAME数据表名
NON_UNIQUE是否唯一索引，0为唯一，1为普通索引
INDEX_SCHEMA索引所在的数据库，和table_schema一致
INDEX_NAME索引名
SEQ_IN_INDEX索引所在位置，等同于ORDINAL_POSITION，也是从1开始排序
COLUMN_NAME索引所在的字段
COLLATION索引里的字段排序规则
CARDINALITY索引里的不重复值预估
SUB_PART前缀索引的部分，比如idx_age(10)，就是括号的10，取前10个字符
NULLABLE字段是否可以为空
INDEX_TYPE索引类型：BTREE, FULLTEXT, HASH, RTREE
INDEX_COMMENT人为加的索引注释

  虽然涉及的字段也很多，不过扣除COLLATION、INDEX_COMMENT这种关系不大的字段，只要记住INDEX_NAME(索引名)、COLUMN_NAME(索引所在字段)、NON_UNIQUE(是否唯一索引)、SUB_PART(是否是前缀索引)这几个字段就能拼凑出一条add index的语句，而index_type这种可以强制要求btree索引才进行表结构同步，当然你也可以根据你的需求来获取你所需的字段进行比对！

表结构同步的基本原理

  表结构同步是为了比对开发、测试、生产乃至多个环境间的表差异，那么我们的比对可以以表为单位，一次可以选取多个表来完成。并且因为只是环境件的差异，所以比对的库名和表名必须一致！针对目标环境未存在的表，则生成创建语句。具体步骤如下：

  1. 连接：选定本地库和目标库的ip、port、database、user、pass来进行连接
  2. 保存：同时获取远程和本地的columns、statistics、tables的结果并存储
  3. 选择：本地选定需要比对的表，可以是单表或者多表
  4. 是否比对：如果是单表，寻找目标库是否有，有则进行比对环节，无则生成创建语句(基于show create table生成)；如果是多表，可逐一遍历，然后进行单表的判断
  5. 比对环节：for循环遍历本地表所有字段，将字段逐一去目标表的字段遍历查找(由于单表字段一般不超过50，所以即使双重for，效率影响也不大)；没找到，生成add语句；找到了，通过set比对差集，如果存在差集生成modify语句，不存在则表明一致！基于index的也一样，区别是index没有modify语句，所以可以通过drop+add的形式来更改索引名，也就是删除原来的索引树并新建一颗索引树！
  6. 合并：将所有生成的sql以逗号合并，形成针对一张表修改的sql语句；再将所有表的sql语句通过分号合并，生成针对所选表的修改。

  所以，手动实现一套表结构对比也不是特别难。而针对网上已存在的，很难定制出自己所需要的，比如add…after…这类的，很多开源工具不支持，而如果公司着实需要的话，就可以根据ORDINAL_POSITION这个字段来生成，这就显得非常灵活。

总结

  关于表结构同步理论篇就介绍到这，下篇记录代码实战，而关于这块还有很多待完善的，也希望大家多提建议~

sandwu
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Python 数据库表结构同步
Python+大数据+数据分析+自动化+Vue组件开发
09-04 509
近日，某个QQ 群里的一个朋友提出一个问题，如何将一个DB 的表结构同步给另一个DB。 针对这个问题，我进行了思考与实践，具体的实现代码如下所示： # coding:utf-8 import pymysql dbDict = {"test1":"l-beta.test1"} dbUser = "test" dbPassword = "123456" class DBUtils(): def __init__(self): self.conn = pymysql.connec
python创建数据库并对表结构备份_Python 如何实现数据库表结构同步
weixin_39927848的博客
12-04 148
近日，某个QQ 群里的一个朋友提出一个问题，如何将一个DB 的表结构同步给另一个DB。针对这个问题，我进行了思考与实践，具体的实现代码如下所示：# coding:utf-8import pymysqldbDict = {"test1":"l-beta.test1"}dbUser = "test"dbPassword = "123456"class DBUtils():def __init__(se...
python实现mysql跨库的表结构和表数据自动同步
qy_getforward的博客
12-01 1719
准备对某个数据库重要的数据字典表进行变更监控。首先需要更具传入的来源表 进行目标表的自动创建和表数据的同步。 核心代码如下： 自动创建表结构 来源库执行 ：create_sql = show create table tablename 目标库执行 excute(create_sql) 自动同步表数据 q_res = select count（*）from information_schema.COLUMNS where table_name =tablename str_str = '%s,'*(int
python实现mysql异库表数据同步，so easy
qq_16497185的博客
10-27 615
python实现mysql表数据同步
python 映射表结构_把MySQL表结构映射为Python中的对象的教程
weixin_39586526的博客
12-08 277
ORMmysql的表结构是二维表，用python的数据结构表示出来就是一个列表，每一个记录是一个tuple。如下所示：[('1', ''huangyi),('2', ''letian),('3', 'xiaosi')]这一行并不便于看出表的结构，可以把它换成对象的形式。class User(object):def __init__(self, id, name):self.id = idself....
python 比较 mysql 表结构差异
最新发布
06-05
这可以通过查询`INFORMATION_SCHEMA.COLUMNS`表来实现，该表包含了所有数据库中表的所有列信息。我们可以为每个表构造一个包含列名、数据类型、是否为主键等信息的元组列表。 3. **比较表结构**：在获取了两个...
python_database_backup:用于备份数据库和_或特定表的 Python 脚本
05-29
下面是一个简单的示例，展示了如何使用 Python 和 `mysql-connector-python` 备份 MySQL 数据库中的所有表： ```python import mysql.connector from mysql.connector import Error def backup_database...
Python实现Mysql数据统计及numpy统计函数
09-09
在Python编程中，数据统计是数据分析的核心环节，而MySQL作为常用的关系型数据库，经常用于存储大量结构化数据。本文将深入探讨如何利用Python与MySQL交互进行数据统计，并介绍numpy库中的统计函数，帮助我们高效...
mysql导出表的字段和相关属性的步骤方法
12-16
在MySQL数据库管理中，有时我们需要将表的结构信息导出，包括字段名、数据类型、长度、是否可为空、默认值以及备注等属性，以便于文档化或迁移数据。以下是一个详细步骤，指导如何完成这一任务： 1. **确定目标...
Python3自动生成MySQL数据字典的markdown文本的实现
09-08
6. **查询表结构**：从`information_schema`系统表中获取表的结构信息，如字段名、字段类型、是否为主键等。 7. **拼装Markdown文本**：逐个字段处理，生成Markdown格式的字段描述，并写入文件。 8. **关闭连接**...
Python 如何实现数据库表结构同步
12-16
近日，某个QQ 群里的一个朋友提出一个问题，如何将一个DB 的表结构同步给另一个DB。 针对这个问题，我进行了思考与实践，具体的实现代码如下所示： # coding:utf-8 import pymysql dbDict = {"test1":"l-beta.test1"} dbUser = "test" dbPassword = "123456" class DBUtils(): def __init__(self): self.conn = pymysql.connect(dbDict['test1'], dbUser, dbPassword) self.cursor
python实时同步数据库数据
10-23
从数据直抽到数据库，通过配置表的形式，可以根据主键实时将生产库的表同步到ods层面，支持增加和修改，不支持删除操作。
使用C#进行数据库增删改查ADO.NET(一)
.NET 致知的博客
11-16 680
这节讲一下如何使用C#进行数据库的增删改查操作，本节以SQL Server数据库为例。 .NET 平台，使用ADO.NET 作为与数据库服务器的桥梁，我们通过ADO.NET就可以使用C#语言操作数据库，它的命名空间在System.Data下，要访问SQLServer数据库，就要引用其下的System.Data.SqlClient空间，若使用别的数据库，就要下载对应的包。例如，访问MySql数据库就要下载MySql.Data包。 我们用C#连接数据库，首先要连接数据库，连接数据...
使用Python 比较mysql数据库中两个数据库的表结构，并导出差异部分的表结构
tenaguan4461的博客
09-01 6586
在web开发中，通常情况下，新增功能或者修改功能涉及到数据库的表结构修改、或者是新增表，删除无用的表的情况下，都需要先在测试环境对表结构进行修改，当涉及到的功能越来越多时，对表结构的修改越来越多的时候，正式环境上线的时候，就很有可能遗漏某张表，或者遗漏某张表的某个字段，这时候就需要利用自动化的工具防止出现这种情况，一般可以使用migration等工具控制这种情况的发生，但是针对没有使用这类自动化工具的时候，我们可以用Python写一个脚本检测比较正式上线的数据库和测试开发的数据库的表结构差异，避免出错！
mysql schema 同步_Python 实现 MySQL Schema 同步
weixin_28995139的博客
01-21 310
前言在日常的工作中，经常会遇到不同环境之间同步表结构的需求，特别是在生产与测试环境之间，在长时间的运行后，总会有不一致的情况。数据库表结构变更的正常流程是在测试环境验证之后，通过工单的形式在生产环境上执行，但生产环境经常会遇到索引的修改，紧急修改字段长度等操作，这些操作很多时候并没有在测试环境中执行，久而久之就造成了环境之间表结构的不一致。SchemaObjectSchemaObject 是一个将...
MySQL简单技巧(四)：教你轻松用information_schema表+python实现表结构同步(下)——实战篇
weixin_42681866的博客
03-07 619
文章目录前文代码说明总结 前文   紧接着上篇MySQL简单技巧(三)：教你轻松用information_schema表+python实现表结构同步(上)——理论篇，这篇进入实战，也就是基于Python来实现一套表结构同步！   代码放于github，链接：https://github.com/sandwu/db_diff.git。 代码说明   代码是基于前端代码修改的，为了在前端显示友好，所以...
用python比较不同环境下数据库表与表结构的信息
zhanghuihua911zha的博客
02-07 936
领导的要求：同一个系统有多个环境，数据库用的是mysql ,需要比较一下他们的表和表结构信息，由于网络问题不能直接用mysqldiff 工具比较，所以我用了python下pandas 、list、pymysql来操作。 操作思路： 比较表 1.用pymysql 连接测试环境1和测试环境2的数据库； 2.读取表的信息，转存在pandas 的数据框中df1和df2； 3.读取数据框中的表名，转成列表格式，就可以比对两个表的异同点； 4.转存表信息的数据框为excel文件 比较表结构 1.相同表的列表，读取
将Excel导入MySQL，并解决乱码问题
weixin_42681866的博客
07-15 4358
前文   工作中经常需要将业务方的数据导入数据库，而这两者通常就是Excel和MySQL。MySQL本身对文件导入导出支持还是比较充分的，但碰到乱码、导入格式不对就比较糟心！这里就提供解决思路和方案！ 从Excel导入MySQL   首先确定要导入的Excel各个字段，那么我们定义demo.xlsx这么一个Excel表，里面的测试数据如下：   导入MySQL步骤如下： 将Excel另存为tx...
> 1044 - Access denied for user 'root'@'localhost' to database 'information_schema'
12-09
这个错误通常是由于用户没有足够的权限来访问所请求的数据库而引起的。这可能是因为用户没有被授权访问该数据库，或者是因为用户使用的凭据不正确。 要解决这个问题，你可以尝试以下几个步骤： 1. 确认你使用的用户名和密码是否正确，并且该用户被授权访问所请求的数据库。 2. 确认你正在连接到正确的数据库。有时候，用户会错误地尝试连接到information_schema数据库，而这个数据库是只读的，不允许用户进行更改。 3. 确认你正在使用正确的主机名和端口号连接到数据库。如果你正在使用远程连接，你需要确保你的MySQL服务器已经配置为允许远程连接，并且你的防火墙没有阻止连接。 以下是一个示例代码，可以用来检查你的MySQL连接是否正常： ```python import mysql.connector try: cnx = mysql.connector.connect(user='root', password='your_password', host='localhost', database='your_database') print("Connection successful!") cnx.close() except mysql.connector.Error as err: print("Something went wrong: {}".format(err)) ```
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值