mysql - 将一列中的逗号分隔值传输到 MySQL 中的不同行

Question

我在“Database Administrator”中提出了类似的问题，但遗憾的是没有人回答这个问题，所以我决定传输数据。

我有一个这样的表:

------------------------------------------------------ 
Parts    | Owner | Number   | Item_ID | ...
------------------------------------------------------
PartB    | Adam  |    4     |   Item_a,Item_b,Item_z,...
ConD     | Steve |    2     |   Item_b,Item_c,Item_g,...

我想将每个值作为单独的行:

------------------------------------------------------ 
Parts    | Owner | Number   | Item_ID | ...
------------------------------------------------------
PartB    | Adam  |    4     |   Item_a
PartB    | Adam  |    4     |   Item_b
PartB    | Adam  |    4     |   Item_z
ConD     | Steve |    2     |   Item_b
ConD     | Steve |    2     |   Item_c
ConD     | Steve |    2     |   Item_g

基于this answer ，我尝试过:

SELECT Parts, 
       Owner,
       Split.a.value('.', 'VARCHAR(100)') Item_ID
FROM   (SELECT Parts,
               Owner,
               Cast ('<M>' + Replace(Item_ID, ',', '</M><M>') + '</M>' AS XML) AS Data
        FROM  Table_1) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a) 

但是我收到错误:

You have an error in your SQL syntax...near '('.', 'VARCHAR(100)')... 

Answer 1

可以使用 C# 等第三方语言轻松完成，因为您只需运行一次。在本例中，使用像 Dapper 这样的轻量 ORM 会更容易:

[Table("TableA")]
class table {
    [Key]
    int id {get; set;}
    string Parts {get; set;}
    string Owner {get; set;}
    int Number {get; set;}
    string Item_ID {get; set;}
    ... 
}

[Table("TableB")]
class newtable {
    [Key]
    int id {get; set;}
    string Parts {get; set;}
    string Owner {get; set;}
    int Number {get; set;}
    string Item_ID {get; set;}
    ... 
}

Func<SqlConnection> conn = () => new SqlConnection("Data Source=127.0.0.1; Initial Catalog=;User Id=root;Password=***");
var newdata = new List<newtable>();
foreach (var d in conn().Query<table>("Select * FROM table"))
{
    foreach (var cd in d.Item_ID.Split(','))
    {
        newdata.Add(new newtable() { Parts = d.Parts, Owner = d.Owner, Number = d.Number, Item_ID = cd.Trim(), ... });
    }
}

//insert the new datas
foreach (var data in newdata) {
    conn().Insert(data);
}