mysql csv 字段名_mysql加入csv字段？

I have three tables like below

CREATE TABLE IF NOT EXISTS `cat` (

`id` int(15) NOT NULL AUTO_INCREMENT,

`color_options` varchar(255) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

);

INSERT INTO `cat` (`id`, `color_options`) VALUES (1, '1,2,3,4');

CREATE TABLE IF NOT EXISTS `template` (

`id` int(15) NOT NULL AUTO_INCREMENT,

`cat_id` int(15) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`)

);

INSERT INTO `template` (`id`, `cat_id`) VALUES (1, 1);

CREATE TABLE IF NOT EXISTS `color` (

`id` int(15) NOT NULL AUTO_INCREMENT,

`name` varchar(255) NOT NULL DEFAULT '',

`code` varchar(6) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

);

INSERT INTO `color` (`id`, `name`, `code`) VALUES

(1, 'Black', '000000'),

(2, 'Red', 'FF0000'),

(3, 'Blue', '0000FF'),

(4, 'Green', '00FF00');

If I run

SELECT *

FROM template

LEFT JOIN cat ON cat.id=template.id;

then I will get

id cat_id color_options

1 1 1,2,3,4

How do I get the color options in text(Black,Red,BLue,Green) instead of plain numbers?

I tried using join but it doesn't work on csv field.

Thanks in advance

解决方案

A sub query with GROUP_CONCAT()

SELECT *,(SELECT GROUP_CONCAT(name) FROM color WHERE CONCAT(',',cat.color_options,',') LIKE CONCAT('%,',color.id,',%')) color_options_text

FROM template

LEFT JOIN cat ON cat.id=template.id

Someone else used a function new to me, this also works & is cleaner to read

SELECT *,(SELECT GROUP_CONCAT(name) FROM color WHERE FIND_IN_SET(color.id,cat.color_options)) color_options_text

FROM template

LEFT JOIN cat ON cat.id=template.id