Menu
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?
827
In SQL, binary data types are used to store any kind of binary data like images, word files, text files, etc. in the table. In binary data types, we have an option like allowing users to store fixed-length or variable length of bytes based on requirements.
The MySQL BINARY function is used for converting a value to a binary string. The BINARY function can also be implemented using CAST function as CAST(value AS BINARY). The BINARY function accepts one parameter which is the value to be converted and returns a binary string.
An asterisk (" * ") can be used to specify that the query should return all columns of the queried tables. SELECT is the most complex statement in SQL, with optional keywords and clauses that include: The FROM clause, which indicates the table(s) to retrieve data from.
A binary string is a sequence of bytes. Unlike a character string which usually contains text data, a binary string is used to hold non-traditional data such as pictures. The length of a binary string is the number of bytes in the sequence. A binary string has a CCSID of 65535.
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
142
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
