比较字符串，其中一个字符串之前有空格，而另一个字符串之前没有空格 [英] Comparing strings with one having empty spaces before while the other does not

问题描述

如果我必须找到一个字符串名称"Akito"并且它位于表foo中，那么下面是正常过程，

If I have to find a string name "Akito" and it lies in the table foo then following is the normal procedure,

select * from foo where `name = 'Akito'`

我试图检查它的两个变体，

I tried to check two variations of it,

工作正常

select * from foo where name = 'Akito   '

没有正常工作

select * from foo where name = '    Akito'

任何人都可以解释一下第二个为什么不起作用吗?

Can anyone please explain why did the 2nd one did not work?

预先感谢

推荐答案

CHAR类型使用空字节将字符串填充到字段的长度(而VARCHAR添加定界符以指示字符串的结尾) -因此忽略了结尾处的多余数据(我是指空字节))，因此结尾处有空格的比较将忽略那些多余的数据.前导空格很重要，因为它们会改变字符串本身.参见克里斯托弗的答案.

CHAR types fill the string to the length of the field with null bytes (while VARCHAR add delimiters to indicate the end of the string - thus ignoring extra data at the end (I mean empty bytes)), and therefore comparisons that have spaces at the end will ignore those. Leading spaces are relevant as their alter the string itself. See Christopher's answer.

需要进一步详细说明

请参阅下面的一些实际测试. VARCHAR类型的确会在字符串中添加空格，而CHAR字段(即使它们使用空格填充了字符串的大小)也将在比较期间忽略它们.具体参见LENGTH函数查询的第二行:

See some practical tests below. VARCHAR types do add spaces to the string, whilst CHAR fields, even though they fill the string up to its size with spaces, ignore them during comparisons. See specifically the second line with the LENGTH function query:

mysql> create table test (a VARCHAR(10), b CHAR(10));
Query OK, 0 rows affected (0.17 sec)

mysql> insert into test values ('a', 'a'), ('a ', 'a '), (' a', ' a');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select a, LENGTH(a), b, LENGTH(b) FROM test;
+------+-----------+------+-----------+
| a    | LENGTH(a) | b    | LENGTH(b) |
+------+-----------+------+-----------+
| a    |         1 | a    |         1 | 
| a    |         2 | a    |         1 | 
|  a   |         2 |  a   |         2 | 
+------+-----------+------+-----------+
3 rows in set (0.00 sec)

其中MySQL声明了CHAR字段，该字段的长度为1个字符，插入时的值为'a'.此外，如果我们连接一些数据:

where MySQL states the CHAR field, with the value of 'a ' as it was inserted, has only 1 character in length. Furthermore, if we concatenate a little data:

mysql> select CONCAT(a, '.'), CONCAT(b, '.') FROM test;
+----------------+----------------+
| CONCAT(a, '.') | CONCAT(b, '.') |
+----------------+----------------+
| a.             | a.             | 
| a .            | a.             | 
|  a.            |  a.            | 
+----------------+----------------+
3 rows in set (0.00 sec)

mysql> select CONCAT(a, b), CONCAT(b, a) FROM test;
+--------------+--------------+
| CONCAT(a, b) | CONCAT(b, a) |
+--------------+--------------+
| aa           | aa           | 
| a a          | aa           | 
|  a a         |  a a         | 
+--------------+--------------+
3 rows in set (0.00 sec)

您会看到，由于VARCHAR确实存储了字符串的结尾位置，因此空格保留在串联上-这对于CHAR类型而言不成立.现在，请记住前面的LENGTH示例，其中第二行的字段a和b具有不同的长度，我们测试:

you can see that, since VARCHAR does store where the string ends, the space remains on concatenations - which does not hold true for CHAR types. Now, keeping in mind the previous LENGTH example, where line two has different lengths for its fields a and b, we test:

mysql> SELECT * FROM test WHERE a=b;
+------+------+
| a    | b    |
+------+------+
| a    | a    | 
| a    | a    | 
|  a   |  a   | 
+------+------+
3 rows in set (0.00 sec)

因此，我们可以总结一下，CHAR数据类型会忽略并修剪其字符串末尾的多余空间，而VARCHAR不会-在比较期间 :

Therefore, we can sum up stating that the CHAR datatype ignores and trims extra space at the end of its string, while VARCHAR does not - except during comparisons:

mysql> select a from test where a = 'a ';
+------+
| a    |
+------+
| a    | 
| a    | 
+------+
2 rows in set (0.00 sec)

mysql> select a from test where a = 'a';
+------+
| a    |
+------+
| a    | 
| a    | 
+------+
2 rows in set (0.00 sec)

mysql> select a from test where a = ' a';
+------+
| a    |
+------+
|  a   | 
+------+
1 row in set (0.00 sec)

那么，CHAR类型是否也一样?

So, is the same true for the CHAR type?

mysql> select a from test where b = 'a ';
+------+
| a    |
+------+
| a    | 
| a    | 
+------+
2 rows in set (0.00 sec)

mysql> select a from test where b = 'a';
+------+
| a    |
+------+
| a    | 
| a    | 
+------+
2 rows in set (0.00 sec)

mysql> select a from test where b = ' a';
+------+
| a    |
+------+
|  a   | 
+------+
1 row in set (0.00 sec)

其中显示 CHAR和VARCHAR类型具有不同的存储方法，但遵循相同的规则进行纯粹的字符串比较.尾随空格将被忽略；而前导空格会修改字符串本身.

Which displays that the CHAR and VARCHAR types have different storage methods, but follow the same rules for sheer string comparison. Trailing spaces are ignored; while leading spaces modify the string itself.

这篇关于比较字符串，其中一个字符串之前有空格，而另一个字符串之前没有空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！