Re: [Maria-discuss] LOAD FILE and BIT datatype

probably something here: https://github.com/MariaDB/server/blob/10.1/sql/field.cc#L9127 2015-11-02 22:28 GMT-02:00 Roberto Spadim :

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/e19a6f3dca7509eb8e042796a9311dba937ad... that's something with field->store (i think) field->store((char*) pos,length,read_info.read_charset);

https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad... 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 :

...

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 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 :

...

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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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

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