How to use binary data type in MySQL column

Question

I am stuck somewhere and need your guidance, basically I am inserting multiple values to single colomn using bit-wise operator OR(|) and my table structure is like and I am inserting weekdays like 1 for sunday, 2 from monday, 4 for tuesday, 8 for wednesday, and so on separating by pipe sign(|) like for sunday and monday it will insert like 1|2 and so on but when it inserted to table it is showing like that

So now I want to know how will I select and show the exact value that I have inserted and how to update it using php(codeigniter) and mysql?

I have used this query select cast(column_name as varchar) from table_name and it gives the result by sum all the decoded value like 5 for 1|4, 3 for 1|2, but now how can I convert it to the inserted type like 1|2 for 3 and 1|4 for 5 and so on...

the question is why do you want to store multiple values, separated by | in a binary column type? Typically anytime you store separated values, whether by commas (csv), tabs, or even |, you should normalize your table, and be adding one-to-many rows instead — Sean
– Sean, Commented Apr 14, 2016 at 6:51

v7d8dpo4 · Accepted Answer · 2016-04-24 11:32:18Z

1

$column&(1<<$day)

will be 0 if that day is not in column and 1<<$day if that day is in column in php.

You don't need to use binary type. Tinyint unsigned is enough. You can insert like this

(1|2)

In php, to convert it to the format you want,

echo '(';
$sep=0;
for($a=1;$a<=$column;$a<<=1)
    if($column&$a){
        if($sep) echo '|';
        else $sep=1;
        echo $a;
    }
echo ')';

edited Apr 24, 2016 at 11:32

answered Apr 14, 2016 at 8:40

v7d8dpo4

1,4099 silver badges9 bronze badges

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

3 Comments

Vivek Singh Over a year ago

can u plzz give a brief description about it ??

Vivek Singh Over a year ago

when i am trying to insert like (1|2) in database with tinyint datatype, it is inserting 0 in column

v7d8dpo4 Over a year ago

@Vicky insert into a(a) values((1|2)); worked fine for me. I got 3.

Collectives™ on Stack Overflow

How to use binary data type in MySQL column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related