mysql 查询条件末尾有空格时被忽略

• 2021-02-26
• 1645 View

　最近再使用mysql时，无意见发现，当我查询参数尾部输入若干个空格时，依然可以查出和不加空格相同的结果，像这样

 

1

2

3

select * from wa where name='be   '

  等同于

select * from wa where name='be';

　　查询大量资(bai)料(du)后，基本给出的结论是这样的：如果字段是char或varchar类型，那么在字符串比较的时候MySQL使用PADSPACE校对规则，会忽略字段末尾的空格字符，这样然后我又查(搜)询(索)了一些资料，在stackoverflow上找到了一些较为专业的解答，先上地址：https://stackoverflow.com/questions/10495692/mysql-comparison-operator-spaces ，大意就是 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>. 嗯，看懂没。

 

　　如果你有特殊需求，需要判断尾部的空格，对于字符串你可以使用  like ，如 like 'be   '，或者 使用 binary ,还有一种方法就是 使用 CHAR_LENGTH ，如  CHAR_LENGTH(name)=CHAR_LENGTH('be    ')。