csv MySQL SQL错误[1411] [HY000]：日期时间值不正确：“”用于函数str_to_date

mlmc2os5 于 2023-01-18 发布在 Mysql

关注(0)|答案(1)|浏览(209)

我正在尝试从csv导入数据，我的日期数据类型在csv文件中保存为% d- % b- % y（例如，2020年8月12日）
该表

create table shows(
ShowID int unique,
Title varchar(255),
TypeID int,
Director varchar(255),
Cast blob,
DateAdded date,
year year,
Violence varchar(255),
Duration varchar(255),
Description blob

);

我试着运行这个脚本来填充：

INTO TABLE shows
FIELDS TERMINATED BY ',' 
optionally enclosed by '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(@ShowID,@type1,shows.Title,shows.Director,shows.`Cast`,@country,@date_added,shows.`year`,shows.Violence,shows.Duration,@c,shows.Description)
SET shows.ShowID =@ShowID ,
shows.TypeID = (select TypeID from typecountry t where t.`Type`=@type1 and t.Country=@country),
shows.DateAdded= STR_TO_DATE(@date_added , '%e-%b-%y');

显示以下错误：
SQL错误[1411][HY000]：日期时间值不正确：""用于函数str_to_date

csv

来源：https://stackoverflow.com/questions/75140834/mysql-sql-error-1411-hy000-incorrect-datetime-value-for-function-str-to

1条答案

按热度按时间

mu0hgdu01#

表中的值为空，因此需要替换它们

LOAD DATA INFILE 'c:/tmp/myfile.csv' 
INTO TABLE shows
FIELDS TERMINATED BY ',' 
optionally enclosed by '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(@ShowID,@type1,shows.Title,shows.Director,shows.`Cast`,@country,@date_added,shows.`year`,shows.Violence,shows.Duration,@c,shows.Description)
SET shows.ShowID =@ShowID ,
shows.TypeID = (select TypeID from typecountry t where t.`Type`=@type1 and t.Country=@country),
shows.DateAdded= STR_TO_DATE(IF (@date_added = '','01-01-1999', @date_added) , '%e-%b-%y');

赞(0）回复(0）举报 2023-01-18

我来回答

csv MySQL SQL错误[1411] [HY000]：日期时间值不正确：“”用于函数str_to_date

1条答案

相关问题

热门标签

最新问答