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]
