如何自动地将行从一个表移动到另一个表？

Question

我正在收集数千个传感器的读数并将它们存储在 MySQL 数据库中。每秒有数百个插入。为了提高插入性能，我最初将值存储到内存缓冲表中。我每分钟运行一次存储过程，将插入的行从内存缓冲区移动到永久表。

基本上我想在我的存储过程中执行以下操作以从临时缓冲区中移动行：

INSERT INTO data SELECT * FROM data_buffer;
DELETE FROM data_buffer;

不幸的是，前一个不可用，因为数据收集进程在上面的 INSERT 和 DELETE 之间的“data_buffer”中插入了额外的行。因此，这些行将被删除，而不会插入到“数据”表中。

如何使操作原子化或使 DELETE 语句仅删除在前面的语句中 SELECTed 和 INSERTed 的行？

如果可能的话，我更愿意以适用于不同数据库引擎的标准方式执行此操作。

由于性能开销和存储要求，我不想添加任何额外的“id”列。

我希望标准 SQL 中有 SELECT_AND_DELETE 或 MOVE 语句或类似的语句...

Answer 1

我相信这会起作用，但会阻塞直到插入完成

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
INSERT INTO data (SELECT * FROM data_buffer FOR UPDATE); 
DELETE FROM data_buffer; 
COMMIT TRANSACTION;

Answer 2

避免所有这些问题并保持快速的一种可能方法是使用两个 data_buffer 表（我们称它们为 data_buffer1 和 data_buffer2 ）；当集合处理插入data_buffer2时，您可以对data_buffer2执行插入和删除；比您切换时，收集的数据会进入 data_buffer2，而数据会从 data_buffer1 插入和删除到 data。

Answer 3

怎么样有一个行id，在插入之前获取最大值，进行插入然后删除记录<= max(id)

Answer 4

这是与 @ammoQ 的答案类似的解决方案。不同之处在于，您可以在计划过程中透明地交换表，而不是让 INSERTing 进程确定要写入哪个表。

在计划过程中使用 RENAME 来交换表：

CREATE TABLE IF NOT EXISTS data_buffer_new LIKE data_buffer;
RENAME TABLE data_buffer TO data_buffer_old, data_buffer_new TO data_buffer;
INSERT INTO data SELECT * FROM data_buffer_old;
DROP TABLE data_buffer_old;

这是有效的，因为 RENAME 语句会自动交换表，因此 INSERTing 进程不会因“找不到表”而失败。但这是 MySQL 特有的。

Answer 5

我假设这些表是相同的，具有相同的列和主键？如果是这种情况，您可以将 select 嵌套在 where 子句中...如下所示：

DELETE FROM data_buffer 
WHERE primarykey IN (SELECT primarykey FROM data)

Answer 6

这是 MySQL 特定的解决方案。您可以使用锁定来防止 INSERTing 进程在移动行时添加新行。

移动行的过程应如下所示：

LOCK TABLE data_buffer READ;
INSERT INTO data SELECT * FROM data_buffer;
DELETE FROM data_buffer;
UNLOCK TABLE;

在缓冲区中插入新行的代码应更改如下：

LOCK TABLE data_buffer WRITE;
INSERT INTO data_buffer VALUES (1, 2, 3);
UNLOCK TABLE;

当锁到位时，INSERT 过程显然会阻塞。