首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >CSV文件上传以处理状态更新&插入新记录

问CSV文件上传以处理状态更新&插入新记录
EN

Stack Overflow用户
提问于 2017-08-31 09:37:48
回答 2查看 1.5K关注 0票数 0

在做一个本地托管的项目时,我被困在管理CSV上传上。其中一项任务要求我每天上传具有新条目或现有条目更新状态的数据。也有可能有些条目(存在于数据库中)没有更新状态。

问题陈述;

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

理想情况下,它应按以下方式工作;

  1. 如果不存在条目(从CSV文件插入新条目基数据)
  2. 如果条目存在并且状态与新上传的CSV文件相同(忽略&不做任何事情)
  3. 如果条目存在并具有与新上传的CSV文件中的状态不同的状态(将状态更新为CSV文件中提到的状态)

数据库/ CSV文件结构

  • 自动增量( tracking_id )
  • odanumber (通过CSV上传,可以有重复的条目)
  • 航空运单(透过CSV及UNIQUE上载)
  • 快递(通过CSV上传,可以有重复的条目)
  • delstatus (通过CSV上传&主要是更新的内容)
  • 交付日期(通过CSV上传并随每次交付更新)

从上面,delstatus几乎每次更新(对于现有条目)新的CSV被上传,因此需要检查。

我假设我们可以选择'airwaybill‘来检查它是否存在,如果存在,检查delstatus是否与CSV文件或update相同。如果“空运单”不存在,则必须将新记录添加到数据库中。因为这样可以避免我不必要地输入数据库中的所有记录。或者可以用一种更好的方式来完成(我还没有去探索)。

现在发生了什么;

我能够上传完整的CSV文件集,通过下面的代码在数据库中创建新条目。

代码语言:javascript
复制
<?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并在动态表中更新条目之前进行比较来完成。

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

谢谢你读了这么多。

诚挚的问候,

阿米特·阿格尼霍特里

EN

回答 2

Stack Overflow用户

发布于 2017-09-01 17:40:55

负载数据信息是如何工作的

基于唯一索引,LOAD DATA INFILE插入新记录或更新现有记录(仅当替换选项处于活动状态时)。

(1)关于插入

如果在db表中找不到唯一索引列的csv输入值,则添加一个新记录,并从csv文件中添加(定义的)输入值。

(2)关于更新

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

  • 它将新的csv值插入作为一个新的记录,其中包含一个新的主键id;
  • 它从数据库中删除旧记录。

注:在我的回答的其余部分,我将只讲更新部分(2)。

INSERT-触发器作为条件更新的解决方案

因为LOAD DATA INFILE在删除操作之前运行插入操作,所以当插入带有csv值的新记录时,可以使用旧db记录仍然存在的事实。因此,您可以根据旧记录中包含的值自定义新的输入值。最酷的部分是:您甚至可以维护主键字段的旧值。

关键是在INSERT触发器之前定义一个,其中包含所有所需的自定义、验证和赋值:

  • 通过运行SELECT 语句获取旧记录的值;
  • 将获取的值存储到先前定义的用户变量中;
  • 使用用户变量进行,比较的旧值和csv输入值;
  • 基于这种比较:将主键字段的旧值赋给作为新的值,将新的csv值更改为旧的或其他的(如果需要的话)。

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

密码

创建表语法:

代码语言:javascript
复制
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;

插入前-触发器:

代码语言:javascript
复制
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)

代码语言:javascript
复制
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

加载数据INFILE函数(从PHP调用)

代码语言:javascript
复制
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,可能会遇到以下错误:

错误1290 (HY000):MySQL服务器使用-安全文件-priv选项运行,因此无法执行此语句。

这意味着:没有读取csv文件的权限。因此,您必须自己在数据库的配置文件中设置secure-file-priv (my.cnf或my.ini)。如下所示:

代码语言:javascript
复制
[mysqld]
secure-file-priv = "<PATH-TO-FOLDER-CONTAINING-THE-CSV-FILES>/"

*)无法定义用于运行LOAD数据INFILE的存储过程。

最后,还有涉及临时表的其他解决方案,这些解决方案无疑可以完美地工作。其中之一是在这篇伟大的文章中提出的。因此,触发解决方案只是另一种方法。

祝好运!

票数 1
EN

Stack Overflow用户

发布于 2017-08-31 10:39:39

这里有两种情况:

  1. 表的列与csv列完全匹配。在这种情况下,REPLACE就是答案--它是LOAD DATA INFILE的关键字(参见doc条目 )
  2. 表的列与csv列不匹配:替换将导致相互冲突的记录被删除和重新插入,从而有效地删除附加数据。在这种情况下,LOAD DATA INFILE本身并不有效,您需要另一种方法,在此之前过滤您的文件,通过php或其他方法进行更新。

无论如何,如果您想在导入过程中添加更多的“逻辑”,也许LOAD DATA INFIlE并不是真正正确的方法,但是使用临时表很可能会受益于数据库提供的所有优点。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/45978176

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档