时间戳

首页 > 解决方案 > 如何在不修改主键 ID 的情况下向上或向下移动数据库表内容？

mysql - 如何在不修改主键 ID 的情况下向上或向下移动数据库表内容？

问题描述

我有一个表，其中第 29 行到第 42 行之间的一些数据意外地偏离了 1。修改外键值以指向正确的主键 ID 是很痛苦的。

我想知道如何在不触及主键 ID 的情况下向上或向下移动内容。不过，我不想更改主键 ID。

标签： mysqlsql

解决方案

行移数据库表行内容向下

向上/向下移动行内容以“冒泡”内容。这是向下的脚本。向下移动时，第 42 行将循环回到第 29 行。稍微编辑脚本以满足您的需要。

# Works and tested in MySQL 5.
# Assuming we are row shifting downwards from row 29 to row 42.
# "counter" is the starting index row of where to start row shifting.

DELIMITER //

DROP PROCEDURE IF EXISTS mysp //
CREATE PROCEDURE mysp()
BEGIN
    DECLARE counter INT;
    DECLARE startIndex INT;
    DECLARE endIndex INT;
    SET startIndex = 29;
    SET endIndex = 42;
    SET counter = startIndex;

    UPDATE
        yourTable t1 INNER JOIN yourTable t2
        ON (t1.id, t2.id) 
        IN ((startIndex,endIndex),(endIndex,startIndex))
    SET
        t1.column1 = t2.column1;
        # Add your additional columns here.

    label1: WHILE counter < endIndex-1 DO
        UPDATE
            yourTable t1 INNER JOIN yourTable t2
            ON (t1.id, t2.id) 
            IN ((counter+1,endIndex),(endIndex,counter+1))
        SET
            t1.column1 = t2.column1;
            # Add your additional columns here.

        SET counter = counter + 1;
    END WHILE label1;
END; //

CALL mysp() //

DELIMITER ;

行移数据库表行内容向上

这是向上移动的脚本。向上移动时，第 29 行将循环回到第 42 行。

# Works and tested in MySQL 5.
# Assuming we are row shifting upwards from row 42 to row 29.
# "counter" is the starting index row of where to start row shifting.

DELIMITER //

DROP PROCEDURE IF EXISTS mysp //
CREATE PROCEDURE mysp()
BEGIN
    DECLARE counter INT;
    DECLARE startIndex INT;
    DECLARE endIndex INT;
    SET startIndex = 29;
    SET endIndex = 42;
    SET counter = endIndex;

    UPDATE
        yourTable t1 INNER JOIN yourTable t2
        ON (t1.id, t2.id) 
        IN ((startIndex,endIndex),(endIndex,startIndex))
    SET
        t1.column1 = t2.column1;
        # Add your additional columns here.

    label1: WHILE counter > startIndex DO
        UPDATE
            yourTable t1 INNER JOIN yourTable t2
            ON (t1.id, t2.id) 
            IN ((counter-1,startIndex),(startIndex,counter-1))
        SET
            t1.column1 = t2.column1;
            # Add your additional columns here.

        SET counter = counter - 1;
    END WHILE label1;
END; //

CALL mysp() //

DELIMITER ;

推荐阅读