Why Rails generate BINARY in SQL query when create new record

Question

I have models Item and Location.

This is my migration file of Item model:

class CreateItems < ActiveRecord::Migration
  def change
    create_table :items do |t|
      t.string :item_cd
      t.string :item_name

      t.integer :location_id
      t.timestamps
    end
  end
end

I use form_tag to create a form for create new item. But when I create new Item, Rails generate SQL like this:

Location Load (0.3ms)  SELECT `locations`.* FROM `locations` WHERE `locations`.`location_cd` = 'jp' LIMIT 1
  Item Load (0.2ms)  SELECT `items`.* FROM `items` WHERE `items`.`item_cd` = '6' LIMIT 1
   (0.1ms)  BEGIN
  Item Exists (0.2ms)  SELECT 1 FROM `items` WHERE `items`.`item_cd` = BINARY '6' LIMIT 1
  SQL (0.6ms)  INSERT INTO `items` (`created_at`, `item_cd`, `item_name`, `location_id`, `updated_at`) VALUES ('2013-04-17 03:26:42', '6', 'Glasses', 12, '2013-04-17 03:26:42')
   (27.6ms)  COMMIT

Why SQL has BINARY in Item Exists row? I insert a string to create item_cd on form. Can anyone show me what problems is?

I am using mysql database.

Answer 1

It looks like you have some validation in your model like

validates :item_cd, :uniqueness => true

Obviously, this would make the model check for existence of an equal value in the db, hence the Exist query.

The reason why the string '6' is cast to a binary before comparison is that binary comparison is faster for char and varchar values. Normally the comparison would go through the collation process so that (e.g.) '6' = '6 ' or 'a' = 'A'. As the query just needs to retrieve exact matches (and also not the values, just their existence: SELECT 1), a binary comparison is sufficient.

You can also have a look at the relevant MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html