How can I fill all bits of a bit datatype equal to 1?

Question

Here is my query:

UPDATE users SET active = -1 WHERE id = ? AND active IS NULL

It works on my current MySQL version. I mean it sets 1111111111 as active value. Noted that active is BIT(10) datatype.

But recently I've upgraded the MySQL version and it throws:

string(94) "SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'active' at row 1"

Any idea how can I fix it?

@BlackMarker What about `UPDATE users SET active = b'1111111111' WHERE id = ? AND active IS NULL` — Martin AJ, Jul 11 '18 at 20:58
Possible duplicate of ["ERROR 1406: 1406: Data too long for column" but it shouldn't be?](https://stackoverflow.com/questions/34418870/error-1406-1406-data-too-long-for-column-but-it-shouldnt-be) — PM 77-1, Jul 11 '18 at 20:59
Read [this](https://stackoverflow.com/a/34419094/2055998) answer. — PM 77-1, Jul 11 '18 at 21:00
Which version of MySQL? There was a change in the definition of `BIT` sometime. — Rick James, Jul 12 '18 at 02:25

Rick James · Answer 1 · 2018-07-12T02:25:59.643

0

If you want something with a bunch of bits, use BINARY(2) to get 16 bits. If you need a really long bit string, use BLOB or MEDIUMBLOB.

edited Jul 12 '18 at 02:25

answered Jul 12 '18 at 02:19

Rick James

1 Answers1