The BINARY function in MySQL is used to convert a value into a binary string. This function ensures that comparisons or operations on the value are done in a binary (case-sensitive) manner.
Syntax
BINARY value
Parameters
| Parameter | Description |
|---|---|
value |
Required. The value to convert to a binary string. |
Definition and Usage
- The
BINARYfunction converts a given value to its binary representation. - It is equivalent to using the
CAST(value AS BINARY)function in MySQL.
This function is particularly useful when you want to enforce case-sensitive comparisons. Without using BINARY, MySQL may perform case-insensitive comparisons (depending on the collation of the data).
Return Values
- The
BINARYfunction returns the value in binary string format.
Usage Examples
Example 1: Convert a string to binary
SELECT BINARY "W3Schools.com";
Output:
The function will convert the string "W3Schools.com" to its binary representation.
Example 2: Case-insensitive comparison of strings
SELECT "HELLO" = "hello";
Output:
| Result |
|---|
| 1 |
Explanation: This compares "HELLO" and "hello" in a case-insensitive manner, returning 1 because they are considered equal in a default, non-binary comparison.
Example 3: Case-sensitive (binary) comparison of strings
SELECT BINARY "HELLO" = "hello";
Output:
| Result |
|---|
| 0 |
Explanation: When using BINARY, MySQL performs a byte-by-byte comparison, and since the characters are not the same (case-sensitive), the result is 0 (not equal).
Technical Details
- Works in: From MySQL 4.0.
- Return Type: Binary string format, which is often used for byte-by-byte comparisons and ensuring case-sensitivity.
- Equivalent to:
CAST(value AS BINARY).
Key Notes
- This function is useful for comparisons, ensuring that the database performs byte-by-byte comparisons, which is important for handling case-sensitive data.