使用LOAD DATA INFILE作为日期格式的多个SET字段 [英] Multiple SET fields using LOAD DATA INFILE for Date Format
问题描述
我能够使用LOAD DATA INFILE成功获取我的csv文件.我可以跳过所有不需要的数据/列(
I am able to use LOAD DATA INFILE to successfully get my csv file uploaded. I am able to skip all of the data/columns that I don't need (Manual 13.2.6)
我能够使用SET命令设置日期格式.我的问题是，如果我有多个日期"字段怎么办，当我拥有超过1个SET时，SET命令与上面的命令结合使用的任何变体都无法正常工作.
I was able to use the SET command to set the date format. My question is, What if I have multiple Date fields, any variation of the SET command combined with the above doesn't want to work when I have more than 1 SET.
<?php
if (isset($_POST['load']))
{
include '_inc/include.php';
$temp = $_FILES['myfile']['tmp_name'];
$sqlstatement="LOAD DATA LOCAL INFILE '$temp' INTO TABLE 000_1616 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (id, @somedate, name, anotherdate, color, 1moredate) SET sdate = IF(LENGTH(@somedate)=7,STR_TO_DATE(@somedate,'%m/%d/%Y'),STR_TO_DATE(@somedate,'%m/%d/%y'))";
mysql_query($sqlstatement) or die(mysql_error());
echo "It worked";
echo "<p><a href='upload-display.php'>go to page</a></p>";
}
?>
<form method="post" action="upload.php" enctype="multipart/form-data">
<input name="myfile" type="file" />
<input name="load" type="submit" value="submit" /></form>
我的SET像这样
SET sdate = IF(LENGTH(@somedate)=7,STR_TO_DATE(@somedate,'%m/%d/%Y'),STR_TO_DATE(@somedate,'%m/%d/%y'))";
或
$sqlstatement="LOAD DATA LOCAL INFILE '$temp' INTO TABLE 000_1616 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (id, @somedate, name, anotherdate, color, 1moredate) SET sdate = IF(LENGTH(@somedate)=7,STR_TO_DATE(@somedate,'%m/%d/%Y'),STR_TO_DATE(@somedate,'%m/%d/%y'))";
我在第一个日期字段中使用了@somedate，它是真正的日期.只要我不尝试使用SET再添加4个日期字段，此方法就可以很好地工作.我想添加@anotherdate(anotherdate)和@ 1moredate(1moredate)是否必须设置一个数组?我需要在phpMyADMIN端的表中做些什么吗?现在，它们的格式设置为日期"，但除了第一个提交的日期，它们都返回000-00-00.
I used @somedate for the first date field Which is really sdate. This works well so long as I don't try to add 4 more date fields using SET. I would like to add @anotherdate (anotherdate) and @1moredate(1moredate) Do I have to set up an array? Do I need to do something in the Table on the phpMyADMIN side? Right now they are formatted to Date but they all come back 000-00-00 except for the first date filed.
这是SQL:
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `database_track`
--
-- --------------------------------------------------------
--
-- Table structure for table `000_datetest`
--
CREATE TABLE IF NOT EXISTS `000_datetest` (
`id` int(11) NOT NULL auto_increment,
`sdate` date default NULL,
`name` varchar(100) collate utf8_unicode_ci default NULL,
`anotherdate` date NOT NULL,
`color` varchar(50) collate utf8_unicode_ci NOT NULL,
`1moredate` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
csv示例:
+----+---------------+-------------------+---------------------+------------+-------------------+
| id | sdate | name | anotherdate | color | 1moredate |
+----+---------------+------------------------+---------------+------------+-------------------+
| 1 | 2011-08-21 | Tom Thumb | 08/16/2010 | Blue | 1/5/08 |
+----+---------------+------------------------+---------------+------------+-------------------+
| 1 | 2009-05-12 | Don Duck | 03/22/2012 | Yellow | 9/15/03 |
+----+---------------+------------------------+---------------+------------+-------------------+
1 row in set (0.00 sec)
我希望这会有所帮助，请在下面的评论中查看物理文件
I hope this helps, Please see physical file in comment below
推荐答案
要将多个SET命令与LOAD DATA INFILE一起使用，您只需指定一次SET，并用逗号分隔每个变量定义即可.
To use multiple SET commands with LOAD DATA INFILE you only need to specify SET once and separate each variable definition with commas eg.
LOAD DATA INFILE 'my_file' INTO TABLE 'my_table' (column1, @date1, @date2)
SET column2 = STR_TO_DATE(@date1, '%d/%m/%Y %H:%i:%s'),
column3 = STR_TO_DATE(@date2, '%d/%m/%Y %H:%i:%s');
这篇关于使用LOAD DATA INFILE作为日期格式的多个SET字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
