CODEWITHSUNDEEP
mysql
user2827214
user2827214

Reputation: 1191

MySQL table with Case Insensitive Select and Unique Index

My MySQL table has 2 columns, word1 and word2 (both case insensitive and collation utf8_bin).

Word1 can be 'basketball or 'Basketball' or 'BASKETBALL'.

(basketball, game) AND (BASKETBALL, game) both exist as rows in the table.

I use a unique index on word1, word2 to prevent duplicate rows from being inserted, for example, the row (basketball, game) exists, so inserting (basketball, game) into the table is rejected.

The problem is that SELECT is case sensitive and removing 'utf8_bin' collation gives a duplicate entries error.

How do I get SELECT to match case insensitive both 'basketball' and 'BASKETBALL' rows?

Upvotes: 0

Views: 185

Answers (1)

Joni
Joni

Reputation: 111259

You can specify a collation to use with the COLLATE keyword, for example

select * from mytable where word1 = 'baseball' collate utf8_general_ci

Upvotes: 1

Related Questions