在mysql中插入逗号分隔值

I am stuck with the following problem :

我遇到了以下问题:

I have table 'questions'(question_id,answer) which has 2 columns both are varchar.

我有表'问题'(question_id,answer),其中有两列都是varchar。

The input values are like ('1,2,3' , 'one,two,three') I need insert query for this and I wish the values should be inserted as :

输入值类似于('1,2,3','一,二,三')我需要插入查询,我希望值插入为:

1 one 
2 two
3 three

Thanks in advance .

提前致谢。

2 个解决方案

#1

Try this as per SQl Server

按照SQl Server尝试此操作

DECLARE @id VARCHAR(50)= '1,2,3', 
@Answer VARCHAR(50)= 'i,know,you',
 @a VARCHAR(50), 
 @b VARCHAR(50);

WHILE LEN(@id) > 0
    BEGIN
        SET @a = LEFT(@id, CHARINDEX(',', @id+',')-1);
        SET @b = LEFT(@Answer, CHARINDEX(',', @Answer+',')-1);

        INSERT INTO #temp
        VALUES
        (@a,
         @b
        );

        SET @id = STUFF(@id, 1, CHARINDEX(',', @id+','), '');
        SET @Answer = STUFF(@Answer, 1, CHARINDEX(',', @Answer+','), '');
    END;
SELECT *
FROM #temp;
DROP TABLE #temp;

#2

It's normal insert statement.

这是正常的插入语句。

INSERT INTO questions (question_id,answer) VALUES ('1', 'one');
INSERT INTO questions (question_id,answer) VALUES ('2', 'two');
INSERT INTO questions (question_id,answer) VALUES ('3', 'three');

#1