MySQL 5.5在查询中丢失尾随空格 [英] MySQL 5.5 losing trailing spaces in query
问题描述
我正在建立一个数据库，其中尾随空格对结果很重要.当我查询结果时，我发现
I am building a database in which trailing space is important to the results. When I query for a result I find that
SELECT * where `field` = 'a '
当存在值为"a"的字段时返回结果.我希望尾随空格在结果集中很重要.我尝试使用Char，Varchar，Text和Blob.我将注意到该字段是我表的索引.
Returns a result when there is a field who's value is 'a'. I want the trailing space to matter in the result set. I have tried using Char, Varchar, Text, and Blob. I will note that this field is the index of my table.
有人可以告诉我如何以尾随(和/或前导)空格计数的方式进行查询吗?我是否需要以任何特殊的方式来格式化表格以使其正常工作?
Can someone show me how to query in a way that makes trailing (and/or leading) spaces count? Do I need to format my table in any special way to make this work?
推荐答案
此行为是设计使然，不仅限于MySQL.
This behaviour is by design, not only in MySQL.
您可以使用BINARY在比较中解决该问题:
You can work around it in comparisons by using BINARY:
mysql> select version(), 'a' = 'a ', BINARY 'a' = BINARY 'a ';
+-------------+------------+--------------------------+
| version() | 'a' = 'a ' | BINARY 'a' = BINARY 'a ' |
+-------------+------------+--------------------------+
| 5.5.25a-log | 1 | 0 |
+-------------+------------+--------------------------+
1 row in set (0.00 sec)
但不多.如果出现空格，这将帮助您使用SELECT.在用户输入搜索内容时；但是如果您想实际输入空格跟踪的信息，那将是一个问题(您不能同时使用'a'和'a'来创建索引).
but not much more. This will help you with SELECTs if whitespaces appear e.g. in user input to a search; but if you want to actually input whitespace-trailed information, it will be a problem (you can't have an index with both 'a' and 'a ').
另请参见
可以想象，您可以反转该列中的字符串，并在显示它们时将它们反转.当然，这将破坏基于该列的任何排序，但是，如果仅测试相等性或子字符串的存在，则可能会起作用. 领先的空间确实很重要.
You could conceivably reverse the strings in that column, and reverse them back when displaying them. Of course this will wreck any ordering based on that column, but if you only test equality or substring existence, it just might work. Leading spaces do count.
对于相等性搜索，您还可以存储字符串的base64编码，该编码应保持字典顺序(即a和b之间的顺序应保持在base64(a)和base64(b)之间).或者，您可以在字符串上附加一个终止符("\ n"可能会很好，并且不会出现在搜索中).
For equality searches you might also store the base64 encoding of the string, which ought to maintain the lexicographical order (i.e., the order between a and b ought to be maintained between base64(a) and base64(b)). Or you might append a terminator on the string ("\n" could do well and not appear in searches).
最后，但这是有风险的，因为人类无法分辨出两者之间的区别，您可以将空格替换为UTF8 char(49824):
Finally, but it's risky because humans can't tell the difference, you could replace spaces with the UTF8 char(49824):
mysql> select concat ('\'a', char(49824),'\'') AS tricked,
concat ('\'a', ' ' ,'\'') as honest,
concat ('\'a', char(49824),'\'') =
concat ('\'a', ' ' ,'\'') as equals;
+---------+--------+--------+
| tricked | honest | equals |
+---------+--------+--------+
| 'a ' | 'a ' | 0 |
+---------+--------+--------+
1 row in set (0.00 sec)
似乎行是相等的，但它们并不相等.请注意，在HTML中，空格是空格，而49824是 (不间断空格).这会影响到HTML来回转换的函数，并且nbsp实际上是UTF8代码点，这意味着 honest 字符串是两个字节，但是 triked 字符串的长度实际上是三个.
The rows seem to be equal, but they are not. Note that in HTML the space is a space, and 49824 is (nonbreaking space). This affects functions that convert to and fro HTML, and the nbsp being actually an UTF8 codepoint means that honest string is two bytes, but length of tricked string is actually three.
最后，您可以声明列VARBINARY而不是VARCHAR，从而完全隐藏正在发生的事情.看来这是最简单的解决方案，但我担心它可能会在几周或几个月后咬住您.
Finally you can declare the column VARBINARY instead of VARCHAR, thus completely hiding what's happening. It looks like the easiest solution, but I fear it might bite you some weeks or months down the line.
这篇关于MySQL 5.5在查询中丢失尾随空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
