Case-Sensitive Comparison in MySQL: utf8mb4_bin and Changing Collation

This article is a localized edition of the Japanese original. Read the Japanese original

MySQL decides how strings are compared and sorted with a "collation". From MySQL 8.0 on, the default collation for utf8mb4 is utf8mb4_0900_ai_ci, and as the trailing ci (case-insensitive) says, it does not distinguish uppercase from lowercase. As a result, WHERE username = 'ALICE' finds the row for Alice, and you cannot store both Alice and alice in a UNIQUE column.

This article shows how to change the collation of columns that need to be case-sensitive, and how to make a single query case-sensitive. The SQL and results were verified as of October 2026 on MySQL 8.4.13.

Case-sensitive collations

The last part of a collation name tells you how it compares.

The two collations you will mainly use for case-sensitive columns are:

"Trailing spaces" means whether 'a' and 'a ' (with a space at the end) are treated as equal. Here are the results checked on MySQL 8.4:

SELECT 'Alice' = 'alice'                            AS default_cmp,  -- 1 (treated as equal)
       'Alice' = 'alice' COLLATE utf8mb4_bin        AS bin_cmp,      -- 0 (distinguished)
       'Alice' = 'alice' COLLATE utf8mb4_0900_as_cs AS as_cs_cmp,    -- 0 (distinguished)
       'a' = 'a ' COLLATE utf8mb4_bin               AS bin_pad,      -- 1 (trailing space ignored)
       'a' = 'a ' COLLATE utf8mb4_0900_as_cs        AS as_cs_pad;    -- 0 (trailing space also counts)

You can check each collation's pad attribute with this SQL:

SELECT COLLATION_NAME, PAD_ATTRIBUTE
FROM information_schema.COLLATIONS
WHERE COLLATION_NAME IN ('utf8mb4_0900_ai_ci', 'utf8mb4_0900_as_cs', 'utf8mb4_bin', 'utf8mb4_0900_bin');

MariaDB uses different collation names and defaults. As of October 2026, the default collation for utf8mb4 in MariaDB 11.8 is utf8mb4_uca1400_ai_ci. utf8mb4_bin is also available in MariaDB and is case-sensitive.

Specify it when you create a new table

Set the collation only on the columns that need to be case-sensitive.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    email VARCHAR(255) NOT NULL,
    UNIQUE KEY uk_username (username)
);

In this example only username is case-sensitive, and email keeps the table default (case-insensitive). Email addresses are generally handled case-insensitively, and if User@example.com and user@example.com could be registered as different users, you would get duplicate sign-ups. Decide per column, based on its purpose, whether case should matter.

You can also change the default for the whole database. In that case, every column in tables created later without an explicit collation becomes case-sensitive.

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

Change the collation of an existing column

To change a single column, rewrite its definition with ALTER TABLE ... MODIFY. You must restate existing attributes such as NOT NULL, so check the current definition with SHOW CREATE TABLE first.

SHOW CREATE TABLE users;

ALTER TABLE users
  MODIFY username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; changes every string column in the table, including columns that should stay case-insensitive (such as email addresses). Use MODIFY when you want to change only specific columns.

After the change, check it with either of these:

SHOW FULL COLUMNS FROM users;

SELECT COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users';

Effect on unique keys

For columns with a UNIQUE key, changing the collation also changes how duplicates are detected.

• Changing from case-insensitive (_ci) to case-sensitive (_bin) keeps the existing data as is. Afterward, you can register both Alice and alice
• Changing from case-sensitive to case-insensitive fails with ERROR 1062 (23000): Duplicate entry if both Alice and alice exist

In our test, inserting alice after Alice into a column with a case-insensitive collation produced this error:

ERROR 1062 (23000): Duplicate entry 'alice' for key 'users.uk_username'

ALTER TABLE may rebuild the table, which takes time on large tables. In production, take a backup first and run it during a quiet period.

Make only a query case-sensitive

To keep the column's collation and make just one query case-sensitive, specify the collation for the comparison with COLLATE.

SELECT * FROM users WHERE username = 'Alice' COLLATE utf8mb4_bin;

However, comparing with a collation different from the column's prevents the index from narrowing the search, so the whole index is read. Running EXPLAIN in our test showed type as index (full index scan). For tables with many rows, narrow the candidates with a normal comparison that can use the index and then apply COLLATE for the final check, as below. With this form, type became const, an index lookup.

SELECT * FROM users
WHERE username = 'Alice'
  AND username = 'Alice' COLLATE utf8mb4_bin;

WHERE BINARY username = 'Alice' also works, but it is deprecated as of MySQL 8.0.27 and running it produces the warning 'BINARY expr' is deprecated. Use COLLATE in new SQL.

What changes when you switch the collation

• Searches: not only = but also LIKE becomes case-sensitive. WHERE username LIKE 'a%' no longer finds Alice
• Sorting: with utf8mb4_bin, ORDER BY follows code point order, so Alice comes before alice and B comes before a
• Applications: if login names become case-sensitive, users who get the case wrong cannot log in. Decide on the on-screen guidance and how input is handled (whether to convert it to lowercase) at the same time

About WordPress tables

WordPress creates its tables according to DB_CHARSET and DB_COLLATE in wp-config.php and the database settings, and they normally use a case-insensitive collation (such as utf8mb4_unicode_520_ci). Changing the collation of WordPress core tables (such as wp_users) changes how usernames are looked up and checked for duplicates, so do not change them. Use the methods in this article for tables you created yourself in a plugin or your own application.

Summary

• The default collation in MySQL 8.0 and later, utf8mb4_0900_ai_ci, is case-insensitive
• Use utf8mb4_bin or utf8mb4_0900_as_cs for columns that must be case-sensitive. utf8mb4_bin ignores trailing spaces; utf8mb4_0900_as_cs does not
• Change existing columns with ALTER TABLE ... MODIFY. CONVERT TO changes every string column
• For a case-sensitive query only, use COLLATE utf8mb4_bin and combine it with a normal comparison so the index can be used
• The BINARY operator is deprecated as of MySQL 8.0.27

Related articles:

• MySQL character set and collation settings
• How to change the MySQL character set and collation
• How to set the database character set to UTF-8

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)