sql - 如何测试 SQL "CREATE TABLE"语句是否等效于现有表？

Question

对于我的 MySQL 数据库中的每个表，我都有一个 mytablename.sql 文件，其中包含该表的 CREATE TABLE 语句。

我想添加一个测试来检查是否没有人在不更新此文件的情况下在实时数据库中添加/更改列 - 我想检查我是否可以使用这些脚本重新创建一个空数据库。

我怎样才能可靠地做到这一点？

一个选项是使用 SHOW COLUMNS FROM mytable(或 DESCRIBE mytable)，在命令行上将以如下表格形式输出:

+---------+------------------+------+-----+---------------------+----------------+
| Field   | Type             | Null | Key | Default             | Extra          |
+---------+------------------+------+-----+---------------------+----------------+
| pk_guid | int(10) unsigned | NO   | PRI | NULL                | auto_increment | 
| d_start | datetime         | NO   | MUL | 0000-00-00 00:00:00 |                | 
| d_end   | datetime         | NO   | MUL | 0000-00-00 00:00:00 |                | 
+---------+------------------+------+-----+---------------------+----------------+

然后创建一个临时表并比较结果。

这很好，但如果任何列已添加到实时数据库中，则结果可能不会按相同的行顺序排列。

不幸的是，似乎无法将 ORDER BY 与 SHOW COLUMNS 一起使用。

另一种选择是使用 SHOW CREATE TABLE，但其中包括诸如 AUTO_INCREMENT 计数器值之类的信息，我不关心这些信息。

有更好的方法吗？

Answer 1

我过去曾使用跨数据库服务器(开发/生产设置)的 ghetto/duct-tape PHP shell 脚本来完成此操作。假设您有 2 台服务器，其中一台是“正确的”:

• 获取每个表的列列表以进行检查。 SHOW CREATE TABLE 有效，或者如果您对可能更改的数据类型/默认值不感兴趣，则只需 SELECT * FROM x LIMIT 1。
• array_sort() 按字母顺序对字段名称进行排序，并循环遍历每个字段，比较您感兴趣的任何潜在变化。

虽然不推荐这个!我使用它是因为系统管理员没有考虑向某些外包人员授予完整的数据库权限，所以它只是一个快速的安全网，没有像单元测试那样计划或永久的。

---

一个更优雅和更简洁的方法(虽然仅限于 MySQL5+？)是使用 information_schema 系统数据库，尤其是 COLUMNS 表。如果您没有 2 个服务器，一个单独的数据库来保存/复制列信息(例如，具有最新“正确”模式的 information_schema 的克隆)也可以工作。这将使您能够区分新的/删除的表、索引、触发器、过程、用户/权限等。