SHOW CREATE TABLE tbl_name
Shows the CREATE TABLE
statement
that creates the named table. To use this statement, you must
have some privilege for the table. This statement also works
with views.
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s` char(60) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
As of MySQL 8.0.16, MySQL implements CHECK
constraints and SHOW CREATE TABLE
displays them. All CHECK
constraints are
displayed as table constraints. That is, a
CHECK
constraint originally specified as part
of a column definition displays as a separate clause not part of
the column definition. Example:
mysql> CREATE TABLE t1 (
i1 INT CHECK (i1 <> 0), -- column constraint
i2 INT,
CHECK (i2 > i1), -- table constraint
CHECK (i2 <> 0) NOT ENFORCED -- table constraint, not enforced
);
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`i1` int(11) DEFAULT NULL,
`i2` int(11) DEFAULT NULL,
CONSTRAINT `t1_chk_1` CHECK ((`i1` <> 0)),
CONSTRAINT `t1_chk_2` CHECK ((`i2` > `i1`)),
CONSTRAINT `t1_chk_3` CHECK ((`i2` <> 0)) /*!80016 NOT ENFORCED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE
quotes table
and column names according to the value of the
sql_quote_show_create
option.
See Section 5.1.8, “Server System Variables”.
When altering the storage engine of a table, table options that
are not applicable to the new storage engine are retained in the
table definition to enable reverting the table with its
previously defined options to the original storage engine, if
necessary. For example, when changing the storage engine from
InnoDB to MyISAM, InnoDB-specific options such as
ROW_FORMAT=COMPACT
are retained.
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPACT ENGINE=InnoDB;
mysql> ALTER TABLE t1 ENGINE=MyISAM;
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
When creating a table with
strict mode disabled,
the storage engine's default row format is used if the
specified row format is not supported. The actual row format of
the table is reported in the Row_format
column in response to SHOW TABLE
STATUS
. SHOW CREATE
TABLE
shows the row format that was specified in the
CREATE TABLE
statement.