遇到一個 mysql 的坑,關于字元串連接配接的問題,分享一下
mysql> select * from my_table;
+----+--------+------------+
| id | mod_id | mod_name |
+----+--------+------------+
| 1 | 20 | red |
| 2 | 20 | blue |
| 3 | 20 | pink |
| 4 | 21 | yellow |
| 5 | 21 | green |
| 6 | 21 | white |
| 7 | 21 | black |
| 8 | 30 | gray |
| 9 | 30 | purple |
| 10 | 30 | pinkpurple |
| 11 | 30 | red purple |
+----+--------+------------+
11 rows in set (0.00 sec)
原始表資料。
mysql> select * from my_table where mod_name in ("red", 'blue', 'pink', 'purple');
+----+--------+----------+
| id | mod_id | mod_name |
+----+--------+----------+
| 1 | 20 | red |
| 2 | 20 | blue |
| 3 | 20 | pink |
| 9 | 30 | purple |
+----+--------+----------+
4 rows in set (0.00 sec)
mysql> select * from my_table where mod_name in ("red", 'blue', 'pink' 'purple');
+----+--------+------------+
| id | mod_id | mod_name |
+----+--------+------------+
| 1 | 20 | red |
| 2 | 20 | blue |
| 10 | 30 | pinkpurple |
+----+--------+------------+
3 rows in set (0.00 sec)
mysql> select * from my_table where mod_name = ('pink' 'purple');
+----+--------+------------+
| id | mod_id | mod_name |
+----+--------+------------+
| 10 | 30 | pinkpurple |
+----+--------+------------+
1 row in set (0.00 sec)
使用 = 符号條件精确查詢,仍然解釋為 兩個字元串的連接配接結果。
mysql> select * from my_table where mod_name = 'pink' 'purple';
+----+--------+------------+
| id | mod_id | mod_name |
+----+--------+------------+
| 10 | 30 | pinkpurple |
+----+--------+------------+
1 row in set (0.00 sec)
使用 = 符号條件精确查詢,取消掉括弧,仍然解釋為 兩個字元串的連接配接結果。
mysql> select * from my_table where mod_name = '' 'purple';
+----+--------+----------+
| id | mod_id | mod_name |
+----+--------+----------+
| 9 | 30 | purple |
+----+--------+----------+
1 row in set (0.00 sec)
頭一個字元串為0長度字元串,那麼合并即等于 purple 字元串。
mysql> select * from my_table where mod_name = 'red'' ' 'purple';
Empty set (0.00 sec)
寫三個字元串,其中第一個字元串的單引号和第二個字元串的單引号中間沒有空格,那麼解釋為未知字元。。。。。
mysql> select * from my_table where mod_name = 'red' ' ' 'purple';
+----+--------+------------+
| id | mod_id | mod_name |
+----+--------+------------+
| 11 | 30 | red purple |
+----+--------+------------+
1 row in set (0.00 sec)
'red' ' ' 'purple' ,每一組字元串用空格隔開,那麼就合并為 red purple 字元串。