How Does MySQL Handle Trailing Whitespace in Unique Fields and What Are the Workarounds?

MySQL database stores unique fields without trailing whitespace

Issue:

In a MySQL database, unique fields should ensure that no two rows have the same value. However, when inserting data with trailing whitespace into a unique field, the whitespace is ignored, allowing duplicate values to be stored. This poses a problem when requiring the preservation of leading and trailing whitespace in user inputs.

Solution:

The underlying issue stems from MySQL's handling of trailing whitespace in string comparisons. All MySQL collations are of type PADSPACE, which means that trailing spaces are ignored in both CHAR and VARCHAR field types. This behavior is intended to ensure consistent string comparisons, but it conflicts with the need to preserve trailing whitespace.

To resolve this issue, there are two main solutions:

1. Store data as VARBINARY:

Instead of using text-based data types like CHAR or VARCHAR, consider storing data as VARBINARY. VARBINARY stores data as a sequence of bytes, preserving all characters, including trailing whitespace. This ensures that unique fields truly enforce uniqueness based on the exact value, including whitespace.

Example:

<code class="sql">CREATE TABLE test_ws ( `value` VARBINARY(255) UNIQUE );</code>

2. Use NO PAD collations (MySQL 8.0 and above):

MySQL versions 8.0 and later introduced NO PAD collations. These collations ignore padding characters during string comparisons, including trailing whitespace. By using a NO PAD collation, you can enforce uniqueness based on the exact value, including trailing whitespace.

Example:

<code class="sql">CREATE TABLE test_ws ( `value` VARCHAR(255) UNIQUE COLLATE utf8mb4_0900_ai_ci );</code>

Note: Sorting data based on NO PAD collated fields may yield unexpected results as sorting occurs on byte values rather than the perceived character sequence.

The above is the detailed content of How Does MySQL Handle Trailing Whitespace in Unique Fields and What Are the Workarounds?. For more information, please follow other related articles on the PHP Chinese website!