SQL: How to create table with case sensitive for unique key for utf8mb4_general_ci?

Question

I am using phpMyAdmin and PDO. My database using utf8mb4_general_ci. And look like this.

CREATE TABLE `my_database`.`example` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE (`code`)
) ENGINE = InnoDB;

I got an error when I inserted 'A' and 'a'. I fix it by changing the Collation of the code column to utf8mb4_bin.

Have any way to use utf8mb4_general_ci with sensitive cases? Can I set it in phpMyAdmin, PDO, or SQL?

`Have any way to use utf8mb4_general_ci with sensitive cases?` The `_ci` stands for case insensitive, so no. — tkausl, Jul 02 '22 at 16:12
Aww, thank you for the new thing. Do you have any collate recommendations to use instead utf8_general_ci? I don't see menu utf8mb4_general_cs in phpMyAdmin. — Sittiphan Sittisak, Jul 02 '22 at 16:20
https://stackoverflow.com/questions/4558707/case-sensitive-collation-in-mysql See the (currently) last post. — topsail, Jul 02 '22 at 16:26

score 0 · Accepted Answer · answered Jul 02 '22 at 16:43

Thank @tkausl and @topsail.

The utf8mb4_general_ci contain _ci that for case insensitive.

From https://stackoverflow.com/questions/4558707/case-sensitive-collation-in-mysql, I should use utf8mb4_bin for the unique column that I want case sensitive and I can use ORDER BY column COLLATE utf8_general_ci.

SQL: How to create table with case sensitive for unique key for utf8mb4_general_ci?

1 Answers1