Can someone explain why MySQL returns both values when name = 'test': "test" and "test "

Question

I have the following table and data:

CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `test` (`id`, `name`) VALUES (1, 'test');
INSERT INTO `test` (`id`, `name`) VALUES (2, 'test    ');

When I do either of the following queries, it returns 2 rows (both rows):

SELECT * FROM test WHERE name = 'test';
SELECT * FROM test WHERE name IN ('test');

Can anyone explain this to me and/or how to fix it?

I'm runing MySQL 5.0.27.

Answer 1

MySQL removes whitespace from the end of varchar columns - not exactly sure why this is implemented this way in MySQL - clearly not ANSI standard.

You options are to go with char or text fields if you want to preserver trailing whitespace.

EDIT: I believe that this was changed as of version 5.0.3

Answer 2

From the mysql manual:

Note that all MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces.

Take note that MySQL does not remove the trailing spaces in a version 5.0.3 or higher, they are stored, but not used during comparisons:

VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.

Both of these quotes come from this page of the manual: 10.4.1. The CHAR and VARCHAR Types

Answer 3

Use the BINARY keyword:

SELECT * FROM test WHERE BINARY name = 'test';