mysql函数大全find_in_set_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的使用方式。

整理自网络

Svoid

2015-04-24