php - 上传 CSV 文件以处理状态更新和插入新记录

Question

在处理本地托管的项目时，我一直在管理 CSV 上传。其中一项任务要求我每天上传包含新条目或现有条目更新状态的数据。某些条目(存在于数据库中)也有可能没有更新状态。

问题陈述；

我创建了一个 CSV 上传功能，可将 CSV 文件上传到特定位置并导入指定表中的信息。我想知道在上传 CSV 时验证数据库记录的最佳方法是什么。

理想情况下，它应该按如下方式工作；

1. 如果条目不存在(插入来自 CSV 文件的新条目基础数据)
2. 如果条目存在且状态与新上传的 CSV 文件相同(忽略且不执行任何操作)
3. 如果条目存在且状态与新上传的 CSV 文件中的状态不同(更新状态为 CSV 文件中提到的内容)

<小时/>

数据库/CSV文件结构

• tracking_id(自动递增)
• odanumber(通过 CSV 上传且可以有重复条目)
• 空运提单(通过 CSV 和 UNIQUE 上传)
• express (通过 CSV 上传且可以有重复条目)
• delstatus(通过 CSV 上传且主要更新)
• 交货日期(通过 CSV 上传并在每次交货时更新)

从上面来看，delstatus 几乎每次(对于现有条目)上传新的 CSV 时都会更新，因此需要进行检查。

I assume that we can pick 'airwaybill' to check if it exists, andif it does, check if the delstatus is same as of CSV file orupdate. If 'airwaybill' doesn't exist then a new records must be addedto the database. As that would save me from entering all records indatabase unnecessarily. Or can be done may be in a better way (thatI'm yet to explore).

<小时/>

现在发生了什么；

我可以上传完整的 CSV 文件集，通过以下代码在数据库中创建新条目。

<?php 

