1

I am looking for a charset/collation that would make it so when I do a

SELECT * FROM table_name WHERE username = "Warrior"

It only returns me the rows where username = "Warrior", "warrior" or "WARRIOR", and not "WÂRRÎOR" "Wârrîor" etc.

I found a partial solution, by changing the Charset to "utf8mb4" and the Collation to "utf8mb4_bin", now it seems accent sensitive, it differentiates "Wârrîor" from "Warrior", but it's also case sensitive, so "Warrior" is different than "WARRIOR" which is not what I want.

I tried a different collations but I couldn't get one to do what exactly what I want. Any ideas ?

Below is a screenshot of the different Collations available to me in the "utf8mb4" Charset :

enter image description here

1
  • WHAT VERSION OF WHICH PRODUCT?
    – Rick James
    Jun 22 at 2:14

2 Answers 2

1

Note that _bin means case and accent sensitive, in your case you should't use utf8mb4_bin.

You could use:

utf8mb4_0900_as_ci

as means accent sensitive, and ci means case insensitive

Demo:

CREATE TABLE t (
s1 VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci
);

insert into t values
('WÂRRÎOR'),
('Warrior'),
('warrior'),
('WARRIOR'),
('Wârrîor');

SELECT * 
FROM t 
WHERE s1 = "Warrior";

Result:

s1
Warrior
warrior
WARRIOR
5
  • I tried the following : SELECT * FROM table_name WHERE username = "Warrior" COLLATE utf8mb4_0900_as_ci But it says : Error Code: 1273. Unknown collation: 'utf8mb4_0900_as_ci'
    – lyeaf
    Jun 19 at 0:46
  • I've been smashing my head onto all the different tutorials I could find on Google, and exactly none work to add the 'utf8mb4_0900_as_ci' Collation :(
    – lyeaf
    Jun 19 at 1:31
  • I just realized I'm using MariaDB and that it's not exactly the same as MySQL and that MariaDB doesn't have those collations, fuck me.
    – lyeaf
    Jun 19 at 1:34
  • There were a report about these missing collations : jira.mariadb.org/browse/MDEV-25291 but considering it's been over a year the issue was brought in, and no one seemed to acknowledge it, it's fair to consider it could take forever to be done.. So I don't know, I'll wait a few days to see if someone has a solution, if not I'll consider switching from MariaDB.
    – lyeaf
    Jun 19 at 1:43
  • I found a solution. Simply using : f`...WHERE BINARY LOWER(`username`) = '${Warrior.toLowerCase()}\'` Not sure how I didn't see that earlier. I'll mark your answer as Solution because it would apply to MySQL and I didn't specify MariaDB at first, but for people on MariaDB, thing above should work
    – lyeaf
    Jun 19 at 2:10
0
  • _bin -- accent sensitive and case sensitive
  • _as_ci (MySQL 8.0 only) -- accent sensitive and case insensitive
  • _ci -- accent insensitive and case insensitive

This lets you see what will compare equal and what won't:

(Caveat: Those were taken from specific versions; the available collations do change, but the collations don't change.)

Most, maybe not all, _ci and _ai_ci collations will treat "Wârrîor" = "Warrior"

All _ci or _ai_ci collations will treat "WARRIOR" = "Warrior"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.