如何将日期字符串数据的数据加载到MySQL数据库中？
这是我的.csv文件的一瞥：
Name,Start Date,End Date,Budget
Class 1,1/1/2020,31/1/2020,"USD10,000.00"
Class 2,1/2/2020,28/2/2020,"USD14,000.00"
CSV中的日期格式是％e/％c/％y。
我正在尝试将此数据加载到表class，这是我所做的：
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/class.csv'
INTO TABLE class
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(class_name, @start_date, @end_date, @budget)
SET start_date = REPLACE(@start_date, '/', '-'),
end_date = REPLACE(@start_date, '/', '-'),
budget = REPLACE(@budget, 'USD', ''),
budget = REPLACE(@budget, ',', '');
但是它返回
错误代码：1292。 不正确的日期值：'1-1-2021'for column'start_date在第1行
如何处理？
Here is a glimpse of my .csv file:
Name,Start Date,End Date,Budget
Class 1,1/1/2020,31/1/2020,"USD10,000.00"
Class 2,1/2/2020,28/2/2020,"USD14,000.00"
The date format in csv is %e/%c/%Y.
I am trying to load this data to a table class, here is what I have done:
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/class.csv'
INTO TABLE class
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(class_name, @start_date, @end_date, @budget)
SET start_date = REPLACE(@start_date, '/', '-'),
end_date = REPLACE(@start_date, '/', '-'),
budget = REPLACE(@budget, 'USD', ''),
budget = REPLACE(@budget, ',', '');
But it returns
Error Code: 1292. Incorrect date value: '1-1-2021' for column 'start_date' at row 1
How to handle this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
似乎您需要使用
str_to_date（）诸如start_date之类的函数是 date 类型列，而不是普通字符串。据推测，相同的逻辑也应适用于
end_date。Seems you need to use
STR_TO_DATE()function such asas
start_dateis a DATE type column, but not an ordinary string. Presumably, the same logic should work also forend_date.