stack - 5 months ago 5x

SQL Question

I want to know what's the best datatype to store these:

`null`

`0`

`/* the length of other numbers is always 7 digits */`

`0000000`

`0000001`

`0000010`

`0000011`

`/* and so on */`

`1111111`

I have tested,

`INT`

`0`

`1`

Answer

What you are showing are binary numbers

- 0000000 = 0
- 0000001 = 2^0 = 1
- 0000010 = 2^1 = 2
- 0000011 = 2^0 + 2^1 = 3

So simply store these numbers in an integer data type (which is internally stored with bits as shown of course). You could use BIGINT for this, as recommended in the docs for bitwise operations (http://dev.mysql.com/doc/refman/5.7/en/bit-functions.html).

Here is how to set flag n:

```
UPDATE mytable
SET bitmask = POW(2, n-1)
WHERE id = 12345;
```

Here is how to add a flag:

```
UPDATE mytable
SET bitmask = bitmask | POW(2, n-1)
WHERE id = 12345;
```

Here is how to check a flag:

```
SELECT *
FROM mytable
WHERE bitmask & POW(2, n-1)
```

But as mentioned in the comments: In a relational database you usually use columns and tables to show attributes and relations rather than an encoded flag list.

Source (Stackoverflow)

Comments