Convert Strings to Binary in MySQL

In MySQL, you can convert strings to binary format using the BINARY keyword, which treats the string as a binary data type.

In this tutorial, you will learn how to use the BINARY keyword in MySQL to convert strings into binary data for case-sensitive operations and binary storage.

Using BINARY to Convert Strings to Binary in MySQL

The BINARY keyword in MySQL is used to convert a string to binary format. When a string is converted to binary, it becomes case-sensitive, and comparisons are made byte-by-byte rather than character-by-character. This can be useful for case-sensitive searches, storage, and when you need to work with binary data.

Examples: Converting Strings to Binary in MySQL

Below are examples demonstrating how to use the BINARY keyword to convert strings to binary format and apply binary comparisons in MySQL.

1. Simple String to Binary Conversion

To convert the string 'hello' to binary format:

</>
Copy
SELECT BINARY 'hello' AS binary_string;

This query converts the string 'hello' to binary format. The result can be used for case-sensitive comparisons, distinguishing between 'hello' and 'Hello'.

2. Case-Sensitive Comparison Using BINARY

To perform a case-sensitive search on a string, for example, checking if 'Hello' is not the same as 'hello':

</>
Copy
SELECT 'Hello' = BINARY 'hello' AS case_sensitive_comparison;

This query returns 0 (false) because the binary format treats 'Hello' and 'hello' as distinct values due to case sensitivity.

Convert Strings to Binary in MySQL - Example: Case-Sensitive Comparison Using BINARY

3. Storing Binary Data in a Column

To insert binary data into a table for case-sensitive storage, convert a string to binary on insert:

</>
Copy
CREATE TABLE binary_data (
    id INT PRIMARY KEY,
    data BINARY(10)
);

INSERT INTO binary_data (id, data)
VALUES (1, BINARY 'TestString');

This example demonstrates how to store binary data in a column by using BINARY on a string. When retrieved, the data is case-sensitive and stored byte-by-byte.

Convert Strings to Binary in MySQL - Example: Storing Binary Data in a Column