我的MySql(8.0)有两个表，main:
ID | TITLE | kindof
1 | aaa | shop
2 | bbb | food
3 | ccc | market
类别:
ID | TITLE | CATEGORY
1 | aaa | design, home, clothing
2 | bbb | asian, indian
3 | ccc | second hand
我的Node/Express（主ID为自动递增）:
让sql=开始； 插入主（title，kindof）值（“${[req.body.title]}”，“${req.body.kindof}）；插入类别（id，title，category）值(LAST_INSERT_ID()，”${[req.body.title]}“，”${[req.body.categories]}“）；提交；；
我希望:
ID | TITLE | CATEGORY
1 | aaa | design
1 | aaa | home
1 | aaa | clothing
2 | bbb | asian
2 | bbb | indian
3 | ccc | second hand
Req.Body.Category类似于:{design，home，Koying}
如何在MySQL中使用逗号拆分字符串？ 谢谢
请考虑以下事项。 我有一个由0到9的整数(I)组成的表（ints）。。。
DROP TABLE IF EXISTS ints;
CREATE TABLE ints(i INT NOT NULL PRIMARY KEY);
INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
DROP TABLE IF EXISTS category;
CREATE TABLE category
(id INT NOT NULL
,title VARCHAR(12) NOT NULL
,category VARCHAR(100) NOT NULL
);
INSERT INTO category VALUES
(1,'aaa','design, home, clothing'),
(2,'bbb','asian, indian'),
(3,'ccc','second hand');
DROP TABLE IF EXISTS main_category;
CREATE TABLE main_category
AS
SELECT DISTINCT id
, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(category,',',i+1),',',-1)) category
FROM category
, ints;
DROP TABLE IF EXISTS category;
ALTER TABLE main_category ADD PRIMARY KEY(id,category);
SELECT * FROM main_category;
+----+-------------+
| id | category |
+----+-------------+
| 1 | design |
| 2 | asian |
| 3 | second hand |
| 1 | home |
| 2 | indian |
| 1 | clothing |
+----+-------------+
例如，在MySQL8+中，您可以使用，
SELECT category.ID, category.TITLE, TRIM(jsontable.value) CATEGORY
FROM category
CROSS JOIN JSON_TABLE( CONCAT('["', REPLACE(category.CATEGORY, ',', '","'), '"]'),
"$[*]" COLUMNS( value VARCHAR(254) PATH "$" )
) AS jsontable
ORDER BY category.ID, jsontable.value;
小提琴
