Question 246455 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – problem retriving row comparing string – collate

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    Declare the collation for the column to be utf8_bin or some other _bin. Else, you get "accent insensitive" comparison. MySQL 8 has _as_ collations that also provide accent insensitivity.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search