mysql ID转换为名称_mysql 逗号分隔的id转为逗号分隔的名称（或者名称转id）

今天处理一个需求，表字段中存的是以逗号分割的code，另一张表中存放着code对应的code_name，现在又要要求把表中的字段的code转换成code_name并以逗号分割，形式和表字段相同。

需求说明：​

达到的效果：

为了解决这个问题我就想到以前用sqlserver写的split函数，准备拿过来修改下，结果发现两个create function 语法不通，试图修改了下然后发现MySQL貌似不支持表变量。这下完了，就不能把逗号分割的code以表的形式传出来去匹配了。然后去网上找mysql的split函数发现怎么这么麻烦，写一个函数，还要先创建两个函数做铺垫。果断放弃了。

然后找了一些资料用mysql自带的函数解决了这个问题

1、INSTR函数(INSTR相当于sqlserver的CharIndex函数)

INSTR(字段名, 字符串)

这个函数返回字符串在某一个字段的内容中的位置, 没有找到字符串返回0，否则返回位置(从1开始)

select GROUP_CONCAT(code_name) from t_fix_code

where INSTR((select engine_number from t_inventory_tmp where id =1 ),code)>0 and length(code)=8

虽然感觉貌似可以用了，但是有个问题就是如果以逗号分割的code在不通的长度都对应一个code_name的话匹配出来的结果会增多。还有instr函数子查询只能返回一行，无法进行整表匹配。不过还是可以解决一条的问题。

就对INSTR重新运用了下

select a.engine_number,GROUP_CONCAT(b.code_name)

from t_inventory_tmp a

join t_fix_code b

on INSTR(a.engine_number,b.code)>0 and length(code)=8

group by a.engine_number

这个解决了问题但是条件里面有一条length(code) = 8 简称我必须要先明确code的长度并且还要统一长度，负责就会出现匹配结果变多，还是很尴尬。

2、FIND_IN_SET函数

假如字符串str在N子链以逗号分割组成的字符串列表strlist 中，则返回值的范围在1到N之间。 如果str不在strlist 或strlist 为空字符串，则返回值为0。

关于FIND_IN_SET用法细节我还是不太清楚，有时间再了解下。

select b.engine_number,GROUP_CONCAT(code_name) as tran_name

from t_fix_code a,t_inventory_tmp b

where FIND_IN_SET(a.code,b.engine_number) > 0

group by b.engine_number

或者

select b.engine_number,GROUP_CONCAT(code_name) as tran_name

from t_fix_code a

join t_inventory_tmp b ON FIND_IN_SET(a.code,b.engine_number) > 0

group by b.engine_number

以上是转自http://blog.csdn.net/wang1988081309/article/details/72920960

重点是GROUP_CONCAT函数和FIND_IN_SET函数

GROUP_CONCAT()函数

GROUP_CONCAT函数返回一个字符串结果，该结果由分组中的值连接组合而成。

使用表info作为示例，其中语句SELECT locus,id,journal FROM info WHERE locus IN('AB086827','AF040764');的返回结果为

+----------+----+--------------------------+

| locus    | id | journal                  |

+----------+----+--------------------------+

| AB086827 |  1 | Unpublished              |

| AB086827 |  2 | Submitted (20-JUN-2002)  |

| AF040764 | 23 | Unpublished              |

| AF040764 | 24 | Submitted (31-DEC-1997)  |

+----------+----+--------------------------+

1、使用语法及特点：

GROUP_CONCAT([DISTINCT] expr [,expr ...]

[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]

[SEPARATOR str_val])

在 MySQL 中，你可以得到表达式结合体的连结值。通过使用 DISTINCT 可以排除重复值。如果希望对结果中的值进行排序，可以使用 ORDER BY 子句。

SEPARATOR 是一个字符串值，它被用于插入到结果值中。缺省为一个逗号 (",")，可以通过指定 SEPARATOR "" 完全地移除这个分隔符。

可以通过变量 group_concat_max_len 设置一个最大的长度。在运行时执行的句法如下： SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;

如果最大长度被设置，结果值被剪切到这个最大长度。如果分组的字符过长，可以对系统参数进行设置：SET @@global.group_concat_max_len=40000;

2、使用示例：

语句 SELECT locus,GROUP_CONCAT(id) FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus; 的返回结果为

+----------+------------------+

| locus    | GROUP_CONCAT(id) |

+----------+------------------+

| AB086827 | 1,2              |

| AF040764 | 23,24            |

+----------+------------------+

语句 SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;的返回结果为

+----------+----------------------------------------------------------+

| locus    | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') |

+----------+----------------------------------------------------------+

| AB086827 | 2_1                                                      |

| AF040764 | 24_23                                                    |

+----------+----------------------------------------------------------+

语句SELECT locus,GROUP_CONCAT(concat_ws(', ',id,journal) ORDER BY id DESC SEPARATOR '. ') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;的返回结果为

+----------+--------------------------------------------------------------------------+

| locus    | GROUP_CONCAT(concat_ws(', ',id,journal) ORDER BY id DESC SEPARATOR '. ') |

+----------+--------------------------------------------------------------------------+

| AB086827 | 2, Submitted (20-JUN-2002). 1, Unpublished                               |

| AF040764 | 24, Submitted (31-DEC-1997) . 23, Unpublished                            |

+----------+--------------------------------------------------------------------------+

上面的操作只是查询出来；然后怎么更新的话可类似如下sql

UPDATE t_bus_type_detail_copy t SET t.fee_type =

(SELECT x.tran_name FROM

(select b.id,GROUP_CONCAT(a.fee_no) as tran_name

from t_fee_type_config a,t_bus_type_detail_copy b

where FIND_IN_SET(a.fee_type_name,b.fee_type) > 0

group by b.id) x WHERE t.id = x.id);