MySQL函式FIND_IN_SET介紹

背景

前幾天面試遇見一個問題，如果一個表欄位對應另一個表多個欄位，問怎麼用一條SQL語句實現，因為沒有接觸過FIND_IN_SET，當時想了其他很多辦法都沒有有效解決，後來發現可以使用FIND_IN_SET函式實現。

準備工作

mysql> create table movie (
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> type varchar(20)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table movie_type( 
    > id int primary key auto_increment, 
    > type_name varchar(20), 
    > type_desc varchar(255)
    > );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into movie values (null, '速度與激情','1,3,6');
Query OK, 1 row affected (0.03 sec)

mysql> insert into movie values (null, '萬物生長','2,3,5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into movie values (null, '戰狼','1,3');
Query OK, 1 row affected (0.02 sec)

mysql> insert into movie values (null, '星際迷航','10');
Query OK, 1 row affected (0.03 sec)

mysql> insert into movie_type values(1,'動作','動作大片'),(2,'愛情','愛情片'),(3,'驚悚','驚悚片'),(4,'倫理','倫理片'),(5,'科幻','科幻片');
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

試用FIND_IN_SET函式

mysql> select FIND_IN_SET('2','5,4,3,2,1');
+------------------------------+
| FIND_IN_SET('2','5,4,3,2,1') |
+------------------------------+
|                            4 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select * from movie where FIND_IN_SET(2,type);
+----+--------------+-------+
| id | name         | type  |
+----+--------------+-------+
|  2 | 萬物生長     | 2,3,5 |
+----+--------------+-------+
1 row in set (0.00 sec)

mysql> select a.name,a.type, group_concat(b.type_name),
          group_concat(b.type_desc order by b.id desc SEPARATOR ' # ') as type_desc
       from movie a 
       inner join movie_type b 
       on FIND_IN_SET(b.id,a.type)    
       group by a.id ;
+-----------------+-------+---------------------------+-----------------------------------+
| name            | type  | group_concat(b.type_name) | type_desc                         |
+-----------------+-------+---------------------------+-----------------------------------+
| 速度與激情      | 1,3,6 | 動作,驚悚                 | 驚悚片 # 動作大片                 |
| 萬物生長        | 2,3,5 | 驚悚,愛情,科幻            | 科幻片 # 驚悚片 # 愛情片          |
| 戰狼            | 1,3   | 動作,驚悚                 | 驚悚片 # 動作大片                 |
+-----------------+-------+---------------------------+-----------------------------------+
3 rows in set (0.00 sec)

當時想的正則匹配不能實現
mysql> select a.name,a.type, group_concat(b.type_name),
          group_concat(b.type_desc order by b.id desc SEPARATOR ' # ') as type_desc
       from movie a 
       inner join movie_type b 
       on a.type REGEXP b.id    
       group by a.id ;
+-----------------+-------+---------------------------+-----------------------------------+
| name            | type  | group_concat(b.type_name) | type_desc                         |
+-----------------+-------+---------------------------+-----------------------------------+
| 速度與激情      | 1,3,6 | 動作,驚悚                 | 驚悚片 # 動作大片                 |
| 萬物生長        | 2,3,5 | 驚悚,愛情,科幻            | 科幻片 # 驚悚片 # 愛情片          |
| 戰狼            | 1,3   | 動作,驚悚                 | 驚悚片 # 動作大片                 |
| 星際迷航        | 10    | 動作                      | 動作大片                          |
+-----------------+-------+---------------------------+-----------------------------------+
4 rows in set (0.00 sec)

可以看到正則匹配查詢匹配出的資料並不正確，順便複習了一下group_concat的使用方式。

整理自網路