How to make MySql table data Case Sensitive?

Question

I wanted to enter data in MySql table's primary key field with respect to case sensitivity. But default it is not considering case sensitivity for table data.

Here is my query.

mysql> select id from product where id = 'a1';
+----+
| id |
+----+
| A1 |
+----+
1 row in set (0.00 sec)

mysql> insert into product values('a1', 'SomeName', 'SomeName', 200, 10, 10);
ERROR 1062 (23000): Duplicate entry 'a1' for key 'product.PRIMARY'

Also i have tried Collation while creating table but not getting result as required. can any one suggest which collation has to use or any other technique to make table's column domain case sensitive.

Having case-sensitive primary keys is just an awful idea. Don't do it. — Gordon Linoff, Apr 19 '20 at 18:53
Does this answer your question? [Changing SQL Server collation to case insensitive from case sensitive?](https://stackoverflow.com/questions/3296428/changing-sql-server-collation-to-case-insensitive-from-case-sensitive) — user123456789, Apr 19 '20 at 18:53
@GordonLinoff can you please elaborate on the awful idea part? I'm also facing this issue, and I can't find a proper explanation anywhere. — scientific_explorer, Mar 30 '21 at 16:00

score 0 · Answer 1 · answered Apr 20 '20 at 18:45

ALTER TABLE product
    MODIFY COLUMN id VARCHAR(...) COLLATION ..._bin NOT NOT NULL;

Where the ... are the current column size and character set.

The only case-sensitive things I can think of are

base-64
Unix file names

But those do not seem likely as PKs. What is your use case? Most things are better off being case-insensitive.

(A Comment links to a SQL Server suggestion using ALTER DATABASE; that will not work for MySQL since that only changes the default for subsequently created tables.)

How to make MySql table data Case Sensitive?

1 Answers1