Why Is MySQL Ignoring Trailing Spaces in Unique Fields?

MySQL Database Ignoring Unique Field's Trailing Spaces

When creating a database with a unique field in MySQL, it's expected that values containing leading or trailing spaces should be treated as distinct. However, it's observed that trailing spaces are trimmed off during insertion, resulting in duplicate value errors when attempting to insert words with varying trailing whitespace.

The underlying issue lies in MySQL's default string comparison behavior, which ignores trailing whitespace. Consequently, the UNIQUE index uses standard string comparison, leading it to ignore trailing whitespace and rejecting insertions based on character equality.

To resolve this issue, one approach is to store values as VARBINARY, which preserves spaces and allows for the creation of unique indexes that respect trailing whitespace. Although it allows for storing values with varying spaces, it's important to note that sorting by these values may not yield the expected results.

An alternative solution is to utilize MySQL's NO PAD collations, which have been introduced in MySQL 8.0. These collations, such as utf8mb4_0900_ai_ci, handle string comparisons without ignoring trailing spaces. By creating the unique field with a NO PAD collation, you can ensure that values with varying trailing whitespace are treated as distinct.

The above is the detailed content of Why Is MySQL Ignoring Trailing Spaces in Unique Fields?. For more information, please follow other related articles on the PHP Chinese website!