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

Question

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?

you could query the sys tables for the data types, and then use that to identify the length for your insert — , May 20 '16 at 13:14
`Select CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS where table_name = 'MYTABLE' and schema = 'MYSCHEMA' and column_Name = 'numb'` use [lpad](http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_lpad) with this to pad 1 for each value in length. — xQbert, May 20 '16 at 13:17
Can you set the value to `-1`, which is all 1s in `two's complement` — Stavr00, May 20 '16 at 13:20
wow .. @Stavr00 well played ..! `UPDATE mytable SET numb = -1;` works perfectly. — stack, May 20 '16 at 13:22

score 4 · Answer 1 · answered May 20 '16 at 13:25

4

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 |
+---------+------------+
*/

answered May 20 '16 at 13:25

stack

10,280
19
65
117

Much simpler than my effort :) – Matt May 20 '16 at 13:26
@Matt Yes ... but your query would be correct too. upvote for your answer – stack May 20 '16 at 13:27

score 1 · Answer 2 · answered May 20 '16 at 13:25

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

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

2 Answers2