提问者:小点点

MySQL-如何使用逗号拆分单个字符串中的值


我的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中使用逗号拆分字符串? 谢谢


共2个答案

匿名用户

请考虑以下事项。 我有一个由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;

小提琴