MySQL Ignoring Trailing Spaces in VARCHAR Values and How to Enforce Exact Matching

MySQL is a widely used relational database, but it has a surprising behavior: it ignores trailing spaces in string values during comparisons. The article creates a simple table t_test_space with an id , first_name , and last_name column, inserts two rows, and highlights that the second row's last_name value ends with a space.

When querying with a literal that includes a trailing space, e.g., SELECT * FROM t_test_space WHERE last_name='John ' , MySQL returns the matching row even though the stored value has no trailing space. Adding more spaces to the literal does not affect the result; MySQL still matches the row.

The same effect occurs when the trailing space is stored in the data: querying SELECT * FROM t_test_space WHERE last_name='Jack' returns the row whose last_name actually contains a trailing space.

To enforce strict matching, the article suggests using the MySQL‑specific BINARY keyword, e.g., SELECT * FROM t_test_space WHERE BINARY last_name='Jack ' . With BINARY , the comparison becomes case‑sensitive and space‑sensitive, so only rows with an exact trailing space match are returned.

The author notes that relying on BINARY ties the solution to MySQL and may complicate ORM usage, inviting readers to discuss alternative approaches.