mysql - 有人可以解释为什么当名称 = 'test' : "test" and "test " 时 MySQL 返回两个值

Question

我有下表和数据:

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    ');

当我执行以下任一查询时，它返回 2 行(两行):

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

任何人都可以向我解释这个问题和/或如何解决它吗？

我正在运行 MySQL 5.0.27。

Answer 1

摘自mysql手册:

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.

请注意，MySQL 在 5.0.3 或更高版本中不会删除尾随空格，它们会被存储，但不会在比较期间使用:

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.

这两个引用都来自手册的这一页:10.4.1. The CHAR and VARCHAR Types