MySQL - problem retriving row comparing string - collate

Question

I've a table in MySQL 8 "test" with utf8 encoding

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "test"
  AND T.table_name = "user_login";

with two rows than contain values in column "login" the values

id login
-- ---
1  uu
2  ùù

The select

select *, 'explicit utf8_unicode_ci' used_collation from test where login=_utf8'uu' COLLATE utf8_unicode_ci;

return all the two rows. Why? How can i return only the exact result of the where clause?

Related: [UTF-8 all the way through](https://stackoverflow.com/questions/279170/) — JosefZ, Sep 01 '23 at 16:54

score 0 · Answer 1 · answered Aug 31 '23 at 15:58

Why not use utf8mb4

CREATE TABLE test
    (`id` varchar(2), `login` varchar(3))
;
    
INSERT INTO test
    (`id`, `login`)
VALUES
    ('1', 'uu'),
    ('2', 'ùù')
;

select *, 'explicit utf8_unicode_ci' used_collation 
  from test where login=_utf8mb4'uu' COLLATE  utf8mb4_0900_as_cs              ;

id	login	used_collation
1	uu	explicit utf8_unicode_ci

fiddle

MySQL - problem retriving row comparing string - collate

1 Answers1