MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `test` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name_ci` VARCHAR(32),
  `name_cs` VARCHAR(32) COLLATE utf8mb4_0900_as_cs,
  key (name_ci),
  key (name_cs)
);

INSERT INTO `test` (`name_ci`, `name_cs`)
  VALUES ('John', 'John'), ('john', 'john'), ('JOHN', 'JOHN');

Records: 3  Duplicates: 0  Warnings: 0


SELECT `id`, `name_ci`, `name_cs`
  FROM `test`
  WHERE `name_ci` = 'john';
EXPLAIN
SELECT `id`, `name_ci`, `name_cs`
  FROM `test`
  WHERE `name_ci` = 'john';


id
name_ci
name_cs


1
John
John

2
john
john

3
JOHN
JOHN




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
test
null
ref
name_ci
name_ci
131
const
3
100.00
null


SELECT `id`, `name_ci`, `name_cs`
  FROM `test`
  WHERE `name_ci` = 'john' COLLATE utf8mb4_0900_as_cs;
EXPLAIN
SELECT `id`, `name_ci`, `name_cs`
  FROM `test`
  WHERE `name_ci` = 'john' COLLATE utf8mb4_0900_as_cs;


id
name_ci
name_cs


2
john
john




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
test
null
ALL
name_ci
null
null
null
3
33.33
Using where


SELECT `id`, `name_ci`, `name_cs`
  FROM `test`
  WHERE `name_cs` = 'john';
EXPLAIN
SELECT `id`, `name_ci`, `name_cs`
  FROM `test`
  WHERE `name_cs` = 'john';


id
name_ci
name_cs


2
john
john




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
test
null
ref
name_cs
name_cs
131
const
1
100.00
null


SELECT `id`, `name_ci`, `name_cs`
  FROM `test`
  WHERE `name_cs` = 'john' COLLATE utf8mb4_0900_ai_ci;
EXPLAIN
SELECT `id`, `name_ci`, `name_cs`
  FROM `test`
  WHERE `name_cs` = 'john' COLLATE utf8mb4_0900_ai_ci;


id
name_ci
name_cs


1
John
John

2
john
john

3
JOHN
JOHN




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
test
null
ALL
name_cs
null
null
null
3
33.33
Using where