Mysql select rows where a specific bit is set of an integer

Question

I need a field where I can set multiple values. I don't want to go ahead with mysql sets. The other way round I can do is say I have multiple categories.

cat 1, cat 2, cat 3....etc. limited to some 20 categories. Assume them as 1 (00001), 2(00010), 4(00100)...etc.

If my post is in 'cat 1' I would add value of 1(00001) in the post table field which is TINYINT. If my post in in 'cat 1' and 'cat 2' I would add value of 3 (00011) in the TINYINT field.

Now I want to get all the posts which are in 'cat 1' I want to query like field='****1' i.e. is just to check if last bit is on.

One more example. Say my post is in 'cat 2' and 'cat 3', I would add the value 6 (00111). Now I want to get all the posts which are in 'cat 2'. I would like to query where field='***1*'.

As above examples I would like to know how to query a integer field based on which bit is on?

Kickstart · Accepted Answer · 2014-11-06 09:53:23Z

2

Not played with bit strings in years (used to store a hell of a lot of info in them back in mainframe days).

However you should be able to use bit arithmetic. For example to check the 2nd bit is on:-

SELECT *
FROM sometable
WHERE bit_field & 2 = 2

Or to check the 2nd bit is on and the 1st bit is off:-

SELECT *
FROM sometable
WHERE bit_field & 3 = 2

Few details here:-

http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html

answered Nov 6, 2014 at 9:53

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Conqueror Rose · Accepted Answer · 2014-11-06 09:58:38Z

0

SELECT CONV(15,10,2);

or

SET @SAYI = 86;
SELECT
    @SAYI SAYI,
    CONCAT(
        TRUNCATE(@SAYI/64,0), 
        TRUNCATE(MOD(@SAYI,64)/32,0),
        TRUNCATE(MOD(MOD(@SAYI,64),32)/16,0), 
        TRUNCATE(MOD(MOD(MOD(@SAYI,64),32),16)/8,0),
        TRUNCATE(MOD(MOD(MOD(MOD(@SAYI,64),32),16),8)/4,0),
        TRUNCATE(MOD(MOD(MOD(MOD(MOD(@SAYI,64),32),16),8),4)/2,0),
        TRUNCATE(MOD(MOD(MOD(MOD(MOD(MOD(@SAYI,64),32),16),8),4),2)/1,0)
    ) AS BINARY_;

answered Nov 6, 2014 at 9:58

Conqueror Rose

11 bronze badge

1 Comment

user555045 Over a year ago

Why nested MODs? You can just take the last one, right?

Collectives™ on Stack Overflow

Mysql select rows where a specific bit is set of an integer

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related