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

Question

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

40

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

UPDATE Foo SET field = TRIM(field);

在运行这个之前，我希望能够实际看到这会影响哪些字段。我尝试了这个方法，但是返回了 0 个结果：

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

看起来这应该可以工作，但实际上并没有。

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

- Michael

请参考这个答案：https://dev59.com/dnE95IYBdhLWcg3wUMPW#2363449。另外，对于`!=`，可以尝试使用`<>`。 - David Starkey

@David：!=和<>似乎没有区别。 - Michael

eggyal解释了尾随空格。根据您的查询结果，可以安全地假设您的值没有前导空格。 - Dan Bracuk

4个回答

42

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

- user4035

正确！根据MySQL中这个被充分记录的特殊性，LIKE会尊重前导和尾随空格，而=则不会：https://bugs.mysql.com/bug.php?id=64772 - Joshua Pinter

9

以下是一个使用正则表达式的示例:

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

- EpixRu

假设 OP 所说的空格是指 whitespace 而非仅限于 U+0020，那么这个回答才是正确的。 - maaartinus

0

另一种解决方案可能是使用 SUBSTRING() 和 IN 来将字符串的最后一个字符和第一个字符与空格字符列表进行比较...

(SUBSTRING(@s,  1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(@s, -1, 1) IN (' ', '\t', '\n', '\r'))

...其中@s是任何输入字符串。根据您的情况，可以添加额外的空格字符到比较列表中。

以下是一个简单的测试，演示该表达式如何处理各种字符串输入：

SET @s_normal = 'x';
SET @s_ws_leading = '\tx';
SET @s_ws_trailing = 'x ';
SET @s_ws_both = '\rx ';

SELECT
    NOT(SUBSTRING(@s_normal,      1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(@s_normal,     -1, 1) IN (' ', '\t', '\n', '\r')) test_normal      #=> 1 (PASS)
  ,    (SUBSTRING(@s_ws_leading,  1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(@s_ws_leading, -1, 1) IN (' ', '\t', '\n', '\r')) test_ws_leading  #=> 1 (PASS)
  ,    (SUBSTRING(@s_ws_trailing, 1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(@s_ws_trailing,-1, 1) IN (' ', '\t', '\n', '\r')) test_ws_trailing #=> 1 (PASS)
  ,    (SUBSTRING(@s_ws_both,     1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(@s_ws_both,    -1, 1) IN (' ', '\t', '\n', '\r')) test_ws_both     #=> 1 (PASS)
;

如果这是您经常要做的事情，您也可以为此创建一个函数：

DROP FUNCTION IF EXISTS has_leading_or_trailing_whitespace;

CREATE FUNCTION has_leading_or_trailing_whitespace(s VARCHAR(2000))
  RETURNS BOOLEAN
  DETERMINISTIC
RETURN (SUBSTRING(s, 1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(s, -1, 1) IN (' ', '\t', '\n', '\r'))
;

# test
SELECT
    NOT(has_leading_or_trailing_whitespace(@s_normal     )) #=> 1 (PASS)
  ,     has_leading_or_trailing_whitespace(@s_ws_leading )  #=> 1 (PASS)
  ,     has_leading_or_trailing_whitespace(@s_ws_trailing)  #=> 1 (PASS)
  ,     has_leading_or_trailing_whitespace(@s_ws_both    )  #=> 1 (PASS)
;

- raven-rock

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- eggyal · Accepted Answer

作为CHAR和VARCHAR类型下记录的，所有MySQL排序规则都是PADSPACE类型。这意味着，在MySQL中，所有的CHAR和VARCHAR值都不考虑任何尾随空格进行比较。

在LIKE运算符的定义中，手册说明:

特别地，尾随空格是有意义的，而对于使用=运算符执行的CHAR或VARCHAR比较，则不是这样。

正如此答案中提到的:

这种行为在SQL-92和SQL:2008中有规定。为了比较，短字符串将填充到与长字符串相同的长度。从草案（8.2<比较谓词>）中可以看出：如果X的字符长度不等于Y的字符长度，则为了比较，短字符串将被有效地替换为自身的副本，该副本通过在右侧连接一个或多个填充字符来扩展到长字符串的长度，其中填充字符是根据CS选择的。如果CS具有NO PAD特性，则填充字符是一个实现相关的字符，不同于X和Y字符集中的任何字符，并且在CS下排列小于任何字符串。否则，填充字符是一个空格。一种解决方案：

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