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!
How to write a bit in a table ?!
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.
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.
If you cannot change it, then you'll have to leave with it
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:
And we get:
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.