How do I have mysql select statement case sensitive by default?

Question

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.

Answer 1

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.)