6

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.

Atropo
  • 12,231
  • 6
  • 49
  • 62

4 Answers4

15

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

Ilán
  • 151
  • 1
  • 3
  • 3
    After some experimenting with the same problem it looks like for the bit field the value from the text file is interpreted as "whether there is a value (1) or there isn't (0)". Any value such as 0,1,2134,'True', 'foo', 'bla' is interpreted as (1) – Paceman Mar 09 '15 at 05:06
  • thanks, this is the correct answer especially if your load command is generic. – Kalpesh Popat Jan 22 '23 at 08:57
14

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').

Atropo
  • 12,231
  • 6
  • 49
  • 62
1

You can use BIN() function like this : 

INSERT INTO `table` VALUES (`column` = BIN(1)), (`column` = BIN(0));
JoDev
  • 6,633
  • 1
  • 22
  • 37
0

Let me guess, but I think you should ignore 1st line of your CSV file in LOAD query.

See "IGNORE number LINES"

gaRex
  • 4,144
  • 25
  • 37