Proper way to store 32-bit binary in Laravel

Question

At the moment I'm using

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('test_binary', function (Blueprint $table) {
        $table->increments('id');
        $table->char('binary_number', 32)->charset('binary'); // From: https://stackoverflow.com/a/62615777/5675325
        $table->timestamps();

    });
}

to store 32-bit binary numbers like 00000000000000000000000000000010 which are result of

$binaryString = str_pad(base_convert(2, 10, 2),32,'0',STR_PAD_LEFT);

and are stored in such table

The seeder has something like

'binary_number' => str_pad(base_convert(2, 10, 2),32,'0',STR_PAD_LEFT),

In relation to have a BINARY type in the DB (as the previous image show), apokryfos suggested

I think bit is what you need here, though I'm not fully sure that Laravel supports that so you might need to use DB::raw("b'000000010'") to insert data to bit columns

Jarek Tkaczyk agrees with the (I'm not fully sure that Laravel supports that) part

Having bit type field means that you need to use raw values as a workaround whenever you are inserting/updating that field. (...)

DB::table('table')->insert(['bit_field' => DB::raw(0)]); // inserts 0

and he suggests for the OP to change to tinyint if he could (for a case of a column that can have as value 0 or 1).

This might hint that if one wants to deal with bit of size 32 through Laravel migrations, one would want to use an integer of higher size than tinyint.

So, if I wanted to have an int with size 32, Alexey Mezenin points out

You can't do this, but you can use different types of integer:

$table->bigInteger()
$table->mediumInteger()
$table->integer()
$table->smallInteger()
$table->tinyInteger()

What should happen to

$table->char('binary_number', 32)->charset('binary');

then to accomodate the 32-bit binary numbers as values and how to insert/retrieve such records?

Answer 1

The input in your database should be of type unsigned integer wich is 32 bit size or 4 bytes.

$table->unsignedInteger();

So, saving the value should not be an issue, you just have a binary value to switch to a decimal one using bindec() or a simple SUM using the constant method.

For the query part, let say you want the results wich has the 3rd bit as 1.

$query->whereRaw('BIT_COUNT(4 & fieldName) = 1')

i'm used to assign constants for those values on the model concerned wich help maintaining the code & debug.

class User extends Model {
const NOTIFICATION_FRIEND = 1; //2^0 or 0000 ... 0000 0001
const NOTIFICATION_CLIENT = 2; //2^1 or 0000 ... 0000 0010
const NOTIFICATION_APP = 4; //2^2 or 0000 ... 0000 0100
const NOTIFICATION_VENDOR = 8; //2^3 or 0000 ... 0000 1000
//...
// up to a max of 32nd one wich is 2147483648 (or 2^31)
}

So the query looks like this

$usersToBeNotified = $currentUser->friends()
    ->whereRaw('BIT_COUNT('.User::NOTIFICATION_FRIEND .' & notification_preferences) = 1')
    ->get();

To build the integer to be stored in from a form, just sum the constants.

$user->notification_preferences  = User::NOTIFICATION_FRIEND + User::NOTIFICATION_APP;