Is there a way to force case sensitivity in MySQL / Rails for a single find?

Question

I'm doing some searching of tags, and some users like "cat" while others like "Cat" Go figure...

Anyways, is there a way to force a particular find to be case sensitive? Such as:

Tag.find(:some-special-option-here)

Any ideas?

Answer 1

In the mysql database, set your text's data type to utf_collate_bin. For example:

ALTER TABLE `sets` CHANGE `set_name` `set_name` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

Where 'sets' is the table, 'set_name' is the column of type VARCHAR(64). You can also do this in PhpMyAdmin..

Any binary collate will do the job; but utf8 is preferable.

If you were wondering what the _ci at the end of your current collate is, it means "Case Insensitive" :p

Answer 2

You can also do a case-sensitive search without changing your column properties.

SELECT * FROM mytable WHERE myfield='Value'

This query matches

• Value
• value
• VALUE
• vAlUe
• and so on

while

SELECT * FROM mytable WHERE BINARY myfield='Value'

machtes only

• Value

Answer 3

You can make all strings case sensitive when you create the table by adding "COLLATE utf8_bin" to the :options string when creating the table. For example:

create_table( "page_infos", :force => true, :options => "ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin") do |t|
      t.string   "title",       :limit => 120
end