MaraiDB – Equals(=) vs. LIKE

測試環境為 CentOS 8 x86_64

遇到一個奇怪的問題,當使用 Equals(=) 與 LIKE 來比對字串 String ”(空字串) 與 ‘ ‘ (空白字串) ,卻得要不一樣的結果.

MariaDB [(none)]> SELECT ' ' LIKE '';
+-------------+
| ' ' LIKE '' |
+-------------+
|           0 |
+-------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT ' ' = '';
+----------+
| ' ' = '' |
+----------+
|        1 |
+----------+
1 row in set (0.002 sec)

查了一下論壇 – https://stackoverflow.com/questions/543580/equals-vs-like

Equals(=) 的比對方式:

1. 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 attribute, 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 .
2. The result of the comparison of X and Y is given by the collating sequence CS.
3. Depending on the collating sequence, two strings may compare as equal even if they are of different lengths or contain different sequences of characters. When the operations MAX, MIN, DISTINCT, references to a grouping column, and the UNION, EXCEPT, and INTERSECT operators refer to character strings, the specific value selected by these operations from a set of such equal values is implementation-dependent.

裡面提到當兩邊字串長度不同時,較短的字串右側將延展為較長字串的長度,填充字元是根據 CS (Character Set) 來選擇填充字符 (Pad Character)

LIKE 的比對方式:

1. A substring of M is a sequence of 0 or more contiguous s of M and each of M is part of exactly one substring.
2. If the i-th substring specifier of P is an arbitrary character specifier, the i-th substring of M is any single .
3. If the i-th substring specifier of P is an arbitrary string specifier, then the i-th substring of M is any sequence of 0 or more s.
4. If the i-th substring specifier of P is neither an arbitrary character specifier nor an arbitrary string specifier, then the i-th substring of M is equal to that substring specifier according to the collating sequence of the , without the appending of characters to M, and has the same length as that substring specifier.
5. The number of substrings of M is equal to the number of substring specifiers of P.

回到前面的範例,根據 Equals(=) 的比對方式, ” (空字串) 長度與 ‘ ‘ (空白字串不相等),所以在使用 Equals(=) 比對時,” (空字串) 會被填充 ‘ ‘ 空白填充字元 (Pad Character),變成 ‘ ‘(後面多一個空白字元),也因此當使用 Equals(=) 比對時 ”(空字串) 會等於 ‘ ‘ (空白字串).

MariaDB [(none)]> SELECT ' ' = '';
+----------+
| ' ' = '' |
+----------+
|        1 |
+----------+
1 row in set (0.002 sec)

MariaDB [(none)]> SELECT '   ' = '';
+------------+
| '   ' = '' |
+------------+
|          1 |
+------------+
1 row in set (0.001 sec)