MySQL LOAD DATA Trick

最新推荐文章于 2025-12-10 22:18:24 发布

转载最新推荐文章于 2025-12-10 22:18:24 发布 · 740 阅读

文章标签：

#mysql #date #table #function #file #im

本文介绍了一种使用MySQL的LOAD DATA INFILE命令加载外部数据源时遇到日期格式不匹配问题的解决方案。通过将文件中不符合MySQL标准的日期格式转换为目标格式，确保了数据正确导入。

I leaned a new trick today with LOAD DATA INFILE. I’m migrating some data from an external source, and the Date Format is not the MySQL required YYYY-MM-DD, it was DD-MMM-YY. So how do you load this into a Date Field.

$ echo "02-FEB-07" > /tmp/t1.psv $ mysql -umysql USE test; DROP TABLE IF EXISTS t1; CREATE TABLE t1(d1 DATE); # echo "02-FEB-07" > /tmp/t1.psv LOAD DATA LOCAL INFILE '/tmp/t1.psv' INTO TABLE t1 (@var1) SET d1=STR_TO_DATE(@var1,'%d-%M-%y'); SELECT * FROM t1; EXIT

The trick is to bind the appropriate column within the file being loaded to a variable, @var1 in my example and use the SET syntax to perform a function on the variable. Rather cool.

原文地址:http://blog.arabx.com.au/