Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql enum is not case sensitive it match the first one without case-sensivity

Tags:

I have enum field that contains lowercase and uppercase of a same letter, when I try to update a row and change the value it doesn’t work.

this is the way how reproduce the problem:

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 returnes:

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

I use MySQL 5.5, please help me

EDIT:

as mentiond @farshad in his comment, It use the first match, if I change the order of enum and use 'G','g',... it will always use G and you can not change it back to g