MySQL使用INSERT INTO / SELECT分割由逗号分隔的字符串值

I have a field named names in a table. This field shows "last_name, first_name". I am trying to take that field from the names table and insert the values into a new table into 2 different columns labeled last_name and first_name. I have tried the following codes:

我在表中有一个名为names的字段。该字段显示“last_name，first_name”。我试图从名称表中取出该字段，并将值插入到一个新表中，分为2个标记为last_name和first_name的不同列。我试过以下代码：

INSERT INTO usernames (last_name,first_name)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',1),',',-1) as last_name,
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',2),',',-1) as first_name
FROM person;

and

和

INSERT INTO usernames (last_name,first_name)
SELECT IF(LOCATE(', ',name)>0
SUBSTRING(name,1,LOCATE(', ',name)-1) AS last_name,
IF(LOCATE(', ',name)>0
SUBSTRING(name,LOCATE(', ',name)+1),NULL) AS first_name
FROM person;

I'm not sure what I am doing wrong with this code. Any help Is greatly appreciated.

我不确定这个代码我做错了什么。任何帮助是极大的赞赏。

Thank you, Larry

拉里，谢谢你

1 个解决方案

#1

your query is almost ok, but second SELECT is not needed. check the syntax of SELECT in documentation.

您的查询几乎没问题，但不需要第二个SELECT。检查文档中SELECT的语法。

INSERT INTO usernames (last_name,first_name)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',1),',',-1) as last_name,
SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',2),',',-1) as first_name
FROM person;