CODEWITHSUNDEEP
mysqlsqlbit
stack
stack

Reputation: 10228

How to set 1 for all bits without knowing the length of column?

I have a bit column like this:

// mytable
+---------+------------+ 
|   id    |    numb    |
+---------+------------+
| int(11) |  bit(10)   |
+---------+------------+
| 1       | NULL       |
| 2       | NULL       |
+---------+------------+

Not this is expected result:

// mytable
+---------+------------+ 
|   id    |    numb    |
+---------+------------+
| int(11) |  bit(10)   |
+---------+------------+
| 1       | 1111111111 |
| 2       | 1111111111 |
+---------+------------+

You know, I can do that like this:

UPDATE mytable SET numb = b'1111111111';

But maybe I will change the length of numb column in future and then query above fails. How can I set 1 for all bits without knowing the length of bit-column?

EDIT: Actually I'm trying to do something like this:

UPDATE mytable SET numb = b'0';
/* output
+---------+------------+ 
|   id    |    numb    |
+---------+------------+
| int(11) |  bit(10)   |
+---------+------------+
| 1       | 0000000000 |
| 2       | 0000000000 |
+---------+------------+
*/

As you see, I set 0 for all bits without knowing the length of column. Well how can I do that just set 1 instead of 0?

Upvotes: 1

Views: 88

Answers (2)

Matt
Matt

Reputation: 15061

Use the REPEAT function and utilise the CHARACTER_MAXIMUM_LENGTH for the variable.

SELECT id, REPEAT('1', (SELECT CHARACTER_MAXIMUM_LENGTH 
                        FROM information_schema.columns
                        WHERE table_schema = DATABASE()
                        AND table_name = 'mytable'
                        AND COLUMN_NAME = 'numb')) AS numb
FROM mytable

Upvotes: 1

stack
stack

Reputation: 10228

Based on what @Stavr00 said in the comments, I can set the value to -1. Something like this:

UPDATE mytable SET numb = -1;

/* output
+---------+------------+ 
|   id    |    numb    |
+---------+------------+
| int(11) |  bit(10)   |
+---------+------------+
| 1       | 1111111111 |
| 2       | 1111111111 |
+---------+------------+
*/

Upvotes: 4

Related Questions