MySQL like查詢字元轉義遇到的坑

pythontab發表於2018-09-04
MySql字元

最近遇到一個問題， 在MySQL like查詢時， 明明字串時相同的， 但是卻like不到結果， 後面經過多方面查詢資料， 終於發現是字串轉義的問題， 而MySQL的LIKE的轉義和普通字串的不同。


首先來看一個測試：

mysql> SET @a='\\';SELECT @a,@a LIKE '\\\\';
 
+----+----------------+
| @a | @a LIKE '\\\\' |
+----+----------------+
| \  |              1 |
+----+----------------+

可以看出”\\\\“在LIKE中轉義成”\“，LIKE的轉義和平時用的字串的轉義還是有點不一樣的。


查了下官方文件，對於跳脫字元的解釋：


this is because the backslashes are stripped once by the parser and again when the pattern match is made,

leaving a single backslash to be matched against.

mysql自己的parser轉義一次，然後LIKE的pattern對前面的結果再轉義一次。


也就是說上例中的“\\\\”第一次轉義成“\\”，第二次才是“\”。


再來看個mysql字元轉義的測試

mysql> SELECT '\%','\_';
+----+----+
| \% | \_ |
+----+----+
| \% | \_ |
+----+----+
1 row in set


呵呵，什麼都轉義（比如"\x" 轉義成“x”，“\b"是回退字元），就是不轉義”\%“和”\_“。而LIKE，都轉義。恐怕MYSQL這樣做的目的也是為了相容LIKE吧。


總結下，LIKE跳脫字元串時，一共兩次，第一次不轉義”\%“和”\_“，第二次全部轉義。轉義的特殊字元可以參見 http://dev.mysql.com/doc/refman/5.1/en/string-literals.html#character-escape-sequences


LIKE的轉義可以看成先替換“\\\\”為“\”,剩下的字串用”\\“轉義,剩下的字串再用”\"轉義。通常不遇見“\\”, LIKE的特殊解析是感覺不到的。


PHP版like特殊字元過濾

function likeEscape($str) {
    return strtr($str, array('%'=>'\\%', '_'=>'\\_', '\\'=>'\\\\\\\\'));
}

注意最後的雙反斜槓轉成了8個反斜槓， 其實就是1轉4， 只不過在PHP字串中需要轉換一次， 這裡有點繞，大家仔細想想，有問題可以留言


相關文章