0

I'm trying to make mysql 8.0.11 query select case sensitive.

I'm aware mysql doesn't consider the case sensitivity by default. For example, the following two statements yield the same result.

select * from user where username = 'u01';
select * from user where username = 'U01';

while, the following two do not

select * from user where username = BINARY 'u01';
select * from user where username = BINARY 'U01';

How do I have mysql select statement case sensitive by default as if I'm using BINARY without actually using BINARY?

I've tried create databases with collations that claim case sensitive

CREATE DATABASE cstest COLLATE utf8mb4_0900_as_cs;
CREATE DATABASE cstest COLLATE utf8mb4_bin;

although none of them works. I have to explicitly using BINARY to distinguish 'u01' and 'U01' in my select statements.

Here is the table structure, automatically generated by spring data.

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `last_login_datetime` datetime DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `registered_datetime` datetime DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

with which I have to use BINARY explicitly.

and then I tried this

ALTER TABLE user COLLATE utf8mb4_0900_as_cs;

and got

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `last_login_datetime` datetime DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `registered_datetime` datetime DEFAULT NULL,
  `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs

with which I have to use BINARY explicitly again.

I also tried utf8_general_cs from https://stackoverflow.com/a/4879861/ and got

Unknown collation: 'utf8_general_cs'

How do I do? Any suggestions or clues would be appreciated.

2
  • COLLATE utf8mb4_0900_as_cs in your DB should do it. Please add the SHOW CREATE TABLE of your table. Commented Nov 11, 2022 at 1:27
  • @MauricioCacho thank you. updated
    – JJJohn
    Commented Nov 11, 2022 at 1:46

1 Answer 1

0

You changed the database default -- which is only used to provide the default for any newly created tables. Also it provides the collation for future stored routines that you create.

If you are just changing a few columns, do

ALTER TABLE user
    MODIFY COLUMN username` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,

If you are changing all the char/text columns in a table:

ALTER TABLE user
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

(Similarly for utf8mb4_0900_as_cs if you chose that.)

2
  • ALTER TABLE user MODIFY COLUMN username varchar(255) COLLATION utf8mb4_bin DEFAULT NULL; causes >>> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATION utf8mb4_bin DEFAULT NULL' at line 1
    – JJJohn
    Commented Nov 11, 2022 at 2:23
  • Not COLLATION but COLLATE.
    – Akina
    Commented Nov 11, 2022 at 4:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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