MySql开发之自动同步表结构

MySql开发之自动同步表结构攻略

在 MySQL 开发项目中，表结构同步通常是一个很耗时的任务，特别是在团队协作开发的情况下。你必须确保所有的开发人员和数据库管理人员都知道这个改动。但是，手工同步表结构的过程往往容易出错，因此我们需要一种自动化的方法。下面就介绍一些方法可以自动同步表结构。

使用 "Flyway" 自动同步表结构

"Flyway" 是一种开源工具，它可以帮助我们自动化地控制数据库 schema 的版本管理。当数据 schema 发生变化时，它可以在原有 schema 的基础上自动升级 schema 并且修改相关的数据。"Flyway" 主要的功能是：

• 自动执行数据库脚本；
• 控制脚本的顺序和执行顺序；
• 非常容易和市场上其他工具集成。

安装和配置

"Flyway" 支持多种语言和数据库，具体的安装和配置方式可以参考 Flyway官方文档。

下面是一个基本的示例 Docker Compose 文件，它使用 "Flyway" 来管理 MariaDB 的数据库 schema 版本。如果你对 "Flyway" 不熟悉，这是一个可以帮助你快速入门的例子。

version: '3'
services:
  flyway:
    image: boxfuse/flyway:7.7.2
    volumes:
      - ${PWD}/migrations:/flyway/sql
    environment:
      - FLYWAY_URL=jdbc:mariadb://mariadb:3306/flyway
      - FLYWAY_USER=root
      - FLYWAY_PASSWORD=root
    depends_on:
      - mariadb

  mariadb:
    image: mariadb:10.5
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - '3306:3306'

上述 Docker Compose 文件中，使用的 "boxfuse/flyway" 镜像包含了基本的 "Flyway" 客户端工具。我们把 MariaDB 作为后端数据存储，它会被 "Flyway" 使用。

在 migrations/ 目录中，我们可以放置 SQL 脚本，"Flyway" 将会在启动时自动执行这些脚本，并记录每一个版本号。

使用 Flyway 进行自动同步

下面我们就来演示 "Flyway" 的自动同步表结构功能。首先，我们在 migrations/ 目录中创建一个 SQL 脚本文件 V1__users.sql，它包含了 Users 数据表的创建 SQL 命令。

CREATE TABLE `Users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

然后，我们将这个 SQL 脚本文件添加到我们的代码版本控制工具中。

接下来，我们将 "Flyway" 服务启动起来，并检查 "Flyway" 是否自动执行了 SQL 脚本并创建了 Users 数据表。

docker-compose up -d

当服务启动完成后，我们可以使用以下命令查看 "Flyway" 是否成功创建了 Users 数据表。

docker-compose exec mariadb mysql -proot -e "use flyway; show tables;"

这将输出一个结果，含有 "Users" 表名。

下面我们演示另一个场景，我们新建了一个 SQL 脚本文件 V2__alter_users_table.sql，它用于修改 Users 数据表的结构。如下是 SQL 命令：

ALTER TABLE `Users` ADD COLUMN `email` varchar(100) NOT NULL AFTER `username`;

我们将这个 SQL 脚本文件加入到 migrations/ 目录，并再次使用 docker-compose up -d 命令重启 "Flyway" 服务。"Flyway" 会自动检测到新的 SQL 脚本文件，并执行它来修改我们的 "Users" 数据表。

这时候我们再次使用 docker-compose exec mariadb mysql -proot -e "use flyway; describe Users;" 查询表结构，将会看到一个新添加的 email 字段。

使用 "Liquibase" 自动同步表结构

"Liquibase" 是另一个开源的数据库 schema 版本管理工具，它支持多种数据库，并提供了非常强大的自动化 schema 升级和同步功能。与 "Flyway" 不同的是，"Liquibase" 不仅支持 SQL 脚本，还支持多种 XML 和 YAML 等格式的脚本定义，并且更加灵活。

安装和配置

"Liquibase" 也支持多种语言和数据库，具体的安装和配置方式可以参考 Liquibase官方文档。

下面是一个基本的示例 Docker Compose 文件，它使用 "Liquibase" 来管理 PostgreSQL 的数据库 schema 版本：

version: '3'
services:
  liquibase:
    image: liquibase/liquibase:4.4.3
    volumes:
      - ${PWD}/changelog:/liquibase/changelog
    environment:
      - LIQUIBASE_URL=jdbc:postgresql://postgres:5432/liquibase
      - LIQUIBASE_USERNAME=liquibase
      - LIQUIBASE_PASSWORD=liquibase
    depends_on:
      - postgres

  postgres:
    image: postgres:13-alpine
    restart: always
    environment:
      - POSTGRES_USER=liquibase
      - POSTGRES_PASSWORD=liquibase
    ports:
      - '5432:5432'

上述 Docker Compose 文件中，我们使用的是 "liquibase/liquibase:4.4.3" 镜像，它也包含了基本的 "Liquibase" 客户端工具。我们将 PostgreSQL 作为后端数据存储。

在 changelog/ 目录中，我们可以放置 XML 或 YAML 脚本，"Liquibase" 将会在启动时自动执行这些脚本，并记录每一个版本号。

使用 Liquibase 进行自动同步

下面我们演示如何在 PostgreSQL 数据库中使用 "Liquibase" 来自动同步表结构。

我们在 changelog/ 目录下创建一个 XML 格式的脚本文件 0001_create_user_table.xml，它用于创建一个名为 user 的数据表。如下是 XML 代码：

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
    <changeSet id="0001" author="alice">
        <createTable tableName="user">
            <column name="id" type="BIGINT" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="name" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>
</databaseChangeLog>

然后，我们将这个 XML 脚本文件添加到我们的代码版本控制工具中。

接下来，我们使用以下命令启动 "Liquibase" 服务：

docker-compose up -d

当服务启动完成后，我们可以使用以下命令检查表结构是否已经创建成功：

docker-compose exec postgres psql -U liquibase -c "SELECT * FROM user;"

现在我们尝试增加一个新列来修改我们的 "user" 数据表。

我们在 changelog/ 目录下新建一个 XML 格式的脚本文件 0002_add_email_column.xml。如下是 XML 代码：

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
    <changeSet id="0002" author="bob">
        <addColumn tableName="user">
            <column name="email" type="VARCHAR(255)"/>
        </addColumn>
    </changeSet>
</databaseChangeLog>

之后，我们再次使用以下命令重启 "Liquibase" 服务，它会自动检测到新添加的 XML 脚本文件和之前版本的差异，并自动升级 "user" 数据表结构。

docker-compose up -d

现在我们可以使用以下命令检查 "user" 数据表是否添加了新的列：

docker-compose exec postgres psql -U liquibase -c "SELECT * FROM user;"

以上就是使用 "Flyway" 和 "Liquibase" 自动化同步表结构的基本流程。