MySQL Equality Comparison Ignores Trailing Spaces – Causes and Precise Query Techniques

In a recent discussion, a colleague discovered that a MySQL query using an equality condition returned rows whose user_name values had different numbers of trailing spaces, which seemed contradictory.

Verification

Data preparation : A user_info table was created and three rows were inserted with user_name values 'adu' (no space), 'adu ' (one space), and 'adu    ' (four spaces).

CREATE TABLE `user_info` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增ID',
  `user_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '名字',
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

INSERT INTO user_info(user_name) values('adu');      # no space
INSERT INTO user_info(user_name) values('adu ');     # one space
INSERT INTO user_info(user_name) values('adu    '); # four spaces

Problem verification

Running SELECT * FROM user_info WHERE user_name = 'adu' returned all three rows, showing that MySQL ignored trailing spaces in equality comparisons. The same behavior persisted for one and two trailing spaces, while leading spaces caused mismatches.

SELECT * FROM user_info WHERE user_name = 'adu';   # matches all three
SELECT * FROM user_info WHERE user_name = 'adu ';  # matches all three
SELECT * FROM user_info WHERE user_name = 'adu  '; # matches all three

Adding a unique index on user_name prevented insertion of the three rows, confirming that MySQL considered them identical for uniqueness checks.

Length checks showed the stored strings have different lengths, proving that the data is stored unchanged but compared differently.

Analysis of the cause

The MySQL documentation states that the default collation uses the PAD SPACE rule, which causes equality comparisons for CHAR, VARCHAR, and TEXT types to ignore trailing spaces. This behavior is mandated by the SQL standard.

Therefore, the "=" operator cannot be used for precise string matching when trailing spaces matter.

Precise query methods

Two approaches can achieve exact matches:

LIKE : Performs character‑by‑character comparison, preserving trailing spaces.

BINARY : Casts the string to a binary byte sequence, forcing byte‑by‑byte comparison.

Using LIKE :

SELECT * FROM user_info WHERE user_name LIKE 'adu    ';

Using BINARY :

SELECT * FROM user_info WHERE BINARY user_name = 'adu    ';

Both queries return only the rows that exactly match the specified trailing spaces.

Summary

MySQL’s PAD SPACE collation causes equality comparisons to ignore trailing spaces for CHAR, VARCHAR, and TEXT columns.

The original spaces are stored unchanged.

To perform exact matches, avoid the "=" operator and use LIKE or BINARY instead.

Understanding these rules helps avoid unexpected query results.

Author: Du Yunjie, Senior Architect, responsible for service governance, MQ, cloud platform, APM, distributed tracing, monitoring, configuration, task scheduling, ID generation, and distributed locks.