Geek Answers Handbook

Mysql enum is not case sensitive, it matches the first, case insensitive

I have an enum field containing lowercase and uppercase the same letter when I try to update a string and change the value that it doesn't work.

This is how the problem reproduces:

 CREATE TABLE `mytable` ( `id` bigint(20) NOT NULL, `name` varchar(100) NOT NULL, `strategy` enum('g','G','r','R') NOT NULL DEFAULT 'g' ) ENGINE=InnoDB; INSERT INTO `mytable` VALUES(1,'test','g');

now when i try to change strategy from g to g , it doesn't work:

 UPDATE `mytable` SET `strategy`='G' WHERE id=1;

it returns:

 Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0

I am using MySQL 5.5 , help me

EDIT:

as the mention of @farshad in his comment, He uses the first match, if I change the order of enum and use 'G','g',... , he will always use g , and you cannot change it to g

+7
source share
2 answers

My solution is to change the mapping to ASCII:

 ALTER TABLE 'your_table' CHANGE 'strategy' ENUM('g', 'G', 'r', 'R') CHARACTER SET ASCII COLLATE ascii_bin NOT NULL DEFAULT 'g';
+3
source

From the doc :

Upon receipt, the values ​​stored in the ENUM column are displayed using the letter case used in the column definition. Note that character sets and collation can be assigned to ENUM columns. For binary or case-sensitive collation, is considered when assigning values ​​to a column.

Therefore, you must change the collation of the columns .

+5
source

More articles:


All Articles