maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

hi justing, but i always get 1
even when data file have "0" (character "0") at line

i'm reading the load data file:
https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L853
that's something with field->store (i think)
field->store((char*) pos,length,read_info.read_charset);
https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L1025
field->store((char*) pos, length, read_info.read_charset);i didn't checked
yet store of bit field type






2015-11-02 22:15 GMT-02:00 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
>>
>>
>


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