if(isset($_POST['csv']))
{
$sqlname= 'localhost';
$username= 'root';
$table= 'tracking';
$password= '';
$db='aatrack';
$file=$_POST['csv'];
$cons= mysqli_connect("$sqlname", "$username","$password","$db") or die(mysql_error());

$result1=mysqli_query($cons,"select count(*) count from $table");
$r1=mysqli_fetch_array($result1);
$count1=(int)$r1['count'];


mysqli_query($cons, '
    LOAD DATA LOCAL INFILE "'.$file.'"
        INTO TABLE '.$table.'
        FIELDS TERMINATED by \',\'
        LINES TERMINATED BY \'\n\'
        IGNORE 1 LINES
')or die(mysql_error());

$result2=mysqli_query($cons,"select count(*) count from $table");
$r2=mysqli_fetch_array($result2);
$count2=(int)$r2['count'];

$count=$count2-$count1;
if($count>0)
{
    header("location:success.php?id=$count");
}

}

?>

您能否帮助指导实现这一目标的最佳方法。据我所知，可以通过首先将信息上传到 temp_table 并在 LIVE 表中更新条目之前比较相同的信息来完成。

请提出实现结果的最佳方法。

感谢您阅读本文。

最诚挚的问候，

阿米特·阿格尼霍特里

Answer 1

LOAD DATA INFILE 的工作原理

基于 UNIQUE 索引，LOAD DATA INFILE 插入一条新记录或更新现有记录(仅当 REPLACE 选项处于事件状态时)。

(1)关于插入:

如果在数据库表中找不到 UNIQUE 索引列的 csv 输入值，则会使用 csv 文件中的(定义的)输入值添加新记录。

(2)关于更新:

如果在 db 表中找到 UNIQUE 索引列的 csv 输入值，则 LOAD DATA INIFILE 查询将执行以下操作(按此顺序!):

• 它插入新的 csv 值作为具有新主键 ID 的新记录；
• 它从数据库中删除旧记录。

注意:在我的回答的其余部分中，我将仅讨论更新部分(2)。

BEFORE INSERT-TRIGGER 作为条件更新的解决方案

由于 LOAD DATA INFILE 在删除操作之前运行插入操作，因此您可以利用以下事实:插入具有 csv 值的新记录时，旧数据库记录仍然存在。因此，您可以根据旧记录中包含的值自定义新的输入值。真正酷的部分是:您甚至可以维护 PRIMARY KEY 字段的旧值。

关键是定义一个BEFORE INSERT-TRIGGER，其中包含所有需要的自定义、验证和分配:

• 通过运行 SELECT sql 语句来获取旧记录的值；
• 将获取的值存储到先前定义的用户变量中；
• 使用用户变量比较旧值与 csv 输入值；
• 基于此比较:指定主键字段的旧值作为新值，并将新 csv 值更改为旧值或其他值(如果)也需要。

然后从 PHP 执行 LOAD DATA INFILE 查询。

代码

创建表语法:

CREATE TABLE `tracking` (
  `tracking_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `odanumber` int(11) DEFAULT NULL,
  `airwaybill` int(11) DEFAULT NULL,
  `courierful` varchar(100) DEFAULT NULL,
  `delstatus` tinyint(1) DEFAULT NULL,
  `deliverydate` varchar(19) DEFAULT NULL,
  PRIMARY KEY (`tracking_id`),
  UNIQUE KEY `uni_airwaybill` (`airwaybill`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

插入触发之前:

USE `tests`;

DELIMITER $$

DROP TRIGGER IF EXISTS tests.tracking_BEFORE_INSERT$$
USE `tests`$$
CREATE DEFINER = CURRENT_USER TRIGGER `tests`.`tracking_BEFORE_INSERT` BEFORE INSERT ON `tracking` FOR EACH ROW
BEGIN

    /* Define vars to store old record values. */
    SET @old_tracking_id = NULL;
    SET @old_odanumber = NULL;
    SET @old_courierful = NULL;
    SET @old_delstatus = NULL;
    SET @old_deliverydate = NULL;

    /* 
        Fetch the existing record if exists and pass 
        its values into the correspnding vars.
    */
    SELECT 
        tracking_id,
        odanumber,
        courierful,
        delstatus,
        deliverydate 
    INTO 
        @old_tracking_id,
        @old_odanumber,
        @old_courierful,
        @old_delstatus,
        @old_deliverydate 
    FROM tracking 
    WHERE airwaybill = NEW.airwaybill
    LIMIT 1;

    /* If an old record was found... */
    IF @old_tracking_id IS NOT NULL THEN

        /* ...set the new record's tracking_id to it. */
        SET NEW.tracking_id = @old_tracking_id;

        /* ...and if delstatus are the same... */
        IF NEW.delstatus = @old_delstatus THEN

            /* ...maintain the old record values. */
            SET NEW.odanumber = @old_odanumber;
            SET NEW.courierful = @old_courierful;
            SET NEW.deliverydate = @old_deliverydate;

        END IF;

    END IF;

END$$
DELIMITER ;

CSV 文件(tracking.csv)

odanumber,airwaybill,"courierful",delstatus,"deliverydate"
19,1,abc,0,2017-04-31
25,2,def,1,2017-05-31
103,3,ghi,1,2017-06-31
324,4,jkl,1,2017-07-31
564,5,mno,0,2017-08-31

LOAD DATA INFILE 函数(从 PHP 调用)

LOAD DATA INFILE "<PATH-TO>/tracking.csv"
REPLACE
INTO TABLE tests.tracking
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(odanumber, airwaybill, courierful, delstatus, deliverydate);

<小时/>

注释:

*) 关于 LOAD DATA INFILE，您可能会遇到错误:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

意思是:LOAD DATA INFILE没有读取csv文件的权限。因此，您必须自己在数据库的配置文件(my.cnf 或 my.ini)中设置secure-file-priv。像这样:

[mysqld]
secure-file-priv = "<PATH-TO-FOLDER-CONTAINING-THE-CSV-FILES>/"

*) 您无法定义从中运行 LOAD DATA INFILE 的存储过程。

最后，还有其他涉及临时表的解决方案，毫无疑问，它们可以完美地工作。其中之一出现在this great article中。 。所以，触发解决方案只是另一种方法。

祝你好运!