Why two strings in Mysql are the same?

Question

I have a problem in MySQL: Are 'amelie' and 'amélie' the same in mysql?

When I use distinct for the table, they are the same.

I am using MySQL 8.0.15，and find the two strings are the same. I know it's about problem of language , but I don't know how to solve it? Please give me some advice? Below, is the data:

CREATE TABLE test1 (aa varchar(255) DEFAULT NULL)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

SET NAMES 'utf8';
-----------------------------------------------
INSERT INTO test1(aa) VALUES
('amelie'),
('amélie');

------------------------------------------------

SELECT DISTINCT aa FROM test1;

score 1 · Accepted Answer · answered Mar 07 '19 at 05:28

This is most likely the behavior of the COLLATION you choose. You can workaround this default behavior by using COLLATE utf8mb4_bin:

SELECT DISTINCT aa COLLATE utf8mb4_bin FROM test1;

or by using BINARY:

SELECT DISTINCT(BINARY aa) as aa FROM test1;

You may also create the table with the binary COLLATE:

CREATE TABLE test1 (aa varchar(255) DEFAULT NULL) ENGINE = INNODB, CHARACTER SET utf8mb4, COLLATE utf8mb4_bin;

Why two strings in Mysql are the same?

1 Answers1