Load data from CSV inside bit field in mysql

Question

What's the right syntax to insert a value inside a column of type bit(1) in `MySQL'?

My column definition is:

payed bit(1) NOT NULL

I'm loading the data from a csv where the data is saved as 0 or 1. I've tried to do the insert using:

b'value' or 0bvalue (example b'1' or 0b1)

As indicated from the manual.

But I keep getting this error:

 Warning | 1264 | Out of range value for column 'payed' at row 1

What's the right way to insert a bit value?

I'm not doing the insert manually but I'm loading the data from a csv (using load data infile) in which the data for the column is 0 or 1.

This is my load query, I've renamed the fields for privacy questions, there's no error in that definition:

load data local infile 'input_data.csv' into table table
fields terminated by ',' lines terminated by '\n'
(id, year, field1, @date2, @date1, field2, field3, field4, field5, field6, payed, field8, field9,   field10, field11, project_id)
set
date1 = str_to_date(@date1, '%a %b %d %x:%x:%x UTC %Y'),
date2 = str_to_date(@date2, '%a %b %d %x:%x:%x UTC %Y');
show warnings;

This is an example row of my CSV:

    200014,2013,0.0,Wed Feb 09 00:00:00 UTC 2014,Thu Feb 28 00:00:00 UTC 2013,2500.0,21,Business,0,,0,40.0,0,PROSPECT,1,200013

Update: I didn't find a solution with the bit, so I've changed the column data type from bit to tinyint to make it work.

Answer 1

I've finally found the solution and I'm posting it here for future reference. I've found help in the mysql load data manual page.

So for test purpose my table structure is:

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| nome   | varchar(45) | YES  |     | NULL    |       |
| valore | bit(1)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

My csv test file is:

1,primo_valore,1
2,secondo_valore,0
3,terzo_valore,1

The query to load the csv into the table is:

 load data infile 'test.csv' into table test
    fields terminated by ',' lines terminated by '\n'
    (id, nome, @valore) set
       valore=cast(@valore as signed);
    show warnings;

As you can see do load the csv you need to do a cast cast(@valore as signed) and in your csv you can use the integer notation 1 or 0 to indicate the bit value. This is because BIT values cannot be loaded using binary notation (for example, b'011010').

Answer 2

Replace the "0" values in the csv by no value at all. That worked for me.