mysql中导入数据列有空值,在MySQL中导入CSV数据时，空整数字段的默认值

最新推荐文章于 2022-08-25 19:24:47 发布

西西喵

最新推荐文章于 2022-08-25 19:24:47 发布

阅读量342

点赞数

文章标签： mysql中导入数据列有空值

在使用LOAD DATA INFILE导入CSV到MySQL表时遇到空整数字段问题，MySQL会抛出错误警告。解决方法是利用用户变量，条件设置列值，将空字符串转为0或其他默认值。

摘要由CSDN通过智能技术生成

I'm importing a CSV into a MySQL table with LOAD DATA INFILE. One of the table's fields stores zip code data, which I've defined in the table structure contributor_zipcode INT.

In the CSV, this field is sometimes empty. When I execute the LOAD query, MySQL will throw a warning like:

Incorrect integer value: '' for column 'contributor_zipcode' at row 180

I've tried redefining the field as contributor_zipcode INT DEFAULT '0', which creates the same warning, and contributor_zipcode INT DEFAULT NULL, which MySQL won't allow. Any advice?

解决方案

The empty values are being interpreted as the empty string (''), not NULL, so the default value is not being used.

If you want to explicitly control the handling of these empty strings, the best thing to do is to load them into a user variable, and then set the column