我的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;
小提琴