MySQL on the command line, the pits between strings with fewer comma separators

Encounter a MySQL pit, a question about string connections, share a bit

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)

Raw table data.

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)

Use the Where condition to query the columns in the matching list. (normal query)

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)

Note that the last set of values, (' Pink ' purple ') is missing a comma, so the query results are interpreted by default as ' Pinkpurple ' strings.

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) uses the = symbol condition for exact query, still interpreted as the result of a connection of two strings. 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) uses the = sign condition for exact queries, cancels out the brackets, and still interprets the result of the connection as a two string. Mysql> SELECT * from my_table where mod_name = ' purple '; +----+--------+----------+| ID | mod_id |  Mod_name |+----+--------+----------+|     9 | 30 | Purple |+----+--------+----------+1 row in Set (0.00 sec) Header One string is a 0-length string, then the merge equals the purple string. Mysql> SELECT * from my_table where mod_name = ' red ' ' Purple '; Empty Set (0.00 sec) writes three strings, where the single quotation mark of the first string and the second string have no spaces in the middle of the single quotation mark, then interpreted as an unknown character .... 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 ', each set of strings separated by spaces, then merged into a red purple string.

Originally, this should be reported syntactically wrong, but,,,,,
Alas, was the pit,,,,

MySQL on the command line, the pits between strings with fewer comma separators