|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a CHAR);
|
ALTER TABLE t1
|
MODIFY a CHAR COLLATE DEFAULT,
|
CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;
|
SHOW CREATE TABLE t1;
|
+-------+--------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`a` char(1) COLLATE utf8mb3_bin DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------+
|
utf8mb3_bin for the column 'a' looks wrong. The ALTER statement asked to use COLLATE DEFAULT for the column "a", which is utf8mb3_general_ci. But COLLATE DEFAULT was erroneously ignored.
Note, if I change the script slightly to ask for:
- binary collation on the column level
- default collation on the table level
it returns the expected result:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a CHAR);
|
ALTER TABLE t1
|
MODIFY a CHAR BINARY,
|
CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;
|
SHOW CREATE TABLE t1;
|
+-------+----------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`a` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------+
|
Notice, the BINARY on the column level was not ignored, which is correct.
The first script should be fixed not to ingore COLLATE DEFAULT.
|