How to write a bit in a table ?!

tbop posted 5 years ago in General
What a noob question isn't it?

I need to insert a new row into a table and one field needs to be a unique bit (basically it's a boolean)... Each time I want to post the new row, Heidi argues "data too long for column blabla".

I tried several ways.... No one works!

- 0
- (0)
- '0'
- B'0'
- B(0)
- false
- 'false'
- BIT(0)

What is THE way of writing a bit into a new row.

Many thanks... I'm getting mad!
ansgar posted 5 years ago
You should know the data grids in HeidiSQL do not support inserting BIT values. So, with the current version you need to write some UPDATE or INSERT to do that. I guess you just need a "0" without quotes for it then.
tbop posted 5 years ago
Hi there.

Actually I should have known but I didn't know.

Ok thanks for your answer anyway :)
kalvaro posted 5 years ago
BIT column type is not designed for boolean data. It is used to store bit maps, such as '00101001'.

You probably want to use TINYINT(1) (or BOOLEAN, but that's only an alias for TINYINT). It accepts 1 and 0 (as well as 2, 3...) but you can also use TRUE and FALSE, which are aliases for 1 and 0.

tbop posted 5 years ago
Actually I set this field by executing a hardcoded INSERT request. The request is returned as valid but I still cannot see in the data sheet view if this field is set to 0.

tbop posted 5 years ago
I'm not the official designer of this table unfortunately I cannot change it.
kalvaro posted 5 years ago
If you cannot change it, then you'll have to leave with itsmile

HeidiSQL's editor, as mentioned, does not support BIT columns. So you cannot use HeidiSQL to directly view or manipulate data. You need to write your own queries.

The MySQL manual does not seem to provide any usage example but it explains BIT among the numeric data types:


So we can assume it can be handled as a number. And given it's a bit field, it's likely than hexadecimal notation will make things easier. So I guess you can:

- Use hexadecimal notation to insert data (optional)
- Use the HEX() function to read the column (mandatory)

An example:

`is_winter` BIT(1) NULL DEFAULT NULL
insert into test (id, is_winter) values
(1, 0x0),
(2, 0x1),
(3, 0x2); -- This should be invalid
select id, hex(is_winter)
from test;

And we get:


Please login to leave a reply, or register at first.