mysql 關於命令列下，字串間少逗號分隔字元的坑

標籤：concat   字串串連   mysql   字串   坑   

遇到一個 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)

使用 where 條件查詢匹配列表中的列。（正常查詢）

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)

注意最後一組值，(‘pink‘ ‘purple‘) 中間少了個逗號，那麼查詢出來的結果，預設會解釋為 ‘pinkpurple‘ 字串。

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 字串。

本來，這應該報語法錯誤的，但是，，，，，
唉，被坑了，，，，

mysql 關於命令列下，字串間少逗號分隔字元的坑