Re: LOAD DATA INFILE and BIT columns
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:
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
02
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) |
+--+
| |
| 110010 |
| |
| |
| |
| |
+--+
6 rows in set (0.05 sec)
so the 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) |
+--+
| |
| 110010 |
| |
| |
| |
| |
| 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) |
+--+
| |
| 110010 |
| |
| |
| |
| |
| 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
02
mysql> create table bit_test (b bit(8));
Query OK, 0 row
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
02
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) |
+--+
| |
| 110010 |
| |
| |
| |
| |
+--+
6 rows in set (0.05 sec)
so the 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) |
+--+
| |
| 110010 |
| |
| |
| |
| |
| 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) |
+--+
| |
| 110010 |
| |
| |
| |
| |
| 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
> 02
>
>
> 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) |
> +--+
> | |
> | 110010 |
> | |
> | |
> | |
> | |
> +--+
> 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]
LOAD DATA INFILE and BIT columns
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 02 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) | +--+ | | | 110010 | | | | | | | | | +--+ 6 rows in set (0.00 sec) Thanks!
