mysql - MySQL utf-8无法识别特殊字符

This question already has answers here:

MySQL distinction between e and é (e acute) - UNIQUE index
                                
                                    （4个答案）
                                
                        
                                2个月前关闭。
            
                    
有谁知道为什么会失败？

我已经创建了一个带有char3主键的utf-8表，并且在使用MySQL Workbench向其中插入记录时，它无法区分A和Ä

我们正在使用MySQL 5.1.73和Workbench 6.3.10

CREATE TABLE `test` (
  `citycode` char(3) NOT NULL,
    PRIMARY KEY (`citycode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `test` (`citycode`) VALUES ('JLA');
INSERT INTO `test` (`citycode`) VALUES ('JLÄ');

Operation failed: There was an error while applying the SQL script to the database.
Executing:
INSERT INTO `trains4_copy`.`test` (`citycode`) VALUES ('JLA');
INSERT INTO `trains4_copy`.`test` (`citycode`) VALUES ('JLÄ');

ERROR 1062: 1062: Duplicate entry 'JLÄ' for key 'PRIMARY'
SQL Statement:
INSERT INTO `trains4_copy`.`test` (`citycode`) VALUES ('JLÄ')

最佳答案

引用此link，将这些类型的字符集存储为二进制。

CREATE TABLE `test` (
  `citycode` char(3) NOT NULL,
    PRIMARY KEY (`citycode`)
) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO `test` (`citycode`) VALUES ('JLA');
INSERT INTO `test` (`citycode`) VALUES ('JLÄ');

试试这个dbfiddle。