MySQL bit values export DML issue

Question

I have created a table in MySQL as

create table bittest (id int(11),constant bit(1) default b'0' );

Then,

insert into bittest(id) values (1);

select query get printed as

+------+----------+
| id   | constant |
+------+----------+
|    1 |          |
+------+----------+
1 row in set (0.00 sec)

Clearly value of constant is not shown as bit.

And when I do export using mysqldump insert statement gets exported as

INSERT INTO `bittest2` VALUES (1,'\0');

which is causing issues as I don't want null values inserted.

Is there a work around for this? MySQL version 5.7.13

Possible duplicate of [Can't see MySQL BIT field value when using SELECT](http://stackoverflow.com/questions/14248554/cant-see-mysql-bit-field-value-when-using-select) — Solarflare, Aug 04 '16 at 08:02
Question is similar, however that doesn't solve export issue. — rohitpal, Aug 04 '16 at 09:55
`\0` is not `null`. I'm not sure why the linked question doesn't answer your question, but `b'0'` is the same as `\0` (all bits in `\0` are 0, it's just not 8 bits as you usually think of when you see `\0`). A bit is not an integer, and if you reimport it (with your `insert`) is will work fine. It would actually work with 0, too, because it will cast it correctly, but bits are just not integers. If you want to have an integer, use one, e.g. `tinyint(1)`, or its synonym `bool`. (mysql used to use `tinyint` for `bit` some versions ago, but changed it to be more standard compliant). — Solarflare, Aug 04 '16 at 11:29

score 3 · Answer 1 · edited May 23 '17 at 12:18

3

You can use hex-blob option with mysqldump.

mysqldump -u[user-name] -p [db-name] [table-name] --hex-blob > [file-name].sql

Then, the sql file shows following values.

And you can import the dump file properly.

edited May 23 '17 at 12:18

Community

answered May 19 '17 at 04:57

yabaiwebyasan

score 1 · Answer 2 · answered Aug 05 '16 at 03:48

It works fine for me on 5.7.13 . You are mistaking the output of a bitvalue of 0 for something the screen in command-line tools can show.

It exports fine, it imports fine. And this query shows row 1:

select * from bittest where constant=b'0';

Regardless of whether or not you expect a bitvalue of 0 to be human readable on the display.

Exported with:

mysqldump -u UserName -p so_gibberish > c:\nate\bittest.sql

There is simply no problem to solve here.

mysqldump:

2 Answers2