Description:
The output of SHOW CREATE TABLE explicitly specifies options that were not explicit in the original CREATE TABLE, making reuse of its output unstable by causing options to be explicit when they should not be.
How to repeat:
mysql> DROP TABLE IF EXISTS `t`;
Query OK, 0 rows affected (0.03 sec)
# Create a table; do not specify CHARACTER SET or COLLATE for the column:
mysql> CREATE TABLE `t` (`v` varchar(100) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.01 sec)
# The SHOW CREATE TABLE output specifies the COLLATE option explicitly:
mysql> SHOW CREATE TABLE `t` \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`v` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
mysql> DROP TABLE IF EXISTS `t`;
Query OK, 0 rows affected (0.03 sec)
# Create the same table using the SHOW CREATE TABLE output above:
mysql> CREATE TABLE `t` (`v` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW CREATE TABLE `t` \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`v` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.01 sec)
# The CHARACTER SET option is now also explicitly specified.
Suggested fix:
The COLLATE option should not be output unless it was specified originally, as specifying it makes it an explicitly specified collation which also causes the character set to become explicit.
Alternatively, both CHARACTER SET and COLLATE options should always be output for every column, consistently, and it should be documented as such.
Description: The output of SHOW CREATE TABLE explicitly specifies options that were not explicit in the original CREATE TABLE, making reuse of its output unstable by causing options to be explicit when they should not be. How to repeat: mysql> DROP TABLE IF EXISTS `t`; Query OK, 0 rows affected (0.03 sec) # Create a table; do not specify CHARACTER SET or COLLATE for the column: mysql> CREATE TABLE `t` (`v` varchar(100) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Query OK, 0 rows affected (0.01 sec) # The SHOW CREATE TABLE output specifies the COLLATE option explicitly: mysql> SHOW CREATE TABLE `t` \G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `v` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) mysql> DROP TABLE IF EXISTS `t`; Query OK, 0 rows affected (0.03 sec) # Create the same table using the SHOW CREATE TABLE output above: mysql> CREATE TABLE `t` (`v` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE `t` \G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `v` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.01 sec) # The CHARACTER SET option is now also explicitly specified. Suggested fix: The COLLATE option should not be output unless it was specified originally, as specifying it makes it an explicitly specified collation which also causes the character set to become explicit. Alternatively, both CHARACTER SET and COLLATE options should always be output for every column, consistently, and it should be documented as such.