BINARY

Description

The BINARY() function is a function used to convert a value into a binary string. It is typically used when comparing text or character data, treating strings as binary rather than ordinary character data. This enables binary comparisons of character data regardless of character set or encoding.

The BINARY() function implements binary comparison of character data, which handles cases such as case-sensitive string comparison.

Syntax

  1. > BINARY value

Parameter Values

ParameterDescription
valueRequired. The value to convert

Examples

  1. CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, password VARCHAR(100) NOT NULL);
  3. INSERT INTO users (username, password) VALUES ('JohnDoe', 'Abcd123'), ('AliceSmith', 'Efgh456'), ('BobJohnson', 'ijkl789');
  5. -- Use the BINARY() operator for password verification, and the BINARY password = 'Abcd123' part treats the password value as a binary string, so the comparison is case-sensitive. If the entered password matches a record in the database, the query will return the corresponding user id and username. Otherwise, an empty result will be returned.
  6. mysql> SELECT id, username FROM users WHERE username = 'JohnDoe' AND BINARY password = 'Abcd123';
  7. +------+----------+
  8. | id   | username |
  9. +------+----------+
  10. |    1 | JohnDoe  |
  11. +------+----------+
  12. 1 row in set (0.00 sec)
  14. mysql> SELECT id, username FROM users WHERE username = 'JohnDoe' AND BINARY password = 'abcd123';
  15. Empty set (0.00 sec)