How to insert value for bit column of mysql

Question

I want to insert the value into a bit type column in MySQL. But I am getting a data truncation error.

CREATE TABLE `BITTESTTABLE` (
  `db_field` varchar(50) NOT NULL,
   `is_editable` bit(1) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1

If I am inserting a row with

INSERT INTO BITTESTTABLE values('XYZ','0')

I am getting

Data too long for column 'is_editable' at row 1

So how do I insert the data for bit type column?

change to INSERT INTO BITTESTTABLE values('XYZ',0); inserting the number 0 not the character.. — amdixon
– amdixon, Commented Jan 1, 2016 at 10:24
Integer type data it takes perfectly even though I use quotes for the data like '123' but why bits type is not taking is there any restriction in MySQL? — SrinivasDJ
– SrinivasDJ, Commented Jan 1, 2016 at 10:55
if you will put like '123' it means it will take like string and Integer is support to null value and null value is also string — Piyush Gupta
– Piyush Gupta, Commented Jan 1, 2016 at 11:01
@SrinivasDJ, if you will insert integer value '123' sometime it works because of sql tools or query but if you will insert ineteger value like 'abc' the it will throw error.......sql workbench, yog tools supports integer '123' cause of conversation of tools — Piyush Gupta
– Piyush Gupta, Commented Jan 1, 2016 at 11:42
I recommend using a TINYINT instead of a BIT. Your DBAL should quote and escape every value, so you'll have single quotes, which will work fine for a TINYINT, but not for a BIT. — Rudie
– Rudie, Commented Jan 1, 2016 at 11:47

Kevin · Accepted Answer · 2017-05-05 14:56:27Z

38

You should use:

INSERT INTO `BITTESTTABLE` VALUES('XYZ', b'0');

answered May 5, 2017 at 14:56

Kevin

3913 silver badges3 bronze badges

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

1 Comment

contactmatt Over a year ago

Worked for entering directly into the MySQL Workbench grid.

Ahmed Khan · Accepted Answer · 2016-01-01 11:13:55Z

9

Since bit is a number not a string you need enter it like

INSERT INTO BITTESTTABLE values('XYZ',0)

edited Jan 1, 2016 at 11:13

answered Jan 1, 2016 at 10:56

Ahmed Khan

5264 silver badges12 bronze badges

2 Comments

SrinivasDJ Over a year ago

Suppose if I have interger data type if I insert column value as '123' mysql accepts. it won't give an error.

Ahmed Khan Over a year ago

Oh thanks for telling me this. Once I was adding number like this '1' in Int column it gives me error!

Piyush Gupta · Accepted Answer · 2016-01-01 10:29:14Z

6

You need to insert data in bit format like this INSERT INTO BITTESTTABLE values('XYZ',0);.you are doing values('XYZ','0') so it is taking as string value.

answered Jan 1, 2016 at 10:29

Piyush Gupta

2,1813 gold badges16 silver badges28 bronze badges

Comments

houssam · Accepted Answer · 2019-01-09 09:27:48Z

5

You can use true/false directly; BUT I am not sure if this is related to MySQL itself or to InnoDB : EXAMPLE:

INSERT INTO BITTESTTABLE values('XYZ',false);    
INSERT INTO BITTESTTABLE values('XYZ',true);

To be sure try to run:

SELECT TRUE , FALSE  ;

answered Jan 9, 2019 at 9:27

houssam

1,87316 silver badges30 bronze badges

Comments

devpro · Accepted Answer · 2016-01-01 10:31:44Z

4

Generally speaking, for boolean or bit data types, you would use 0 or 1 like that:

INSERT INTO BITTESTTABLE values('XYZ',0);

answered Jan 1, 2016 at 10:31

devpro

16.1k3 gold badges29 silver badges39 bronze badges

Comments

T.Ahmed · Accepted Answer · 2023-08-25 15:59:14Z

0

I had the same problem inserting data in prostgres db and I resolved.

Do like this INSERT INTO table (bitData) VALUES (CAST(1 AS bit));

rather then INSERT INTO table (bitData) VALUES (1);

answered Aug 25, 2023 at 15:59

T.Ahmed

134 bronze badges

Collectives™ on Stack Overflow

How to insert value for bit column of mysql

6 Answers 6

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related