Trailing White Spaces are Ignored by MySQL for Comparison in a Select Statement
Just learned today that for char or varchar columns, MySQL ignores trailing white spaces for comparison in a select statement’s where clause.
This came as a surprise because I was thinking that the string ‘[email protected]’ is different from ‘[email protected] ‘.
mysql> CREATE TABLE `mysql_trailing_test` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) NOT NULL, `email` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
mysql> INSERT INTO `mysql_trailing_test` VALUES (1, 'Naresh', '[email protected]'); |
mysql> SELECT * FROM `mysql_trailing_test` WHERE name='Naresh'; |
| id | name | |
|---|---|---|
| 1 | Naresh | [email protected] |
1 row in set (0.01 sec)
Perfect, as expected. But watch now:
mysql> SELECT * FROM `mysql_trailing_test` WHERE name='Naresh '; |
| id | name | |
|---|---|---|
| 1 | Naresh | [email protected] |
1 row in set (0.01 sec)
AND
mysql> INSERT INTO `mysql_trailing_test` VALUES (2, 'Naresh ', '[email protected]'); |
mysql> SELECT * FROM `mysql_trailing_test` WHERE name='Naresh'; |
| id | name | |
|---|---|---|
| 1 | Naresh | [email protected] |
| 2 | Naresh | [email protected] |
2 row in set (0.01 sec)
Also because of this, if you had a unique constraint on the column and tried to insert another value with trailing space, MySQL would return ERROR 1062 (23000): Duplicate entry.
mysql> INSERT INTO `mysql_trailing_test` VALUES (3, 'Naresh ', '[email protected] '); |
ERROR 1062 (23000): Duplicate entry ‘[email protected] ‘ for key ’email’
More details on MySQL Documentation: The CHAR and VARCHAR Types
