当前位置：文江博客话题详情

MySQL CSV 行到多行

发布于 2024-10-17 19:03:28 字数 160 浏览 2 评论 0原文

我需要将旧数据库迁移到新数据库。不幸的是，编写旧数据库的人使用带有逗号分隔外键的字段创建了 n,n 关系。

我想编写一个 mysql 查询（也许使用 insert into ... select）来分割这些逗号分隔的外键，以便我可以构建一个表，其中每行都是外键。

这可能吗？

原文

分享到QQ

分享到微博

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

发布评论

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

好菇凉咱不稀罕他 2024-10-24 19:03:28

在纯 SQL 中执行此操作并不简单。最简单的方法是使用您选择的编程语言依次检索每条记录，并根据逗号分隔字段插入多对多连接表记录。以下伪代码建议您可以使用的方法：

for each (id, csv_foreign_keys) in source_rows do
    foreign_keys = split ',', csv_foreign_keys

    for each fk in foreign_keys do
        insert (id, fk) into many-to-many link table

完成此操作后，可以删除包含逗号分隔外键的现有列。

It's not straightforward to do this in pure SQL. It will be easiest to retrieve each record in turn using a programming language of your choice and insert the many-to-many join table records based on the comma separated field. The following pseudo code suggests an approach that you might use:

for each (id, csv_foreign_keys) in source_rows do
    foreign_keys = split ',', csv_foreign_keys

    for each fk in foreign_keys do
        insert (id, fk) into many-to-many link table

Once you've done this, the existing column holding the comma separated foreign keys can be removed.

回复收藏 0 原文

樱娆 2024-10-24 19:03:28

我的解决方案

DELIMITER $

DROP FUNCTION IF EXISTS SPLITCVS $
DROP PROCEDURE IF EXISTS MIGRATE $

CREATE FUNCTION SPLITCVS (
 x VARCHAR(255),
 delim VARCHAR(12),
 pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '') $

CREATE PROCEDURE MIGRATE () 
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE id INT(11);
    DECLARE csv BLOB;
    DECLARE cur CURSOR FOR SELECT uid,foreigns FROM old;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO id, csv;
        IF done THEN
          LEAVE read_loop;
        END IF;

        IF LENGTH(csv) <> 0 THEN
            SET @i = 0;
            SET @seps = LENGTH(csv) - LENGTH(REPLACE(csv, ',', ''));

            IF RIGHT(csv,1) <> ',' THEN
                SET @seps = @seps + 1;
            END IF;

            WHILE @i < @seps DO
                SET @i = @i + 1; 
                INSERT INTO db.newtable(uid_local,uid_foreign)
                VALUES (id,SPLITCVS(csv,',',@i));
            END WHILE;
        END IF;
    END LOOP;

    CLOSE cur;
END $

CALL MIGRATE() $

DROP FUNCTION SPLITCVS $
DROP PROCEDURE MIGRATE $

My solution

DELIMITER $

DROP FUNCTION IF EXISTS SPLITCVS $
DROP PROCEDURE IF EXISTS MIGRATE $

CREATE FUNCTION SPLITCVS (
 x VARCHAR(255),
 delim VARCHAR(12),
 pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '') $

CREATE PROCEDURE MIGRATE () 
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE id INT(11);
    DECLARE csv BLOB;
    DECLARE cur CURSOR FOR SELECT uid,foreigns FROM old;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO id, csv;
        IF done THEN
          LEAVE read_loop;
        END IF;

        IF LENGTH(csv) <> 0 THEN
            SET @i = 0;
            SET @seps = LENGTH(csv) - LENGTH(REPLACE(csv, ',', ''));

            IF RIGHT(csv,1) <> ',' THEN
                SET @seps = @seps + 1;
            END IF;

            WHILE @i < @seps DO
                SET @i = @i + 1; 
                INSERT INTO db.newtable(uid_local,uid_foreign)
                VALUES (id,SPLITCVS(csv,',',@i));
            END WHILE;
        END IF;
    END LOOP;

    CLOSE cur;
END $

CALL MIGRATE() $

DROP FUNCTION SPLITCVS $
DROP PROCEDURE MIGRATE $

回复收藏 0 原文

~没有更多了~