How to edit values of SET data type

How to edit values of SET data type

You have a table with a column of SET data type:

…and you want to edit the value in `animal` field. One approach is to read old value, edit and save new value; but this solution is very common and a bit difficult.

 

Another appoach is to use capabilityies of the SET data type – to use it as is. Internally the SET data type is a 8 byte integer, where each bit specifies a value.

 

SET(\’dog\’ – 1st bit, \’cat\’ – 2nd bit, \’snake\’ – 3rd bit, \’pig\’ – 4th bit). So, the value \’dog,cat,snake,pig\’ equals to 1+2+4+8=15, or \’1111\’ in binary form.

 

This query shows `animal` field values and their decimal and binary forms –

 

To edit SET values we can apply bitwise operations. Here is an example:

 

1. If you want to add \’cat\’ value to \’dog,snake\’, use bitwise OR operation: 5 OR 2 => 7.

 

2. Vice versa: if you want to remove \’cat\’ from the value \’dog,cat,snake\’, use bitwise AND operation for inverse value of \’cat\’: 7 AND ~2 => 5

 

In addition, to remove value by its string name, use FIND_IN_SET function which is optimized to use bit arithmetic for column of type SET.

How do we write a query that removes the \’snake\’ value? Here is an example:

 

…FIND_IN_SET function returns number of bit, for \’snake\’ it is 3, we just convert it to the real value – \’100\’ in binary form or 4 in decimal.

0 thoughts on “How to edit values of SET data type”

Leave a Reply

Your email address will not be published. Required fields are marked *