MySQL SET
SET is a data type of String object that can hold zero or more, or any number of string values. They must be chosen from a predefined list of values specified during table creation. It is one of the rarely used data type in the MySQL database. It is referred to as a complex type because its implementation increases the complexity.
The SET and ENUM data types work in a similar way because MySQL uses it with a predefined list of values. But one thing makes it different that are ENUM data type can hold only a single member of the predefined list of values, whereas SET data type allows us to store zero or any number of values together.
If we define the SET column values with multiple set members, then it is required to separate it using the comma (,) operator. For example, we have defined a column as:
Then, our column can have any of the following values:
Storage of SET data type
A SET data type can store a maximum of 64 distinct members in its column. If the SET column contains duplicate values, it throws an error when the strict SQL mode is enabled.
When we create a table and if the SET member values in the table definition have spaces, then they are automatically deleted from the table definition after the execution of the CREATE statement.
The SET data type is stored as a numeric value within the MySQL tables. It occupies from one to eight( i.e., 1, 2, 3, 4, 8) bytes, which depends on the number of elements contains in a SET column. The following table explains the storage requirement for the SET data type in MySQL:
| No. of Elements | No. of Bytes |
|---|---|
| 1-8 | 1 |
| 9-16 | 2 |
| 17-24 | 3 |
| 25-32 | 4 |
| 33-64 | 8 |
In MySQL, the SET element stored in the bitmap where each member represented by a single bit value. Let us look at the following table that illustrates it more clearly:
In the above definition, each of the set members assigned a single bit that have following decimal and binary values:
| SET Element | Decimal Value | Binary Value |
|---|---|---|
| Java | 1 | 0001 |
| Python | 2 | 0010 |
| Android | 4 | 0100 |
| PHP | 8 | 1000 |
Now, if we assign a value 9 to this column, in terms of binary, it will be 1001. So, it selects the first and fourth SET elements, and the resulting value is ”Java, PHP”, which will be the fifth member of the SET column.
Sometimes our SET data type column has more than one element; in that case, there is no need to insert the value in a specific order. It means the element can be in any order. MySQL also allows us to insert the same element many times in the SET data type column. When we retrieved the value, it will return each element once based on the order in which they were listed during the time of table creation.
Let us understand it with the help of following example where we are going to create a table named “myset_test” that contains a column SET(‘Java’, ‘Python’, ‘Android’, ‘PHP’):
Next, we need to insert the values into the table:
Now, when we retrieve the value from the table, we will get all these values to appear as “Java, PHP” so execute the below statement to see this:
In the output, we can see that our SET column have only (Java, PHP) values:
How to update SET data
MySQL SET data type can updates the data in three ways, which are given below:
1. By completely replacing the SET data
If we want to replace the complete data into a SET, it is required to specify the new values. Execute the following statements where the first query updates row = 4 with values = ‘Java, Python’ and second query updates row = 5 with values = 11. Here, 11 means 8+2+1=Java, Python, and PHP.
After the successful execution, execute the below command to verify the output:
Output
2. By adding SET members
Sometimes, there is a need to add the element into an existing SET column, then a CONCAT() or CONCAT_WS() function allows us to insert a new element in the comma-separated list. Execute this statement to understand it more clearly:
After the successful execution, execute the below command to verify the output:
Output
3. By removing SET members
If you want to drop any element from the existing SET, then a REPLACE() function can be used to remove the element SET column. Execute this statement to understand it more clearly:
Again, if you use the decimal value to remove the element from an existing set, use the bitwise AND(&) operator and bitwise NOT(~) operator. Execute the below statement:
After the successful execution, execute the below command to verify the output:
Output
If we want to see the integer values corresponding to the SET member, then we need to add the “+0” with the column name. Let us see the following example:
When we execute the above statement, the following result appears where integer values corresponding to the SET member included:
Disadvantages of Using SET data type
The SET data type does not recommend us to use it in our database because of the following disadvantages:
- The SET data type limits us to 64 members in the column.
- We cannot include commas in the SET elements.
- The SET data is not normalized.
- The SET data type has only one index that represents the whole set data. So, we cannot search for a particular element or member of the SET.
Why we use SET data type
The following are reasons to use the SET data type in MySQL:
- It can handle multiple values without much trouble.
- It allows us to compare multiple values without using complex JOIN operations.
- Its schema is simple that uses only one column instead of three different tables (for example, Person, Interest, Link the persons to particular interest) to store elements.
- It allows us to use binary functions for complex comparisons by comparing bit values on a single column.