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