Why MySQL Ignores Trailing Spaces and How to Enforce Exact Matching

MySQL is a widely used relational database, but it has a surprising quirk: trailing spaces in string values are ignored during comparisons.

Reproducing the Issue

First, a simple table is created and two rows are inserted. The second row’s last_name contains a trailing space.

CREATE TABLE `t_test_space` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `first_name` varchar(30) DEFAULT NULL COMMENT '名',
  `last_name` varchar(30) DEFAULT NULL COMMENT '姓',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_test_space` VALUES (1, 'Mic', 'John');
INSERT INTO `t_test_space` VALUES (2, 'Hy', 'Jack ');

Running a query that looks for last_name = 'John ' (note the trailing space) should return no rows, yet MySQL returns the matching record:

SELECT * FROM t_test_space WHERE last_name='John ';

Even if many spaces are added to the literal, the query still matches:

SELECT * FROM t_test_space WHERE last_name='John            ';

The same behavior occurs when the stored data itself has a trailing space. Querying for last_name='Jack' (without the space) still returns the row that actually stores Jack:

SELECT * FROM t_test_space WHERE last_name='Jack';

These results demonstrate that MySQL automatically trims trailing spaces on both sides of the comparison.

Forcing Exact Matching

To make MySQL treat the strings as binary data and respect trailing spaces, the BINARY keyword can be used:

SELECT * FROM t_test_space WHERE BINARY last_name='Jack ';

This query returns a row only when the literal exactly matches the stored value, including the trailing space. Removing the space from the literal yields no result.

While BINARY solves the problem for MySQL, it is a MySQL‑specific feature and may require special handling in ORMs or when writing portable SQL.