5

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';
  • The earlier post seems to have incorrect subject,so i have removed that and posted this one with more elaboration. If you can suggest about the solution it would be helpful. – Nawaz Sohail Oct 21 '14 at 13:44
7

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 !!!

  • It always returns the false condition means 0 even for True fields as well. I executed following statement LOAD DATA LOCAL INFILE 'file1' INTO table n1 FIELDS TERMINATED BY ',' enclosed by '\"' LINES TERMINATED BY '\n' (@var1,c2) SET c1 = IF(@var='True',1,0); – Nawaz Sohail Oct 21 '14 at 13:58
  • I had just removed an extra semicolon. Please try again. – RolandoMySQLDBA Oct 21 '14 at 13:59
  • I executed it after correcting but the problem remains the same. It always returns the false condition values while loading. can you guess why? – Nawaz Sohail Oct 21 '14 at 14:07
  • I got it, please look again !!! – RolandoMySQLDBA Oct 21 '14 at 14:18
  • 1
    Thank you for accepting. This is has been a headache for years (forums.mysql.com/read.php?98,395223,395223) – RolandoMySQLDBA Oct 21 '14 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.