Sheeri,
Wow. That was my first forum email and I thought it would go unnoticed. I
sure was wrong.
You are exactly right, because apparently with LOAD DATA INFILE, everything
in the file is treated as a string. I'm using PHP to create the text file,
so I tried PHP's pack() function to write '2' as binary data. And...it
worked!
Thanks so much for your input.
~ Julie
----- Original Message -----
From: "sheeri kritzer" <[EMAIL PROTECTED]>
To: "Julie Kelner" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Monday, February 27, 2006 12:50 PM
Subject: Re: LOAD DATA INFILE and BIT columns
Hi Julie,
If you notice after your import, you have 3 warnings. This intrigued
me, so I created a test case (also running 5.0.18 standard):
create table bit_test (b bit(8));
cat /tmp/bit_test.txt
01010101
2
b'010'
b\'010\'
0x2
000000000000000002
mysql> load data infile '/tmp/bit_test.txt' into table bit_test;
Query OK, 6 rows affected, 5 warnings (0.05 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 5
------------------------------------------------------------------------------------------------------------------------
Hrm. I got 5 warnings; you'd only gotten 2. Weird!
------------------------------------------------------------------------------------------------------------------------
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'b' at row 1 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 3 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 4 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 5 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 6 |
+---------+------+-----------------------------------------------------+
5 rows in set (0.02 sec)
------------------------------------------------------------------------------------------------------------------------
What this says to me is that the values were too big, for all but row 2.
------------------------------------------------------------------------------------------------------------------------
mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
+----------+
6 rows in set (0.05 sec)
------------------------------------------------------------------------------------------------------------------------
so the 11111111 values make sense -- the values were larger than the
largest value, so it truncated it to the largest value. But why, when
I insert a 2, does it use 11010 instead of 10?
Let's test:
------------------------------------------------------------------------------------------------------------------------
mysql> insert into bit_test VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
| 10 |
+----------+
7 rows in set (0.00 sec)
------------------------------------------------------------------------------------------------------------------------
That makes sense! the last value is 10, which makes sense for a
binary value of 2. On a hunch, I tried to see what happened if it
treated 2 as a string, not an integer:
------------------------------------------------------------------------------------------------------------------------
mysql> insert into bit_test VALUES ('2');
Query OK, 1 row affected (0.00 sec)
mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
| 10 |
| 110010 |
+----------+
8 rows in set (0.01 sec)
------------------------------------------------------------------------------------------------------------------------
Aha! the culprit -- it was thinking that the "2" in the file was a
string, not an int.
------------------------------------------------------------------------------------------------------------------------
Hope this helped,
-Sheeri
On 2/24/06, Julie Kelner <[EMAIL PROTECTED]> wrote:
Hi. I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE into
tables that have BIT(8) columns. No matter
what format I use, the result is not what I expect (see example below.)
Anyone know how to properly format the data for loading into a BIT column?
Thanks!
$ cat /tmp/bit_test.txt
01010101
2
b'010'
b\'010\'
0x2
000000000000000002
mysql> create table bit_test (b bit(8));
Query OK, 0 rows affected (0.01 sec)
mysql> load data infile '/tmp/bit_test.txt' into table bit_test;
Query OK, 6 rows affected, 3 warnings (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 3
mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 11111111 |
| 110010 |
| 11111111 |
| 11111111 |
| 11111111 |
| 11111111 |
+----------+
6 rows in set (0.00 sec)
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
