How to enforce unique fields in MySQL while preserving trailing spaces?

MySQL Database with Unique Fields Ignoring Ending Spaces

MySQL databases by default ignore trailing whitespace when comparing strings. This can be problematic when you have unique fields that need to consider spacing.

Consider the following table definition:

<code class="sql">CREATE TABLE strings
(
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  string varchar(255) COLLATE utf8_bin NOT NULL,
  created_ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY string (string)
)
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;</code>

If you attempt to insert the word 'apple' with a space on the left, it will store as expected. However, if you attempt to insert the word with a space on the right, it will trim off the trailing space before storing it.

Potential Solution

One potential solution is to add a special character to the right of the word after the spacing. However, this approach introduces additional complexity and may not always be practical.

Proposed Solution

A better solution is to take advantage of MySQL's NO PAD collations introduced in MySQL 8.0. Collations of type NO PAD do not ignore trailing whitespace when comparing strings.

To create a table with a NO PAD collation, use the following syntax:

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

With this collation, you can insert values with and without trailing whitespace:

<code class="sql">INSERT INTO test_ws (`value`) VALUES ('a'); -- Inserts "a"
INSERT INTO test_ws (`value`) VALUES ('a '); -- Inserts "a "

SELECT CONCAT('(', `value`, ')') FROM test_ws;

-- Output:
-- (a)
-- (a )</code>

While this approach addresses the issue of unique fields ignoring ending spaces, it's important to note that sorting on such a column will happen on byte values, which may not align with user expectations.

The above is the detailed content of How to enforce unique fields in MySQL while preserving trailing spaces?. For more information, please follow other related articles on the PHP Chinese website!