tested with binary data 0x00 and 0x01 return ALWAYS bit=1 hehe load data have a documented bug with bit type :P i will test BIT with length 64 2015-11-03 0:42 GMT-02:00 Justin Swanhart <greenlion@gmail.com>:
Yes, \0 is ascii zero, but it must be converting the string \0 to the int. It doesn't do any escape conversion, which is kind of idiotic. :D
I don't know what the LOAD DATA delimiter parser will do with a null character in the input. It might fail.
--Justin
On Mon, Nov 2, 2015 at 7:39 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
character ascii 0 ? \0 \1 b'0' b'1' 0 1 don't work
i will test binary data
2015-11-03 0:26 GMT-02:00 Justin Swanhart <greenlion@gmail.com>:
Try \0 and 1 if you have only one bit to set.
On Mon, Nov 2, 2015 at 5:56 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
maybe enum_field_types type() const { return MYSQL_TYPE_BIT; } if(field->type()==MYSQL_TYPE_BIT){ // convert string to unsigned long long }else{ // use normal field->store() }
must try, but i didn't found how to store() with the "b'0'" representation or someting like it
2015-11-02 22:45 GMT-02:00 Roberto Spadim <roberto@spadim.com.br>:
maybe check if it's a BIT type at sql_load.cc and cast to bigint unsigned before field->store ? i think it's the easier patch, i don't know i tryed any kind of file the bit column only with with "SET bit_column=@some_variable+0" or anything to cast from "string" to "integers"
2015-11-02 22:40 GMT-02:00 Roberto Spadim <roberto@spadim.com.br>:
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 <roberto@spadim.com.br>:
> 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 <greenlion@gmail.com>: > >> 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@spadim.com.br> 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@spadim.com.br>: >>> >>>> 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
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle