加载数据infle str\u to \u date转换

ht4b089n  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(268)

我有一个csv文件,它是通过导出一个sqlserver表创建的,我正在尝试将它导入一个mysql表。下面是导出文件的前10行

  1. "ID","EVENT","DATE_TIME","IP_ADDRESS","USER_ID","ITEM_ID","SESSION_ID"
  2. 5742,"browse protocol",14-JUL-09 09.15.17.000000000 AM,"10.128.101.13","","1",""
  3. 5743,"browse protocol",14-JUL-09 09.31.37.000000000 AM,"74.181.121.77","","37",""
  4. 5744,"browse measure",14-JUL-09 10.34.35.000000000 AM,"99.20.254.1","","25",""
  5. 5745,"browse domain",14-JUL-09 10.39.12.000000000 AM,"65.55.104.26","","1",""
  6. 5746,"browse measure",14-JUL-09 12.45.35.000000000 PM,"75.91.11.40","","19",""
  7. 5747,"browse protocol",14-JUL-09 01.14.50.000000000 PM,"66.249.67.181","","41",""
  8. 5748,"cart add measures",14-JUL-09 02.03.20.000000000 PM,"65.55.211.12","","21,23",""
  9. 5749,"browse protocol",14-JUL-09 02.30.24.000000000 PM,"66.249.67.181","","41",""
  10. 5750,"browse domain",14-JUL-09 03.43.47.000000000 PM,"10.128.101.13","","1",""
  11. 5751,"browse measure",14-JUL-09 03.43.48.000000000 PM,"10.128.101.13","","11",""

mysql表要求日期时间的格式如下 YYYY-MM-DD HH:MM:SS .
我有下面的代码可以正确加载除日期之外的所有内容。我错过了什么?

  1. load data local infile 'C:/Users/sh/Desktop/site_metric_test.csv' into table site_metrics
  2. FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
  3. (id, event, @var1, ip_address, user_id, item_id, session_id)
  4. SET date_time = STR_TO_DATE(@var1,'%Y-%m-%d');
e4eetjau

e4eetjau1#

这似乎解决了问题: 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');

相关问题