Mysql – Converting ‘False'(string) to False(boolean) During LOAD DATA LOCAL INFILE

MySQL

In MySQL is there anyway of loading "False" into a boolean column.

While loading data in table having boolean column and where data is in "True" or "False", it gives warning of:

"Incorrect integer value: 'False' for column 'c1' at row 1".

Table structure is as follow

Create Table: CREATE TABLE `n1` (
  `c1` boolean ,
  `c2` int
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I am using load data statement to load from file "file1" which is comma separated and enclosed in double quotes. Following is the data sample

"False","1" 
"True","2"

The load data local infile statements is as below

load data local infile 'file1' 
into table n1 
  FIELDS TERMINATED BY ',' 
  enclosed by '\"' LINES 
  TERMINATED BY '\r\n';

Best Answer

Convert the first value using user variables to load the True/False values.

Then, compare it to the value 'True'

PROPOSED SOLUTION

LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n' (@var1,c2)
SET c1 = (@var1 = 'True');

SAMPLE DATA

C:\bin>dir
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\bin

10/21/2014  10:01 AM    <DIR>          .
10/21/2014  10:01 AM    <DIR>          ..
10/21/2014  10:01 AM                23 input.txt
               1 File(s)             23 bytes
               2 Dir(s)  685,548,244,992 bytes free

C:\bin>type input.txt
"False","1"
"True","2"
C:\bin>

CODE FOR PROPOSED SOLUTION

USE test
DROP TABLE IF EXISTS n1;
CREATE TABLE `n1` (
  `c1` boolean ,
  `c2` int
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n' (@var1,c2)
SET c1 = (@var1 = 'True');
SELECT * FROM n1;

CODE FOR PROPOSED SOLUTION EXECUTED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS n1;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE `n1` (
    ->   `c1` boolean ,
    ->   `c2` int
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.38 sec)

mysql> LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
    -> LINES TERMINATED BY '\r\n' (@var1,c2)
    -> SET c1 = (@var1 = 'True');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM n1;
+------+------+
| c1   | c2   |
+------+------+
|    0 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Related Question