MySQL BINARY Function

 The BINARY function in MySQL is used to perform a case-sensitive comparison in a query. Essentially, it treats the string as a binary string, meaning the comparison will be case-sensitive based on the binary value of the characters.

Syntax:

BINARY string

How It Works:

When you use the BINARY function, MySQL treats the string as a binary string. This is particularly useful when you need to differentiate between uppercase and lowercase characters in comparisons.

Example 1: Case-Sensitive Comparison

SELECT 'abc' = 'ABC';  -- Output: 1 (TRUE)
SELECT BINARY 'abc' = 'ABC';  -- Output: 0 (FALSE)

• Without BINARY, the comparison is case-insensitive ('abc' equals 'ABC').
• With BINARY, the comparison is case-sensitive.

Example 2: Ordering with BINARY

SELECT 'A' > 'a';         -- Output: 0 (FALSE, case-insensitive comparison)
SELECT BINARY 'A' > 'a';  -- Output: 1 (TRUE, case-sensitive comparison)

Example 3: Using BINARY with a Table Column

If you have a table with data:

CREATE TABLE users (
    username VARCHAR(50)
);

INSERT INTO users (username) VALUES ('John'), ('john'), ('JOHN');

-- Case-insensitive search
SELECT * FROM users WHERE username = 'john';  
-- Returns all rows: 'John', 'john', 'JOHN'

-- Case-sensitive search
SELECT * FROM users WHERE BINARY username = 'john';
-- Returns only the row: 'john'

Key Points:

1. Binary String: The BINARY function forces MySQL to treat the string as a binary string.
2. Case-Sensitive Comparisons: It is commonly used when you need case-sensitive matching.
3. Data Type: It does not change the data type of the column but affects how MySQL processes the comparison or operation.

Let me know if you'd like further examples or clarifications!