从逗号分隔列表到单个项目结果集。 *mysql

发布于 2024-08-31 05:22:37 字数 404 浏览 3 评论 0原文

我正在做一些数据迁移，从一个设计糟糕的数据库到一个设计不那么糟糕的数据库。存在多对多关系，一个表中的主键对应于另一个表中的逗号分隔列表。

FK_ID | data
-------------
1,2   | foo
3     | bar
1,3,2 | blarg

有没有办法将 FK_ID 字段与每个逗号分隔的元素作为结果集中的单行输出？

result set
FK_ID | data
-------------
1     | foo
2     | foo
3     | bar
1     | blarg
2     | blarg
3     | blarg

我认为这需要某种递归查询，但我认为 mysql 没有。

提前致谢。

原文

I'm doing some data migration from a horribly designed database to a less horribly designed database. There is a many to many relationship that has a primary key in one table that corresponds to a comma separated list in another.

FK_ID | data
-------------
1,2   | foo
3     | bar
1,3,2 | blarg

Is there a way to output the FK_ID field with each comma separated element as a single line in the result set?

result set
FK_ID | data
-------------
1     | foo
2     | foo
3     | bar
1     | blarg
2     | blarg
3     | blarg

I'm thinking this would require some sort of recursive query which I don't think mysql has.

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

扬花落满肩 2024-09-07 05:22:37

对我来说，最简单的方法是编写一个脚本来查询源表并将记录插入目标表。

伪代码：

query = "select * from sourcetable";
get a reader object;
while reading()
{
    orig_FK = reader(FK_ID);
    orig_data = reader(data);
    orig_FK_array = orig_FK split by comma
    foreach(ID in orig_FK_array)
    {
        query = "insert into targettable (ID, data) values (@ID, @Data);";
        add parameters;
        execute query;
    }
}

For me the easiest way to do this would be to write a script that queries the source table and inserts the records into the target table.

Pseudocode:

query = "select * from sourcetable";
get a reader object;
while reading()
{
    orig_FK = reader(FK_ID);
    orig_data = reader(data);
    orig_FK_array = orig_FK split by comma
    foreach(ID in orig_FK_array)
    {
        query = "insert into targettable (ID, data) values (@ID, @Data);";
        add parameters;
        execute query;
    }
}

回复收藏 0 原文

Smile简单爱 2024-09-07 05:22:37

如果您有一个 Numbers/Tally 表（该表包含连续的整数列表），则可以在单个查询中完成此操作。

Select Substring(T.FK_ID
   , N.Value
   , CharIndex(',', T.FK_ID + ',', N.Value) - N.Value)
  , T.Data
From Numbers As N
 Cross Join Table As T
Where N.Value <= Len(T.FK_ID)
 And Substring(',' + T.FK_ID, N.Value, 1) = ','

If you have a Numbers/Tally table, which is a table with a sequential list of integers, you can do it in a single query.

Select Substring(T.FK_ID
   , N.Value
   , CharIndex(',', T.FK_ID + ',', N.Value) - N.Value)
  , T.Data
From Numbers As N
 Cross Join Table As T
Where N.Value <= Len(T.FK_ID)
 And Substring(',' + T.FK_ID, N.Value, 1) = ','

回复收藏 0 原文

~没有更多了~