MySQL 选择包含前导或尾随空格的字段

MySQL select fields containing leading or trailing whitespace

提问by Michael Irey

I can use the MySQL TRIM()method to cleanup fields containing leading or trailing whitespace with an UPDATElike so:

我可以使用 MySQLTRIM()方法清理包含前导或尾随空格的字段，UPDATE如下所示：

UPDATE Foo SET field = TRIM(field);

I would like to actually see the fields this will impact before this is run. I tried this but returns 0 results:

我想在运行之前实际查看这将影响的字段。我试过了，但返回 0 结果：

SELECT * FROM Foo WHERE field != TRIM(field);

Seems like this should work but it does not.

看起来这应该有效，但事实并非如此。

Anyone have a solution? Also, curious why this does not work...

有人有解决方案吗？另外，好奇为什么这不起作用......

回答by eggyal

As documented under The CHARand VARCHARTypes:

如TheCHAR和VARCHARTypes 所述：

All MySQL collations are of type PADSPACE. This means that all CHARand VARCHARvalues in MySQL are compared without regard to any trailing spaces.

所有 MySQL 归类都是PADSPACE. 这意味着在不考虑任何尾随空格的情况下比较 MySQL 中的所有CHAR和VARCHAR值。

In the definition of the LIKEoperator, the manual states:

在LIKE操作员的定义中，手册指出：

In particular, trailing spaces are significant, which is not true for CHARor VARCHARcomparisons performed with the =operator:

特别是，尾随空格很重要，这对于使用运算符执行的CHAR或VARCHAR进行的比较而言是不正确的=：

As mentioned in this answer:

正如这个答案中提到的：

This behavior is specified in SQL-92 and SQL:2008. For the purposes of comparison, the shorter string is padded to the length of the longer string.

From the draft (8.2 <comparison predicate>):

If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad characters, where the pad character is chosen based on CS. If CS has the NO PAD characteristic, then the pad character is an implementation-dependent character different from any character in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>.

此行为在 SQL-92 和 SQL:2008 中指定。出于比较的目的，较短的字符串被填充到较长字符串的长度。

从草案（8.2 <比较谓词>）：

如果 X 的字符长度不等于 Y 的字符长度，那么为了比较的目的，较短的字符串被有效地替换为自身的副本，该副本已通过连接扩展到较长字符串的长度在一个或多个填充字符的右侧，其中填充字符是根据 CS 选择的。如果 CS 具有 NO PAD 特性，则该填充字符是一个与实现相关的字符，不同于 X 和 Y 字符集中的任何字符，它比 CS 下的任何字符串都少。否则，填充字符是 <space>。

One solution:

一种解决方案：

SELECT * FROM Foo WHERE CHAR_LENGTH(field) != CHAR_LENGTH(TRIM(field))

回答by user4035

SELECT *
FROM 
    `foo`
WHERE 
   (name LIKE ' %')
OR 
   (name LIKE '% ')

回答by EpixRu

Here is an example with RegEx

这是一个使用RegEx的示例

SELECT *
FROM 
    `foo`
WHERE 
   (name REGEXP '(^[[:space:]]|[[:space:]]$)')