Saving / querying binary sequences and searching using masks

Question

I found a good way to store some data in a binary sequence database, for example 0b0101000, and find rows that give a positive result after applying the mask.

For example: SELECT (0b0101010 & (1<<3 | 1<<5))>0;allows me to get strings with the 3rd or 5th bit, regardless of whether the remaining bits are turned on.

The problem is when I want to do this using ActiveRecord. This migration add_column :table, :column, :binary, :limit => 8.bytesactually creates a column TINYBLOB, not BINARYor VARBINARY, and I cannot apply my mask to its value because it is not considered a binary value.

I know that I could make the correct column format in the migration by executing the raw SQL statement and then query my table with the raw SQL segments for this part, but this does not look like a “Rails path”.

Thanks for any idea.

Answer 1

In fact, it is not optimal, but at least it works to keep this sequence in the TINYBLOB column.

I can query the database as follows

SELECT * FROM table WHERE (column & mask) = mask

For example, with a value in a column 10110110and a mask with 128(100000000), a row is selected.

But I had to build conditionspart of the query using a string; there are no accounting conditions and no placeholder.

() , Ruby:

find_conditions = []

find_conditions[0] = 'string_col = ?'
find_conditions << 'a_value_for_the_string_col'

binary_mask = "01100101"
find_conditions[0] += ' AND '
find_conditions << "(bin_col & #{binary_mask.to_i(2)}) = #{binary_mask.to_i(2)}"

results = Model.all(:conditions => find_conditions)

Answer 2

activerecord:

http://www.packtpub.com/article/Working-with-Rails-ActiveRecord-Migrations-Models-Scaffolding-and-Database-Completion

. :

| MySQL | 1

: | TINYBLOB, BLOB, MEDIUMBLOB LONGBLOB2 | limit = > 1 to 4294967296 ( = 65536) 2