CODEWITHSUNDEEP
mysql
dshin
dshin

Reputation: 2398

case-sensitive INSERT IGNORE on a latin1 character set MySQL table

$ CREATE TABLE `test` ( `data` char(64), UNIQUE KEY `key` (`data`));
$ INSERT IGNORE INTO test (data) VALUES ('a');
$ INSERT IGNORE INTO test (data) VALUES ('A');
$ SELECT * FROM test;
+------+
| data |
+------+
| a    |
+------+

How can I get the second INSERT IGNORE to add a new entry into the table? Is it possible without redefining the table?

Upvotes: 1

Views: 322

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521209

MySQL is refusing to insert the duplicate row as it should. If you want the data column to be case insensitive you will have to alter the table:

ALTER TABLE `test` CHANGE `data` `data` CHAR( 64 )
CHARACTER SET latin1 COLLATE latin1_bin NULL DEFAULT NULL

Upvotes: 1

steffen
steffen

Reputation: 16948

No, it's not possible without DDL. You'd need to change the column collation:

alter table test modify data char(64) collate utf8_bin;

Upvotes: 0

Related Questions