CODEWITHSUNDEEP
mysqlbit-manipulation
Don
Don

Reputation: 1570

Using bitwise on a large number in mysql

I have a need to store a provider name and the country(ies) they are able to provide services in. There are 92 counties. Rather than store up to 92 rows, I'd like to store 2^91 so if they provide only in county 1 and 2, I'd store 3.

The problem I'm having is the largest number is 2475880078570760549798248448, which is way too big for the largest BigInt.

In the past when I've had smaller numbers of options I've been able to do something like....

SELECT * FROM tblWhatever WHERE my_col & 2;

If my_col had 2, 3, 6, etc. stored (anything with bit 2) it would be found.

I guess I'm not sure of 2 things... how to store AND how to query if stored in a way other than an INT.

Upvotes: 1

Views: 1033

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

You could use BINARY(13) as a datatype to store up to 94 bits. But MySQL bitwise operators only support BIGINT which is 64 bits.

So if you want to use bitwise operators, you'd have to store your county bitfield in two BIGINT columns (or perhaps one BIGINT and one INT), and in application code work out which of the two columns to search. That seems like it's awkward.

However, I'll point out a performance consideration: using bitwise operators for searching isn't very efficient. You can't make use of an index to do the search, so every query is be forced to perform a table-scan. As your data grows, this will become more and more costly.

It's the same problem as searching for a substring with LIKE '%word%', or searching for all dates with a given day of the month.

So I'd suggest storing each county in a separate row after all. You don't have to store 92 rows for each service provider -- you only have to store as many rows as the number of counties they service. The absence of a row indicates no service in that county.

Upvotes: 1

Related Questions