Description:
SHOW CREATE TABLE does not explicitly show the collation applied to a table. This can cause problems as copying the table definition to a database or server with a different default character set or collation may lead to a different table definition.
Consequently it would be good if SHOW CREATE TABLE provided a more complete output which would ensure that the create table command can be used on any other MySQL server without causing incompatible definitions.
e.g. sample SHOW CREATE TABLE output would show something like this:
CREATE TABLE `t` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`col2` char(16) NOT NULL,
`col3` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`col4` varchar(50) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col2` (`col2`),
KEY `col3` (`col3`)
) ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=latin1
It is not clear which collation is being used.
With the change in MySQL 8.0 to have a different default character set the default collation also changes so ALTER TABLE command if not very explicit may lead (at least in replication) to the table definition on master and slave being different.
This request is not about that but at least in coming up with a way to be explicit about which collation is being used.
How to repeat:
see above.
Suggested fix:
I see 2 different options here:
(a) make each column and table clause show the exact setting used
CREATE TABLE `t` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`col2` char(16) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`col3` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`col4` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col2` (`col2`),
KEY `col3` (`col3`)
) ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=latin1 DEFAULT COLLATION latin1_swedish_ci
This format may be considered rather noisy but it's completely unambiguous.
(b) make the table clause (the bit at the end) show the default collation used. This is shorter so may be preferred but has the disadvantage that just showing a column definition is not "complete". It would probably show something like:
CREATE TABLE `t` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`col2` char(16) NOT NULL,
`col3` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`col4` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col2` (`col2`),
KEY `col3` (`col3`)
) ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=latin1 DEFAULT COLLATION latin1_swedish_ci
Which is a little less verbose, but will require care when copying or comparing the col2 definition as the character set and collation used are now explicit.
I'm tempted to think that (b) would be better and for the number of use cases where you see this the explicitness avoids confusion.
Given this is a behavioural change I'd expect a global/session setting to adjust output behaviour (at least in 8.0) and I'd enable this in 8.0 by default allowing for the backwards compatible setting to be used if needed.
Other suggestions are welcome.
