distal-attribute
distal-attribute
distal-attribute
distal-attribute

How to write a bit in a table ?!

User, date Message
Written by tbop
4 years ago
Category: General
6 posts since Wed, 23 Feb 11
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!
Written by ansgar
4 years ago
5045 posts since Fri, 07 Apr 06
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.
Written by tbop
4 years ago
6 posts since Wed, 23 Feb 11
Hi there.

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

Ok thanks for your answer anyway :)
Written by kalvaro
4 years ago
599 posts since Thu, 29 Nov 07
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.

Written by tbop
4 years ago
6 posts since Wed, 23 Feb 11
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.
Written by tbop
4 years ago
6 posts since Wed, 23 Feb 11
I'm not the official designer of this table unfortunately I cannot change it.
Written by kalvaro
4 years ago
599 posts since Thu, 29 Nov 07
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.