maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

sorry found at mysql docs

BIT <https://dev.mysql.com/doc/refman/5.7/en/bit-type.html> values cannot
be loaded using binary notation (for example, b'011010'). To work around
this, specify the values as regular integers and use the SET clause to
convert them so that MySQL performs a numeric type conversion and loads
them into the BIT
<https://dev.mysql.com/doc/refman/5.7/en/bit-type.html> column
properly:

 <https://dev.mysql.com/doc/refman/5.7/en/load-data.html>
shell> *cat /tmp/bit_test.txt*
2
127
shell> *mysql test*
mysql> *LOAD DATA INFILE '/tmp/bit_test.txt'*
    -> *INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);*
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0


but i'm checking something very bad
if i execute without "SET" a big file take 1second, with "SET" it takes 3
times more
maybe we could include the bit cast at load data infile and solve this
problem? maybe a mariadb improvement?


2015-11-02 21:43 GMT-02:00 Roberto Spadim <roberto@xxxxxxxxxxxxx>:

> hi guys, how could i send bit data to mysql with LOAD FILE?
>
> a table example:
> create table t (i bit not null default 0);
>
> LOAD DATA LOCAL INFILE "file.csv" IGNORE INTO TABLE `t` FIELDS TERMINATED
> BY ';' LINES TERMINATED BY '\n' (i)
>
>
> the data file is simple:
>
> 0
> 1
> 0
> 1
> 0
> 1
>
> but i got Warning 1024: Out of range value for column 'i' at row 1 ... to
> last row
>
> any idea? i tried
> b'0'
> b'1'
> '0'
> '1'
> 0
> 1
> but no one work, i don't know what happen but it's cast always to 1
>  (maybe convert from string to binary?)
>



-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle