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 — Solarflare
– Solarflare, Commented Aug 4, 2016 at 8:02
Question is similar, however that doesn't solve export issue. — rohitpal
– rohitpal, Commented Aug 4, 2016 at 9: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
– Solarflare, Commented Aug 4, 2016 at 11:29

Community · Accepted Answer · 2017-05-23 12:18:16Z

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.

b'0' to 0x00
b'1' to 0x01

And you can import the dump file properly.

More Info: Does mysqldump handle binary data reliably?

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered May 19, 2017 at 4:57

yabaiwebyasan

2333 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Drew · Accepted Answer · 2016-08-05 03:48:38Z

1

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:

answered Aug 5, 2016 at 3:48

Drew

25k10 gold badges47 silver badges81 bronze badges

Collectives™ on Stack Overflow

MySQL bit values export DML issue

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related