MySQL Trailing Space Issue: Unexpected Matching of Strings with Trailing Spaces

The author discovered a surprising behavior in MySQL where trailing spaces in VARCHAR columns are ignored during string comparison, which may be considered a bug.

First, a test table is created:

CREATE TABLE `t_white_space`(
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '' ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Two rows are inserted, the second one containing a trailing space after the name:

INSERT INTO t_white_space (name) VALUES ('Tom');
INSERT INTO t_white_space (name) VALUES ('Tom ');

A simple query that should return only the exact match unexpectedly returns both rows:

SELECT * FROM t_white_space WHERE name = 'Tom';
+----+-------+
| id | name |
+----+-------+
|  1 | Tom  |
|  2 | Tom   |
+----+-------+
2 rows in set

The result shows that MySQL ignores any number of trailing spaces, which is problematic for scenarios requiring exact text matching. Inserting a third row with many trailing spaces confirms the issue:

INSERT INTO t_white_space (name) VALUES ('Tom          ');

Running the same query now returns three rows:

SELECT * FROM t_white_space WHERE name = 'Tom';
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Tom       |
|  2 | Tom        |
|  4 | Tom          |
+----+-----------+
3 rows in set

Leading or middle spaces are handled correctly; only trailing spaces are ignored. The workaround is to add the BINARY keyword to force a binary (byte‑by‑byte) comparison:

SELECT * FROM t_white_space WHERE BINARY name = 'Tom';
+----+-------+
| id | name |
+----+-------+
|  1 | Tom  |
+----+-------+
1 row in set

While this returns the expected single row, it requires modifying every query, which is cumbersome, especially when using ORMs that abstract SQL generation.

The issue also affects other statements such as GROUP BY , where trailing spaces are ignored. The behavior was observed in MySQL 5.7 and does not occur in PostgreSQL.

The author invites readers to discuss whether this constitutes a MySQL bug.