CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
ohho
ohho

Reputation: 51911

Why the 'BINARY' in a SELECT statement?

In a Vote model, there is a validation:

validates_uniqueness_of :topic_id, :scope => [:user_id]

It is translated to the following SQL in development log:

SELECT 1 AS one FROM `votes` WHERE (`votes`.`topic_id` = BINARY 2 AND `votes`.`user_id` = 1) LIMIT 1

Where there is a BINARY in front of 2 (topic_id)? And, what does it mean?

Upvotes: 5

Views: 2049

Answers (1)

Viren
Viren

Reputation: 5962

It is an efficient way of comparing byte to byte instead of character to character

example

Suppose if your have a database table called products record which has vin_number (some column name) with record with value of vin_number say 123456

Now If you ran the this 

select * from products where vin= '123456'

and

select * from products where vin = '123456 '

Both will result the same result

Notice the space in the second select

But with binary the comparison

select * from products where vin= BINARY '123456'

or 

select * from producst where vin = BINARY '123456 '

A byte by byte match is done as against character to character

so the first one would result in valid result

and

the second one would no result

Here the link that will further help you on this

Upvotes: 7

Related Questions