更新MySQL数据库时如何避免重复? [英] How to avoid duplicates while updating a MySQL database?

问题描述

我每天都从外部服务器接收MySQL转储文件.sql，但我没有任何控制权.我创建了一个本地数据库以将所有数据存储在.sql文件中.我希望我可以设置一个脚本来每天自动更新本地数据库.我每天收到的sql文件包含本地数据库中已经存在的旧数据.如何避免这些旧数据重复，而仅将新数据插入本地MySQL服务器?非常感谢你！

I'm receiving a MySQL dump file .sql daily from an external server, which I don't have any control of. I created a local database to store all data in the .sql file. I hope I can set up a script to automatically update my local database daily. The sql file I'm receiving daily contains old data that is in the local database already. How can I avoid duplicates of such old data and only insert into the local MySQL server new data? Thank you very much!

推荐答案

您可以在具有适当字段的表上使用PRIMARY KEY或UNIQUE索引来停止重复记录.

You can use a PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records.

  PRIMARY KEY 

使用INSERT IGNORE而不是INSERT.如果一条记录与现有记录不重复，MySQL照常插入它.如果记录是重复记录，则IGNORE关键字告诉MySQL静默丢弃它，而不会产生错误.

Use INSERT IGNORE rather than INSERT. If a record doesn't duplicate an existing record, MySQL inserts it as usual. If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error.

 INSERT IGNORE

这篇关于更新MySQL数据库时如何避免重复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！