maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

Instead of using b'...' notation, use bitwise math to get the integer
representation of the bitset, and place that in your loader file.

For example:
select 1 | 2 | 8;  ---> 11

Put 11 in your data file and you will get bits 1 2 and 8 set in your bitset.

--Justin

On Mon, Nov 2, 2015 at 4:52 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>
wrote:

> 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
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>