查询 MySQL 中的字符串比较精确
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10346728/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
String compare exact in query MySQL
提问by Tang Khai Phuong
I created table like that in MySQL:
我在 MySQL 中创建了这样的表：
DROP TABLE IF EXISTS `barcode`;
CREATE TABLE `barcode` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(40) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `barcode` VALUES ('1', 'abc');
INSERT INTO `barcode` VALUES ('2', 'abc ');
Then I query data from table barcode:
然后我从表格条形码中查询数据：
SELECT * FROM barcode WHERE `code` = 'abc ';
The result is:
结果是：
+-----+-------+
| id | code |
+-----+-------+
| 1 | abc |
+-----+-------+
| 2 | abc |
+-----+-------+
But I want the result set is only 1 record. I workaround with:
但我希望结果集只有 1 条记录。我的解决方法是：
SELECT * FROM barcode WHERE `code` = binary 'abc ';
The result is 1 record. But I'm using NHibernate with MySQL for generating query from mapping table. So that how to resolve this case?
结果是1条记录。但我使用 NHibernate 和 MySQL 从映射表生成查询。那么如何解决这个案子呢？
回答by Kaii
There is no other fix for it. Either you specify a single comparison as being binaryor you set the whole database connection to binary. (doing SET NAMES binary, which may have other side effects!)
没有其他修复方法。要么将单个比较指定为 is，要么binary将整个数据库连接设置为binary. （这样做SET NAMES binary，可能有其他副作用！）
Basically, that 'lazy' comparison is a featureof MySQL which is hard coded. To disable it (on demand!), you can use a binarycompare, what you apparently already do. This is not a 'workaround' but the real fix.
基本上，“懒惰”比较是一个特征，其是硬编码的MySQL。要禁用它（按需！），您可以使用binary比较，您显然已经这样做了。这不是“解决方法”，而是真正的解决方法。
from the MySQL Manual:
来自MySQL 手册：
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 归类都是 PADSPACE 类型。这意味着在不考虑任何尾随空格的情况下比较 MySQL 中的所有 CHAR 和 VARCHAR 值
Of course there are plenty of other possiblities to achieve the same result from a user's perspective, i.e.:
当然，从用户的角度来看，还有很多其他可能性可以实现相同的结果，即：
WHERE field = 'abc ' AND CHAR_LENGTH(field) = CHAR_LENGTH('abc ')WHERE field REGEXP 'abc[[:space:]]'
WHERE field = 'abc ' AND CHAR_LENGTH(field) = CHAR_LENGTH('abc ')WHERE field REGEXP 'abc[[:space:]]'
The problemwith these is that they effectively disable fast index lookups, so your query always results in a full table scan. With huge datasets that makes a big difference.
这些的问题在于它们有效地禁用了快速索引查找，因此您的查询始终会导致全表扫描。拥有巨大的数据集，这会产生很大的不同。
Again:PADSPACEis default for MySQLs [VAR]CHAR comparison. You can (and should) disable it by using BINARY. This is the indended way of doing this.
再次：PADSPACE是 MySQLs [VAR]CHAR 比较的默认值。您可以（并且应该）使用BINARY. 这是这样做的最佳方式。
回答by aleroot
You can try with a regular expression matching:
您可以尝试使用正则表达式匹配：
SELECT * FROM barcode WHERE `code` REGEXP 'abc[[:space:]]'
回答by Arion
You could do this:
你可以这样做：
SELECT * FROM barcode WHERE `code` = 'abc '
AND CHAR_LENGTH(`code`)=CHAR_LENGTH('abc ');
回答by Darren
I am assuming you only want one result, you could use LIMIT
我假设你只想要一个结果，你可以使用 LIMIT
SELECT * FROM barcode WHERE `code` = 'abc ' LIMIT 1;
To do exact string matching you could use Collation
要进行精确的字符串匹配，您可以使用 Collation
SELECT *
FROM barcode
WHERE code COLLATE utf8_bin = 'abc';
回答by R T
i was just working on case just like that when using LIKE with wildcard (%) resulting in an unexpected result. While searching i also found STRCMP(text1, text2)under string comparison feature of mysql which compares two string. however using BINARY with LIKE solved the problem for me.
当使用带有通配符 (%) 的 LIKE 导致意外结果时，我只是在处理这种情况。在搜索时，我还在STRCMP(text1, text2)mysql 的字符串比较功能下找到了比较两个字符串的功能。但是使用 BINARY 和 LIKE 为我解决了这个问题。
SELECT * FROM barcode WHERE `code` LIKE BINARY 'abc ';
回答by LeGEC
The sentence right after the one quoted by Kaiibasically says "use LIKE" :
Kaii引用的那句话后面的句子基本上是“使用LIKE”：
and the example below shows that 'Monty' = 'Monty 'is true, but not 'Monty' LIKE 'Monty '.
下面的例子表明这'Monty' = 'Monty '是真的，但不是'Monty' LIKE 'Monty '。
However, if you use LIKE, beware of literal strings containing the '%', '_'or '\'characters : '%'and '_'are wildcard characters, '\'is used to escape sequences.
但是，如果您使用LIKE，请注意包含'%'、'_'或'\'字符的文字字符串：'%'和'_'是通配符，'\'用于转义序列。
