MySQL - SET

MySQL: The SET Data Type

The SET data type in MySQL is a string object that can hold zero or more values, each of which must be chosen from a predefined list of allowed values specified when the table is created. It is useful for storing a small, fixed collection of options or tags.

Defining a SET Column

You define a SET column by providing a comma-separated list of its possible members.

CREATE TABLE UserPreferences (
    user_id INT PRIMARY KEY,
    notifications SET('email', 'sms', 'push', 'in_app')
);

A column defined as SET('a', 'b', 'c') can store any of the following values:

• ” (an empty string)
• ‘a’
• ‘b’
• ‘c’
• ‘a,b’
• ‘a,c’
• ‘b,c’
• ‘a,b,c’

Key Characteristics

• Maximum Members: A SET can have a maximum of 64 distinct members.
• Automatic Deduplication: If you insert a value with duplicate members (e.g., ‘a,b,a’), MySQL stores it as ‘a,b’.
• Order Preservation: Stored values are ordered according to the list in the table definition, not the insertion order.
• Trailing Spaces: Trailing spaces in member values are automatically removed during table creation.

How SET Values Are Stored

MySQL stores SET values efficiently as a bitmap. Each member in the set is assigned a bit. The first member corresponds to bit 0 (value 1), the second to bit 1 (value 2), the third to bit 2 (value 4), and so on. The stored value is the sum of the values of the selected members.

Set Member	Decimal Value	Binary Value
email	1	0001
sms	2	0010
push	4	0100
in_app	8	1000

Storing ‘email,push’ is equivalent to storing the numeric value 5 (1 + 4), or binary 0101.

Working with SET Data

Let’s create a table and insert some data.

CREATE TABLE Articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    tags SET('mysql', 'docker', 'security', 'performance')
);

INSERT INTO Articles(title, tags) VALUES
('Optimizing Queries', 'mysql,performance'),
('Containerizing DBs', 'docker,mysql'),
('SQL Injection Guide', 'security'),
('All About Tags', 'mysql,security,performance');

Querying with FIND_IN_SET()

The most reliable way to find rows with a specific SET member is the FIND_IN_SET() function. It’s safer than LIKE because it checks for whole members and avoids partial matches (e.g., searching for ‘app’ and matching ‘in_app’).

-- Find all articles tagged with 'mysql'
SELECT title, tags FROM Articles
WHERE FIND_IN_SET('mysql', tags) > 0;

Querying with Bitwise Operators

For performance, you can query using the numeric representation. To find articles tagged with ‘security’ (value 4), you can use a bitwise AND (&).

-- Find all articles tagged with 'security' (decimal value 4)
SELECT title, tags FROM Articles
WHERE tags & 4;

Updating SET Values

Adding a Member

To add a tag without removing existing ones, use a bitwise OR (|). Let’s add the ‘security’ tag (value 4) to the article about Docker.

UPDATE Articles SET tags = tags | 4 WHERE id = 2;

Removing a Member

To remove a tag, use a bitwise AND with a negated value (& ~). Let’s remove the ‘performance’ tag (value 8) from the first article.

UPDATE Articles SET tags = tags & ~8 WHERE id = 1;

Caveats and Modern Alternatives

While SET can seem convenient, it is a MySQL-specific feature and is often considered an anti-pattern for several reasons:

• Violates Normal Form: Storing multiple values in a single column violates the First Normal Form (1NF) of database normalization.
• Portability: It makes your schema non-portable to other database systems like PostgreSQL or SQL Server.
• Query Complexity: Queries can become complex and less intuitive, especially for developers unfamiliar with bitwise operations.
• Indexing Limitations: SET columns are difficult to index effectively for all types of queries.

The Normalized Alternative: A Junction Table

The standard and most flexible way to model a many-to-many relationship (like articles and tags) is with a junction table (also called a linking or associative table).

-- 1. A table for all possible tags
CREATE TABLE Tags (
    tag_id INT AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(50) UNIQUE NOT NULL
);

-- 2. The junction table linking articles to tags
CREATE TABLE ArticleTags (
    article_id INT,
    tag_id INT,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES Articles(id),
    FOREIGN KEY (tag_id) REFERENCES Tags(tag_id)
);

This design is more scalable, portable, and easier to query and index, making it the recommended best practice for modern applications.

Using SET in Application Code

Python (with mysql-connector-python)



### Code
This example inserts a new article with a set of tags and then fetches it. The Python driver intelligently converts the comma-separated string from MySQL into a Python `set` object.

```python
# Filename: use_set_type.py
import mysql.connector
from mysql.connector import Error

def manage_article_tags():
    try:
        with mysql.connector.connect(
            host='localhost', user='root', password='password', database='TUTORIALS'
        ) as connection:

            with connection.cursor() as cursor:
                # Insert a new article with tags
                title = 'Getting Started with SET'
                tags = 'mysql,security' # Pass as a comma-separated string
                insert_query = "INSERT INTO Articles (title, tags) VALUES (%s, %s)"
                cursor.execute(insert_query, (title, tags))
                article_id = cursor.lastrowid
                connection.commit()
                print(f"Inserted article with ID: {article_id}")

                # Fetch the article and check the tags
                select_query = "SELECT title, tags FROM Articles WHERE id = %s"
                cursor.execute(select_query, (article_id,))
                result = cursor.fetchone()

                if result:
                    fetched_title, fetched_tags = result
                    print(f"Title: {fetched_title}")
                    print(f"Tags: {fetched_tags}")
                    print(f"Type of tags object: {type(fetched_tags)}")

    except Error as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    manage_article_tags()

Output

Inserted article with ID: 5
Title: Getting Started with SET
Tags: {'mysql', 'security'}
Type of tags object: <class 'set'>