MySQL选择包含前导或尾随空格的字段 [英] MySQL select fields containing leading or trailing whitespace

问题描述

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

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

UPDATE Foo SET field = TRIM(field);

在运行之前，我想实际看到将影响的字段.我试过了，但返回0个结果:

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

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

推荐答案

如 CHAR和VARCHAR类型:

所有MySQL归类均为PADSPACE类型.这意味着将比较MySQL中的所有CHAR和VARCHAR值，而不考虑任何尾随空格.

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 the definition of the LIKE operator, the manual states:

尤其是，尾随空格很重要，对于 CHAR 或 VARCHAR 与

In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:

如此答案中所述:

此行为在SQL-92和SQL:2008中指定.为了进行比较，将较短的字符串填充为较长的字符串的长度.

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.

摘自草案(8.2< comparison谓词>):

From the draft (8.2 <comparison predicate>):

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

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))

这篇关于MySQL选择包含前导或尾随空格的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！