Description:
When create a table, if we declare the collate info before the charset, the collate info will lost.
How to repeat:
As below example, the collate=utf8mb4_bin is lost:
mysql> create table t10013(c1 varchar(10) )collate=utf8mb4_bin charset=utf8mb4 engine=innodb;
Query OK, 0 rows affected (0.52 sec)
mysql> show create table t10013;
+--------+-------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------+
| t10013 | CREATE TABLE `t10013` (
`c1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------+-------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> insert into t10013 values ("A"),("a");
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t10013 where c1="a";
+------+
| c1 |
+------+
| A |
| a |
+------+
2 rows in set (0.01 sec)
And if we declare the charset info before collate, it works fine:
mysql> create table t10013(c1 varchar(10) ) charset=utf8mb4 collate=utf8mb4_bin engine=innodb;
Query OK, 0 rows affected (0.38 sec)
mysql> show create table t10013;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------+
| t10013 | CREATE TABLE `t10013` (
`c1` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> insert into t10013 values ("A"),("a");
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t10013 where c1="a";
+------+
| c1 |
+------+
| a |
+------+
1 row in set (0.02 sec)
mysql> drop table t10013;
Query OK, 0 rows affected (0.15 sec)
Suggested fix:
None.
Description: When create a table, if we declare the collate info before the charset, the collate info will lost. How to repeat: As below example, the collate=utf8mb4_bin is lost: mysql> create table t10013(c1 varchar(10) )collate=utf8mb4_bin charset=utf8mb4 engine=innodb; Query OK, 0 rows affected (0.52 sec) mysql> show create table t10013; +--------+-------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------------------------------------------------+ | t10013 | CREATE TABLE `t10013` ( `c1` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +--------+-------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> insert into t10013 values ("A"),("a"); Query OK, 2 rows affected (0.07 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t10013 where c1="a"; +------+ | c1 | +------+ | A | | a | +------+ 2 rows in set (0.01 sec) And if we declare the charset info before collate, it works fine: mysql> create table t10013(c1 varchar(10) ) charset=utf8mb4 collate=utf8mb4_bin engine=innodb; Query OK, 0 rows affected (0.38 sec) mysql> show create table t10013; +--------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-----------------------------------------------------------------------------------------------------------------------------------------+ | t10013 | CREATE TABLE `t10013` ( `c1` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +--------+-----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> insert into t10013 values ("A"),("a"); Query OK, 2 rows affected (0.07 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t10013 where c1="a"; +------+ | c1 | +------+ | a | +------+ 1 row in set (0.02 sec) mysql> drop table t10013; Query OK, 0 rows affected (0.15 sec) Suggested fix: None.