Currently MariaDB reduces SHOW CREATE TABLE output: COLLATE clause is not displayed if the collation is default for its character set.
CREATE OR REPLACE TABLE t1 (
|
a VARCHAR(10),
|
b VARCHAR(10) CHARACTER SET ucs2
|
) CHARACTER SET utf8mb4;
|
SHOW CREATE TABLE t1;
|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`a` varchar(10) DEFAULT NULL,
|
`b` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice:
- the definition of the column `b` does not have a COLLATE clause, because it's collation is ucs2_general_ci, which is default for the character set ucs2.
- the table option does not have a COLLATE clause, because it's collation is utf8mb4_general_ci, which is default for the chracter set utf8mb4.
Under terms of MDEV-19123, we're going to change the default collation for Unicode charcter sets to uca1400_ai_ci.
This will break dump-restore:
e.g. utf8mb4_general_ci will be erroneously replaced to uca1400_ai_ci.
To avoid this, SHOW CREATE TABLE should always diplay collations after CHARACTER SET clause, even default ones.
MDEV-19123 Change default charset from latin1 to utf8mb4
MDEV-29561 SHOW CREATE TABLE produces syntactically incorrect structure
