如何交换MySQL表并原子地更改auto_increment？

时间：2022-06-28 09:14:03

I have two tables:

我有两张桌子:

CREATE TABLE table_a (
    id SERIAL
);
CREATE TABLE table_b (
    id SERIAL
);

I want to swap the tables out and set the auto_increment of the new table_a to the MAX(id)+1 of table_b. E.g.

我想交换表并将new table_a的auto_increment设置为table_b的MAX(id)+1。例如。

SELECT @A:=MAX(id) FROM table_a;
SET @qry = CONCAT('ALTER TABLE table_b AUTO_INCREMENT =', @A+1);
PREPARE stmt FROM @qry;
EXECUTE stmt;
RENAME TABLE table_a TO table_b_tmp, table_b TO table_a, table_b_tmp TO table_a;

Unfortunately I can't lock the tables nor do this in a transaction as RENAME TABLE doesn't work on a locked table and ALTER TABLE implicitly commits.

遗憾的是,我无法锁定表,也无法在事务中执行此操作,因为RENAME TABLE无法在锁定的表上运行,并且ALTER TABLE隐式提交。

The only solution I can think of to avoid overlapping ids is to set the auto_increment + 50, but as this process is happening frequently I'd rather not have loads of holes in my ids.

我能想到避免重叠id的唯一解决方案是设置auto_increment + 50,但是由于这个过程经常发生,我宁愿在我的id中没有大量的漏洞。

Any ideas?

1 个解决方案

#1

As long as the server is not on a version of MS Windows and you stick with non-transactional tables, you should be able to use table locks and ALTER TABLE table_name RENAME TO new_table_name statements.

只要服务器不在MS Windows版本上并且您坚持使用非事务性表,您就应该能够使用表锁和ALTER TABLE table_name RENAME TO new_table_name语句。

LOCK TABLES table_a WRITE, table_b WRITE;
SELECT @A:=MAX(id)+1 FROM table_a;
ALTER TABLE table_a RENAME TO table_a_tmp;
LOCK TABLES table_b WRITE, table_a_tmp WRITE;
PREPARE stmt FROM 'ALTER TABLE table_b RENAME TO table_a, AUTO_INCREMENT = ?';
EXECUTE stmt USING @A;
ALTER TABLE table_a_tmp RENAME TO table_b;
UNLOCK TABLES;

The second lock worries me. If there are any other pending sessions, will they get a chance at the tables before the lock? I could also easily be wrong about the above working. I'm basing the above on the pages below about locks, transactions and ALTER statements.

第二把锁让我担心。如果还有其他待定会话,他们是否会在锁定前获得表格?关于上述工作我也很容易出错。我基于以下关于锁,事务和ALTER语句的页面。

My other thought is you could create a MERGE table for inserts, updating the merge method to FIRST or LAST instead of (along with?) swapping the tables. Would that work?

我的另一个想法是你可以为插入创建一个MERGE表,将合并方法更新为FIRST或LAST而不是(连同?)交换表。那会有用吗?

#1

As long as the server is not on a version of MS Windows and you stick with non-transactional tables, you should be able to use table locks and ALTER TABLE table_name RENAME TO new_table_name statements.

只要服务器不在MS Windows版本上并且您坚持使用非事务性表,您就应该能够使用表锁和ALTER TABLE table_name RENAME TO new_table_name语句。

LOCK TABLES table_a WRITE, table_b WRITE;
SELECT @A:=MAX(id)+1 FROM table_a;
ALTER TABLE table_a RENAME TO table_a_tmp;
LOCK TABLES table_b WRITE, table_a_tmp WRITE;
PREPARE stmt FROM 'ALTER TABLE table_b RENAME TO table_a, AUTO_INCREMENT = ?';
EXECUTE stmt USING @A;
ALTER TABLE table_a_tmp RENAME TO table_b;
UNLOCK TABLES;

The second lock worries me. If there are any other pending sessions, will they get a chance at the tables before the lock? I could also easily be wrong about the above working. I'm basing the above on the pages below about locks, transactions and ALTER statements.

第二把锁让我担心。如果还有其他待定会话,他们是否会在锁定前获得表格?关于上述工作我也很容易出错。我基于以下关于锁,事务和ALTER语句的页面。

My other thought is you could create a MERGE table for inserts, updating the merge method to FIRST or LAST instead of (along with?) swapping the tables. Would that work?

我的另一个想法是你可以为插入创建一个MERGE表,将合并方法更新为FIRST或LAST而不是(连同?)交换表。那会有用吗?

标签：

相关文章