CODEWITHSUNDEEP
mysql
Ali
Ali

Reputation: 1448

Not able to update bit in mysql table

I have a table with following sql:

CREATE TABLE `event` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  `description` varchar(2000) NOT NULL,
  `enabled` bit(1) NOT NULL,
  `end_date_time` datetime NOT NULL,
  `image_url` varchar(255) NOT NULL,
  `start_date_time` datetime NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
)

When i run the following command with a user that has permissions to insert, i get the following result:

mysql> insert into event (description, enabled, end_date_time, image_url, start_date_time, title) values ('asdf', 1, NOW(), 'asdf', NOW(), 'asf');

Why am i not able to update the 'enabled' flag?

mysql> select * from event;
+----+---------------------+---------------------+-------------+---------+---------------------+-----------+---------------------+-------+
| id | created_date        | modified_date       | description | enabled | end_date_time       | image_url | start_date_time     | title |
+----+---------------------+---------------------+-------------+---------+---------------------+-----------+---------------------+-------+
|  1 | 2017-05-29 17:10:39 | 2017-05-29 17:10:39 | DESCRIPTION |        | 2017-05-29 17:10:39 | URL       | 2017-05-29 17:10:39 | TITLE |
|  2 | NULL                | NULL                | asdf        |        | 2017-05-29 22:00:32 | asdf      | 2017-05-29 22:00:32 | asf   |
+----+---------------------+---------------------+-------------+---------+---------------------+-----------+---------------------+-------+
2 rows in set (0.00 sec)

mysql>

Note

mysql> select if((select enabled from event where id = 2), 'true', 'false');
+---------------------------------------------------------------+
| if((select enabled from event where id = 2), 'true', 'false') |
+---------------------------------------------------------------+
| true                                                          |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> update event set enabled = 0;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select if((select enabled from event where id = 2), 'true', 'false');
+---------------------------------------------------------------+
| if((select enabled from event where id = 2), 'true', 'false') |
+---------------------------------------------------------------+
| false                                                         |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Upvotes: 1

Views: 1927

Answers (1)

awm
awm

Reputation: 6570

You're setting it fine. It's just that when it's displayed, it's showing up as \x01, which is an unprintable character.

(What happens if you run SELECT * FROM events WHERE enabled=1?)

Things might be more intuitive if you use tinyint(1) instead of bit(1) for this column.

Upvotes: 4

Related Questions