Joe Mornin Joe Mornin - 1 year ago 135
SQL Question

Default value for empty integer fields when importing CSV data in MySQL

I'm importing a CSV into a MySQL table with

. 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
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?

Answer Source

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 conditionally using the user variable.

You could use this to set the value to whatever you want (NULL, 0, etc.).

Here's an example, assuming you want to set it to 0:

INTO TABLE your_table
(column_one,..., @contributor_zipcode,..., column_n)
SET contributor_zipcode = IF(@contributor_zipcode='',0,@contributor_zipcode);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download