如何在使用LOAD DATA INFILE时避免重复数据

问题描述

这是我另一个问题的延续。

This is a continuation on my other question link.

我有一个这样的表结构：

I have a table structure like this:

id
assigned_by
assigned_to_user_id
vendor_id
receiver_no
purchase_order_no
destination
po_status
invoice_no
invoice_amount
delivery_date
datetime_done
latest_jda_sync_date
latest_mobile_sync_date
created_at
updated_at
deleted_at

我的csv文件的内容是这样的：

And the content of my csv file is like this:

vendor_id receiver_no purchase_order_no destination po_status
30105   20110   10151   9005    3
50015   20114   10155   9005    3

我的问题是有一个轮询/守护进程，它提取数据并将其插入到csv文件中。所以我知道肯定，我会得到重复的数据每csv。我想避免这样。

My problem here is that I have a polling/daemon process which pull data and insert them in a csv file. So I know for sure that I'll be getting duplicate data per csv. And I want to avoid that.

我读过，使用IGNORE可以在插入数据之前进行验证，但我的问题是我的主键不是'id'，内容I' m拉和存储在csv文件中没有字段id。

I'd read that using IGNORE can do this validation before my data gets inserted but my problem is that my primary key isn't 'id' and the content I'm pulling and storing in the csv file doesn't have the field 'id' in it.

我的csv中的唯一字段是'receiver_no'或'purchase_order_no'。那么如何设置IGNORE命令到po_no或receiver_no。这可能吗？

The unique field in my csv is the 'receiver_no' or 'purchase_order_no'. So how do I set the IGNORE command to the po_no or receiver_no. Is this possible?

感谢，

推荐答案

1. p>在您的表中定义一个合适的 UNIQUE 键：

ALTER TABLE my_table ADD UNIQUE KEY (receiver_no)

添加 IGNORE （保留现有数据）或 REPLACE （删除记录并替换为新数据） LOAD DATA INFILE 命令。

Add either IGNORE (to keep the existing data) or REPLACE (to delete the record and replace with the new data) after the filename to your LOAD DATA INFILE command.

这篇关于如何在使用LOAD DATA INFILE时避免重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！