我可以使用MySQL TRIM()方法来清除包含前导或尾随空格的字段,其中包含UPDATE,如下所示：

UPDATE Foo SET field = TRIM(field);

我想在运行之前看到这些影响的字段.我尝试了这个,但返回0结果：

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

似乎这应该工作,但事实并非如此.

有人有解决方案吗？此外,好奇为什么这不起作用……

解决方法:

如The CHAR and VARCHAR Types所述：

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.

在LIKE操作符的定义中,手册指出：

In particular, trailing spaces are significant, which is not true for 07002 or 07003 comparisons performed with the 07004 operator:

如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>.

一个解决方案

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