MySQL使用游标克隆现有表

-- 创建新表，结构与现有表相同
CREATE TABLE new_table LIKE existing_table;

-- 使用游标逐行复制数据
DELIMITER $$
CREATE PROCEDURE clone_table()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE id INT;
    DECLARE name VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT id, name FROM existing_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO id, name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO new_table (id, name) VALUES (id, name);
    END LOOP;

    CLOSE cur;
END$$
DELIMITER ;

-- 调用存储过程克隆表
CALL clone_table();

INSERT INTO new_table (id, name)
SELECT id, name FROM existing_table;

LOCK TABLES existing_table READ;
INSERT INTO new_table (id, name)
SELECT id, name FROM existing_table;
UNLOCK TABLES;

SHOW CREATE TABLE existing_table;