在做一个本地托管的项目时,我被困在管理CSV上传上。其中一项任务要求我每天上传具有新条目或现有条目更新状态的数据。也有可能有些条目(存在于数据库中)没有更新状态。
问题陈述;
我创建了一个CSV上传功能,它将CSV文件上传到特定的位置,并将信息导入指定的表中。我想知道什么是最好的方式验证数据库记录时,我做CSV上传。
理想情况下,它应按以下方式工作;
数据库/ CSV文件结构
从上面,delstatus几乎每次更新(对于现有条目)新的CSV被上传,因此需要检查。
我假设我们可以选择'airwaybill‘来检查它是否存在,如果存在,检查delstatus是否与CSV文件或update相同。如果“空运单”不存在,则必须将新记录添加到数据库中。因为这样可以避免我不必要地输入数据库中的所有记录。或者可以用一种更好的方式来完成(我还没有去探索)。
现在发生了什么;
我能够上传完整的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并在动态表中更新条目之前进行比较来完成。
请提出一个实现结果的最佳方法。
谢谢你读了这么多。
诚挚的问候,
阿米特·阿格尼霍特里
发布于 2017-09-01 17:40:55
负载数据信息是如何工作的
基于唯一索引,LOAD DATA INFILE插入新记录或更新现有记录(仅当替换选项处于活动状态时)。
(1)关于插入
如果在db表中找不到唯一索引列的csv输入值,则添加一个新记录,并从csv文件中添加(定义的)输入值。
(2)关于更新
如果在db表中找到唯一索引列的csv输入值,则LOAD DATA INIFILE查询执行以下操作(按此顺序!):
注:在我的回答的其余部分,我将只讲更新部分(2)。
INSERT-触发器作为条件更新的解决方案
因为LOAD DATA INFILE在删除操作之前运行插入操作,所以当插入带有csv值的新记录时,可以使用旧db记录仍然存在的事实。因此,您可以根据旧记录中包含的值自定义新的输入值。最酷的部分是:您甚至可以维护主键字段的旧值。
关键是在INSERT触发器之前定义一个,其中包含所有所需的自定义、验证和赋值:
然后从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加载数据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,可能会遇到以下错误:
错误1290 (HY000):MySQL服务器使用-安全文件-priv选项运行,因此无法执行此语句。
这意味着:没有读取csv文件的权限。因此,您必须自己在数据库的配置文件中设置secure-file-priv (my.cnf或my.ini)。如下所示:
[mysqld]
secure-file-priv = "<PATH-TO-FOLDER-CONTAINING-THE-CSV-FILES>/"*)无法定义用于运行LOAD数据INFILE的存储过程。
最后,还有涉及临时表的其他解决方案,这些解决方案无疑可以完美地工作。其中之一是在这篇伟大的文章中提出的。因此,触发解决方案只是另一种方法。
祝好运!
发布于 2017-08-31 10:39:39
这里有两种情况:
REPLACE就是答案--它是LOAD DATA INFILE的关键字(参见doc条目 )LOAD DATA INFILE本身并不有效,您需要另一种方法,在此之前过滤您的文件,通过php或其他方法进行更新。无论如何,如果您想在导入过程中添加更多的“逻辑”,也许LOAD DATA INFIlE并不是真正正确的方法,但是使用临时表很可能会受益于数据库提供的所有优点。
https://stackoverflow.com/questions/45978176
复制相似问题