MySQL case insensitive string matching using =

I'm trying to search records using an alphanumeric "short_code" column. Something like:

SELECT * FROM items WHERE short_code = "1AV9"

With no collation and with column type set to varchar(), this query is case-insensitive, so it returns records with short_codes 1av9, 1Av9, etc. I don't want this.

So I tried changing the collation of the short_code column to utf8_bin, but now the query isn't returning anything at all. However, if I change the query to:

SELECT * FROM items WHERE short_code LIKE "1AV9%"

Then I get the exact row I want. Is it possible that by converting my column's collation, it somehow appended invisible chars at the end of all my shortcodes? How can I verify/fix this?

EDIT: It lo开发者_如何学编程oks that by changing my column type to binary and trying a bunch of other stuff, it somehow padded all my short_codes with null bytes, which explains why the query wouldn't return any result. After starting over and setting the utf8_bin collation, everything's working as expected.

---

Here's a wild guess. I think the table had not origiannly a collation set. Then you set the collation into utf_bin and that caused a confusion in the stored length of the field.

First back up your table. Then try:

ALTER TABLE items
  CHANGE COLUMN short_code short_code VARCHAR(48) 
    CHARACTER SET 'utf8' 
    COLLATE 'utf8_unicode_ci'  ;

Adding some characters (that are not in your data):

UPDATE items 
SET short_code = CONCAT('++F++F', short_code, '++F++F') ;

Removing them:

UPDATE items 
SET short_code = REPLACE(short_code, '++F++F', '') ;

Back to length 8:

ALTER TABLE items
  CHANGE COLUMN short_code short_code VARCHAR(8) ;

And back again to binary collation:

ALTER TABLE items
  CHANGE COLUMN short_code short_code VARCHAR(8) 
    CHARACTER SET 'utf8' 
    COLLATE 'utf8_bin'  ;

Perhaps this will fix the incorrect length. (perhaps a shorter change - from varchar to char and back to varchar - will fix it).

---

Try

SELECT LENGTH(short_code) FROM items WHERE short_code LIKE "1AV9%"

and see if you get something other than 4 as the result.

---

Edit: Hmm, your values might have trailing spaces. Try

SELECT * FROM items WHERE short_code = "1AV9    "

(that's 1AV9 plus four spaces) and see if you get any results.

---

If you can change the collation then try "utf8_general_cs".

or maybe

WHERE '1AV9' COLLATE utf8_general_cs = short_code