Agile FAQs
  About   Slides   Home  

 
Managed Chaos
Naresh Jain's Random Thoughts on Software Development and Adventure Sports
     
`
 
RSS Feed
Recent Thoughts
Tags
Recent Comments

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 ‘naresh@industriallogic.com’ is different from ‘naresh@industriallogic.com ‘.

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', 'naresh@industriallogic.com');
mysql> SELECT * FROM `mysql_trailing_test` WHERE name='Naresh';
id name email
1 Naresh naresh@industriallogic.com

1 row in set (0.01 sec)

Perfect, as expected. But watch now:

mysql> SELECT * FROM `mysql_trailing_test` WHERE name='Naresh ';
id name email
1 Naresh naresh@industriallogic.com

1 row in set (0.01 sec)

AND

mysql> INSERT INTO `mysql_trailing_test` VALUES (2, 'Naresh ', 'naresh@agilefaqs.com');
mysql> SELECT * FROM `mysql_trailing_test` WHERE name='Naresh';
id name email
1 Naresh naresh@industriallogic.com
2 Naresh naresh@agilefaqs.com

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  ', 'naresh@industriallogic.com ');

ERROR 1062 (23000): Duplicate entry ‘naresh@industriallogic.com ‘ for key ‘email’

More details on MySQL Documentation: The CHAR and VARCHAR Types

  • Gregj Jorgensen

    It’s not just MySQL… this is standard SQL behavior and has been a gotcha with RDBMSs since the beginning.


    Licensed under
Creative Commons License