WHAT IS SET Data Type in MySQL?

WHAT IS SET Data Type in MySQL?

In MySQL, the SET data type is used to store a collection of values from a predefined list. It allows you to select zero or more values from the list and stores them as a string.

The syntax for creating a column with the SET data type is as follows:

CREATE TABLE table_name (
    column_name SET(value1, value2, .., valueN)
);

The values within the parentheses represent the available options for that particular column. Each value should be enclosed in single quotes and separated by commas.

Example:

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    skills SET('HTML', 'CSS', 'JavaScript', 'PHP', 'MySQL')
);

In this example, we have created an “employees” table with three columns: “id”, “name”, and “skills”. The “skills” column is of the SET data type and can store any combination of the available skills.

Retrieving Values from a SET Column:

You can retrieve the values stored in a SET column using the SELECT statement. The result will be returned as a comma-separated string.

SELECT skills FROM employees;

This query will return something like:

+----------------+
|     skills     |
+----------------+
| HTML,CSS       |
| JavaScript     |
| CSS,PHP        |
+----------------+

Modifying Values in a SET Column:

To modify the values stored in a SET column, you need to use an UPDATE statement. You can add or remove values using the following syntax:

UPDATE employees
SET skills = 'HTML,CSS,JavaScript'
WHERE id = 1;

This query will update the “skills” column for the employee with ID 1 to contain only the specified values.

Conclusion:

The SET data type in MySQL is a useful way to store and manage a collection of values. It allows you to select multiple options from a predefined list and store them as a string. Retrieving and modifying values in SET columns can be done using standard SQL statements.

By understanding how to use the SET data type effectively, you can enhance your database design and improve the flexibility of your applications.