Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » How to edit values of SET data type

How to edit values of SET data type

Author: rootsql Date: 23-11-2012, 16:44

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

CREATE TABLE table_animals(
  id INT PRIMARY KEY,
  animals SET('dog', 'cat', 'snake', 'pig')
);
INSERT INTO table_animals VALUES
  (1, 'dog,snake'),
  (2, 'dog,snake,cat'),
  (3, 'pig,cat'),
  (4, 'dog,pig');

...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 -

SELECT
  id, animals, CAST(animals AS SIGNED) dec_value, CONV(CAST(animals AS SIGNED),10,2) bin_value
FROM
  table_animals;
+----+---------------+-----------+-----------+
| id | animals       | dec_value | bin_value |
+----+---------------+-----------+-----------+
|  1 | dog,snake     |         5 | 101       |
|  2 | dog,cat,snake |         7 | 111       |
|  3 | cat,pig       |        10 | 1010      |
|  4 | dog,pig       |         9 | 1001      |
+----+---------------+-----------+-----------+

 

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.

UPDATE table_animals SET animals = animals | 2 WHERE id = 1;
SELECT * FROM table_animals WHERE id = 1;
+----+---------------+
| id | animals       |
+----+---------------+
|  1 | dog,cat,snake |
+----+---------------+

 

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

UPDATE table_animals SET animals = animals & ~2 WHERE id = 1;
SELECT * FROM table_animals WHERE id = 1;
+----+-----------+
| id | animals   |
+----+-----------+
|  1 | dog,snake |
+----+-----------+

 

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:

SELECT * FROM table_animals WHERE id = 2;
+----+---------------+
| id | animals       |
+----+---------------+
|  2 | dog,cat,snake |
+----+---------------+

 

...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.

UPDATE table_animals SET animals = animals & ~(1 << FIND_IN_SET('snake', animals) - 1) WHERE id = 2;
SELECT * FROM table_animals WHERE id = 2;
+----+---------+
| id | animals |
+----+---------+
|  2 | dog,cat |
+----+---------+

Tags: SET

Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
Also that we would do without your very good idea
Information
Members of Guests cannot leave comments.