mysql 复制记录到另一个表_MySQL有效地将所有记录从一个表复制到另一个表

bd96500e110b49cbb3cd949968f18be7.png

Is there a more-efficent, less laborious way of copying all records from one table to another that doing this:

INSERT INTO product_backup SELECT * FROM product

Typically, the product table will hold around 50,000 records. Both tables are identical in structure and have 31 columns in them. I'd like to point out this is not my database design, I have inherited a legacy system.

解决方案

There's just one thing you're missing. Especially, if you're using InnoDB, is you want to explicitly add an ORDER BY clause in your SELECT statement to ensure you're inserting rows in primary key (clustered index) order:

INSERT INTO product_backup SELECT * FROM product ORDER BY product_id

Consider removing secondary indexes on the backup table if they're not needed. This will also save some load on the server.

Finally, if you are using InnoDB, reduce the number of row locks that are required and just explicitly lock both tables:

LOCK TABLES product_backup WRITE;

LOCK TABLES product READ;

INSERT INTO product_backup SELECT * FROM product ORDER BY product_id;

UNLOCK TABLES;

The locking stuff probably won't make a huge difference, as row locking is very fast (though not as fast as table locks), but since you asked.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值