mysql - 显示创建表，指定引擎关键字

Question

我的生产数据库是 MySql 5.1.66。我的开发版本是5.5.28。当我在生产环境中运行语句 show create table 时，表 DDL 脚本输出使用关键字 TYPE 来表示存储引擎。但是该关键字不久前已被弃用并被 ENGINE 取代，因此我无法在不修改 5.1 版的脚本输出的情况下在 5.5 版中创建表。 show create table 是否有某种参数会导致输出使用 ENGINE 关键字而不是 TYPE？

5.1 的示例输出:

CREATE TABLE `acr` (
  `IncidentNum` varchar(25)
) TYPE=InnoDB;

它需要在 5.5 中运行:

CREATE TABLE `acr` (
  `IncidentNum` varchar(25)
) ENGINE=InnoDB;

Answer 1

您可以使用 no_table_options mysqldump 中的值 --compatible 参数:

• --compatible=<strong><em>name</em></strong>

  Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5.1.7, “Server SQL Modes”.

  This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, --compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.

  This option requires a server version of 4.1.0 or higher. With older servers, it does nothing.

根据 Server SQL modes 记录:

Full List of SQL Modes

The following list describes all supported modes:

[ <strong><em>deletia</em></strong> ]

• NO_TABLE_OPTIONS

  Do not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.