Why MySQL BIT_OR() is returning different value than PHP bitwise operation

Question

I have the following data in a MySQL table

"Data Dump" 2 phone_calls 001 2 phone_calls 010 2 phone_calls 100 2 phone_calls 1000 2 phone_calls 10000 2 phone_calls 100000

if I run PHP code to do bitwise Or operation like so

echo bindec('001') | bindec('010') | bindec('100') | bindec('1000') | bindec('10000') | bindec('100000');

I get 63 for output "which is expected"

if I do the Or manually

000001
000010
000100
001000
010000
100000
======
111111

the result = 111111 which is `32 + 16 + 8 + 4 + 2 + 1 = 63`

When I run the following query in MySQL

SELECT user_id, section_name, BIT_OR(permission_type) AS final
FROM permissions
WHERE section_name ='phone_calls' and user_id = 2
GROUP BY user_id, section_name

which is basically running BIT_OR() on "Data Dump" listed above and the output is

 2  phone_calls 108543

why MySQL gives me 108543 and PHP gives me 63? How can I get MySQL to give me 63?

paxdiablo · Accepted Answer · 2014-12-03T01:42:44.997

3

108543 is what you get when you or together the decimal values {1, 10, 100, 1000, 10000, 100000}.

In other words, they're not being treated as binary values.

You either need to store the correct decimal values for the binary equivalents, { 1, 2, 4, 8, 16, 32}, or find a way to convert decimal variants holding only 0 and 1 digits into an appropriate value.

If you want to retain strings holding the bit pattern, they can be converted into decimal with something like:

conv(colname,2,10)

which does a base conversion:

mysql> select conv('10',2,10);
    -> '2'
mysql> select conv('1000',2,10);
    -> '8'

edited Dec 03 '14 at 01:42

answered Dec 03 '14 at 01:26

paxdiablo

854,327
234
1,573
1,953

I got it :) I will give you the correct answer. but I left an answer for the next person below :) Thank you so much. What should I make the column type to avid conversion? – Jaylen Dec 03 '14 at 01:31
@Mike, you can leave it as is since the solution works. I myself would probably have stored an integral type rather than a string since the operations would tend to be faster, at least with regular databases. Whether that's the case for MySQL, I don't know. If it, as I seem to remember, stores everything as strings any way, the difference might not be so much. In any case, the first commandment of optimisation is "measure, don't guess" and the second is "check your authorities, don't trust some random bod of the internet just because they sound knowledgeable" :-) – paxdiablo Dec 03 '14 at 01:45

score 0 · Answer 2 · answered Dec 03 '14 at 01:30

0

I got it :)

SELECT user_id, section_name, BIT_OR(CONV(permission_type, 2, 10)) AS final
FROM data_import.permissions
WHERE section_name ='phone_calls' and user_id = 2
GROUP BY user_id, section_name

answered Dec 03 '14 at 01:30

Jaylen

39,043
40
128
221

Why MySQL BIT_OR() is returning different value than PHP bitwise operation

2 Answers2