How to write a bit in a table ?!

[expired user #5547]'s profile image [expired user #5547] posted 13 years ago in General Permalink
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
- 'false'
- 'FALSE'
- BIT(0)

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

Many thanks... I'm getting mad!
ansgar's profile image ansgar posted 13 years ago Permalink
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.
[expired user #5547]'s profile image [expired user #5547] posted 13 years ago Permalink
Hi there.

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

Ok thanks for your answer anyway :)
kalvaro's profile image kalvaro posted 13 years ago Permalink
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.

[expired user #5547]'s profile image [expired user #5547] posted 13 years ago Permalink
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.
[expired user #5547]'s profile image [expired user #5547] posted 13 years ago Permalink
I'm not the official designer of this table unfortunately I cannot change it.
kalvaro's profile image kalvaro posted 13 years ago Permalink
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:

http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html

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:

CREATE TABLE `test` (
`id` INT(11) NULL DEFAULT NULL,
`is_winter` BIT(1) NULL DEFAULT NULL
)
ENGINE=InnoDB;
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:

"id";"hex(is_winter)"
"1";"0"
"2";"1"
"3";"1"



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