mysql 拷贝表锁表吗,mysql - 如何从另一个表拷贝数据，而无需在MYSQL6.2锁表？ - SO中文参考 - www.soinside.com...

为了减少使用OFFSET的缺点，this article描述使用JOIN当一个数字主键id可强制使用适当的指数的可能途径。需要注意的是跟踪过程中，一个“procedure_log”表中创建并逐步处理了一批又更新：

对于MySQL：

DROP PROCEDURE IF EXISTS copyTable;

DELIMITER |

CREATE PROCEDURE copyTable()

BEGIN

DECLARE batchSize INT DEFAULT 100;

DECLARE i INT DEFAULT 0;

DECLARE rowCount INT;

# Note that we use a WHERE clause to prevent a full table scan / use the index properly

SET rowCount = (SELECT COUNT(id) FROM my_table WHERE id IS NOT NULL);

CREATE TABLE IF NOT EXISTS my_table_copy LIKE my_table;

CREATE TABLE IF NOT EXISTS procedure_log ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, entry TEXT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );

WHILE i <= rowCount DO

INSERT IGNORE INTO my_table_copy (

SELECT source.* FROM (

SELECT id FROM my_table ORDER BY id LIMIT i, batchSize

) tmp

JOIN my_table source ON source.id = tmp.id

ORDER BY source.id

);

SET i = i + batchSize;

INSERT INTO procedure_log (entry) VALUES (CONCAT('Copied batch from my_table => my_table_copy, batch: ', batchSize, ', offset: ', i, ', rowCount: ', rowCount));

END WHILE;

END |

DELIMITER ;