我有一个csv文件，它是通过导出一个sqlserver表创建的，我正在尝试将它导入一个mysql表。下面是导出文件的前10行
"ID","EVENT","DATE_TIME","IP_ADDRESS","USER_ID","ITEM_ID","SESSION_ID"
5742,"browse protocol",14-JUL-09 09.15.17.000000000 AM,"10.128.101.13","","1",""
5743,"browse protocol",14-JUL-09 09.31.37.000000000 AM,"74.181.121.77","","37",""
5744,"browse measure",14-JUL-09 10.34.35.000000000 AM,"99.20.254.1","","25",""
5745,"browse domain",14-JUL-09 10.39.12.000000000 AM,"65.55.104.26","","1",""
5746,"browse measure",14-JUL-09 12.45.35.000000000 PM,"75.91.11.40","","19",""
5747,"browse protocol",14-JUL-09 01.14.50.000000000 PM,"66.249.67.181","","41",""
5748,"cart add measures",14-JUL-09 02.03.20.000000000 PM,"65.55.211.12","","21,23",""
5749,"browse protocol",14-JUL-09 02.30.24.000000000 PM,"66.249.67.181","","41",""
5750,"browse domain",14-JUL-09 03.43.47.000000000 PM,"10.128.101.13","","1",""
5751,"browse measure",14-JUL-09 03.43.48.000000000 PM,"10.128.101.13","","11",""mysql表要求日期时间的格式如下 YYYY-MM-DD HH:MM:SS .
我有下面的代码可以正确加载除日期之外的所有内容。我错过了什么？
load data local infile 'C:/Users/sh/Desktop/site_metric_test.csv' into table site_metrics
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
(id, event, @var1, ip_address, user_id, item_id, session_id)
SET date_time = STR_TO_DATE(@var1,'%Y-%m-%d');
1条答案
按热度按时间gupuwyp21#
这似乎解决了问题：
SET date_time = DATE_FORMAT(STR_TO_DATE(@var1, '%d-%b-%Y'), '%Y-%m-%d')```load data local infile 'C:/Users/sh/Desktop/export_head.csv' into table site_metrics
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
(id, event, @var1, ip_address, user_id, item_id, session_id)
SET date_time = DATE_FORMAT(STR_TO_DATE(@var1, '%d-%b-%Y'), '%Y-%m-%d');