MySQL - SET

The MySQL SET data type

The MySQL SET data type is used to store a set of values chosen from a predefined list of values. Each column of the SET datatype can have zero or more values chosen from its list of values. These values are specified as a comma-separated list when inserting or updating data.

It is important to note that the list of values that can be stored in a SET column is defined at the time the table is created, and any values outside this list are not allowed.

For example, if we define a SET column like this −

test_col SET('one', 'two') NOT NULL

The possible values for this column are −

• An empty string ('')
• 'one'
• 'two'
• 'one,two'

Storage of SET Data Type

A MySQL SET column can hold a maximum of 64 distinct members, which means that duplicate values are not allowed. If duplicates exist, MySQL will generate an error or a warning when strict SQL mode is enabled. Additionally, MySQL automatically removes trailing spaces from SET values when creating a table.

In MySQL, when you store a number in a SET column, the bits set in the binary representation of that number determine which set members are included in the column value. Consider the following query for a better understanding −

Create table test_table (
   ID int auto_increment primary key ,
   COL1 set('Goa', 'Assam', 'Delhi', 'Kerala')
);

In the above query, each set member is assigned a single bit with corresponding decimal and binary values −

So, if a value of 3 is assigned to the column (binary: 0011), it selects the first two SET members, resulting in 'Goa,Assam'.

Example

First of all, let us create a table with the name test_table using the following query −

Create table test_table (
   ID int auto_increment primary key ,
   COL1 set('Goa', 'Assam', 'Delhi', 'Kerala')
);

Following is the output obtained −

Query OK, 0 rows affected (0.02 sec)

When inserting values into a SET column, there is no specific order required for listing the elements. Even if a particular element is listed multiple times, when retrieved later, each element will appear only once, following the order specified during table creation.

Here, we are inserting the values into the set −

INSERT INTO test_table (COL1) VALUES 
('Goa,Assam'), 
('Assam,Goa'),
('Goa,Assam,Goa'),
('Goa,Assam,Assam'),
('Assam,Goa,Assam');

Output

The output produced is as shown below −

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Verification

Let us display all the records of the 'test_table' using the SELECT statement as follows −

SELECT * FROM test_table;

As we can see the output below, all the values in 'COL1' will appear as 'Goa,Assam' −

Example

In the following query, we are searching for the SET values in the table using the MySQL LIKE operator. It finds rows where 'COL1' contains 'GOA' anywhere, even as a substring −

SELECT * FROM test_table WHERE COL1 LIKE '%Goa%';

Output

On executing the given query, the output is displayed as follows −

Example

In here, we are fetching the rows where the values are exactly 'Goa,Assam' and in the same order as listed in the 'COL1' definition −

SELECT * FROM test_table WHERE COL1 = 'Goa,Assam';

Output

The output for the above query is as given below −

Updating the SET Values

In MySQL, you can update SET elements in various ways: by replacing elements, adding elements, or removing elements from the SET data. Here are examples of each method −

Replacing SET Data

In the following query, we replace the value in the 5^th row with the number 11, which corresponds to Goa + Assam + Kerala (8 + 2 + 1) −

UPDATE test_table SET COL1 = 11 WHERE Id = 5;

Output

The query executes successfully and produces the following output −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the changes done in the test_table, use the following SELECT query −

SELECT * FROM test_table;

Following is the output produced −

Adding Data to SET

You can add elements to an existing SET column using the CONCAT() function. In this example, we add "Kerala" to the value in the 3rd row −

UPDATE test_table SET COL1 = CONCAT(COL1, ",Kerala")
WHERE Id = 3;

Output

The output for this query is as follows −

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the changes done in the test_table, use the following SELECT query −

SELECT * FROM test_table;

The result shows the updated value −

Removing Data from SET

To remove a specific SET element, you can use the & ~ bitwise operation. In this example, we remove the "Assam" element from the value in the 4th row −

UPDATE test_table SET COL1 = COL1 & ~2 WHERE ID = 4;

Output

The output for this query is as follows −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Let us verify the test_table using the below query −

SELECT * FROM test_table;

Following is the table obtained −

SET Datatype Using a Client Program

We can also create column of the SET datatype using the client program.

Syntax

$sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set('Goa', 'Assam', 'Delhi', 'Kerala') )";
$mysqli->query($sql);

sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set('Goa', 'Assam', 'Delhi', 'Kerala') )";
con.query(sql);

String sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set(\"Goa\", \"Assam\", \"Delhi\", \"Kerala\"))";
statement.execute(sql);

sql = 'CREATE TABLE test_table (ID INT AUTO_INCREMENT PRIMARY KEY,  COL1 SET('Goa', 'Assam', 'Delhi', 'Kerala'))'    
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');

$sql = 'Create table test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala") )';
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!\n");
}
// insert data into created table    
$q ="INSERT INTO test_table (COL1) VALUES ('Goa,Assam'), ('Assam,Goa'), ('Goa,Assam,Goa'), ('Goa,Assam,Assam'), ('Assam,Goa,Assam')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!\n");
}
//now display the table records    
$s = "SELECT * FROM test_table";
if ($r = $mysqli->query($s)) {
    printf("Table Records: \n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Col_1: %s", $row["ID"], $row["COL1"]);
        printf("\n");
    }
} else {
    printf('Failed');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Col_1: Goa,Assam
 ID: 2, Col_1: Goa,Assam
 ID: 3, Col_1: Goa,Assam
 ID: 4, Col_1: Goa,Assam
 ID: 5, Col_1: Goa,Assam         

var mysql = require("mysql2");
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
   if (err) throw err;
   //console.log("Connected successfully...!");
   //console.log("--------------------------");
   sql = "USE TUTORIALS";
   con.query(sql);

   //create a test_table table, that accepts one column of set type.
   sql = "Create table test_table (ID INT auto_increment primary key, COL1 set('Goa', 'Assam', 'Delhi', 'Kerala') )";
   con.query(sql);
   
   //insert data into created table
   sql = "INSERT INTO test_table (COL1) VALUES ('Goa,Assam'), ('Assam,Goa'), ('Goa,Assam,Goa'), ('Goa,Assam,Assam'), ('Assam,Goa,Assam')";
   con.query(sql);
   
   //select datatypes of salary
   sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'test_table' AND COLUMN_NAME = 'col1'`;
   con.query(sql, function (err, result) {
     if (err) throw err;
     console.log(result);
   });
});  

[ { DATA_TYPE: 'set' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Set {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Set data types...!;
         String sql = "Create table test_table (ID INT auto_increment primary key, COL1 set(\"Goa\", \"Assam\", \"Delhi\", \"Kerala\"))";
         statement.execute(sql);
         System.out.println("column of a SET type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE test_table");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}  

Connected successfully...!
column of a SET type created successfully...!
ID int
COL1 set('Goa','Assam','Delhi','Kerala')  

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with Set column
sql = '''
CREATE TABLE test_table (
ID INT AUTO_INCREMENT PRIMARY KEY,
COL1 SET('Goa', 'Assam', 'Delhi', 'Kerala')
)
'''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    ('Goa,Assam',),
    ('Assam,Goa',),
    ('Goa,Assam,Goa',),
    ('Goa,Assam,Assam',),
    ('Assam,Goa,Assam',)
]
# Insert data into the created table
insert_query = "INSERT INTO test_table (COL1) VALUES (%s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM test_table"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Test Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

The table is created successfully!
Rows inserted successfully.
Test Table Data:
(1, {'Assam', 'Goa'})
(2, {'Assam', 'Goa'})
(3, {'Assam', 'Goa'})
(4, {'Assam', 'Goa'})
(5, {'Assam', 'Goa'})