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 −
Set Member | Decimal Value | Binary Value |
---|---|---|
Goa | 1 | 0001 |
Assam | 2 | -0010 |
Delhi | 4 | 0100 |
Kerala | 8 | 1000 |
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” −
ID | COL1 |
---|---|
1 | Goa,Assam |
2 | Goa,Assam |
3 | Goa,Assam |
4 | Goa,Assam |
5 | 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 −
ID | COL1 |
---|---|
1 | Goa,Assam |
2 | Goa,Assam |
3 | Goa,Assam |
4 | Goa,Assam |
5 | Goa,Assam |
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 −
ID | COL1 |
---|---|
1 | Goa,Assam |
2 | Goa,Assam |
3 | Goa,Assam |
4 | Goa,Assam |
5 | Goa,Assam |
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 5th 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 −
ID | COL1 |
---|---|
1 | Goa,Assam |
2 | Goa,Assam |
3 | Goa,Assam |
4 | Goa,Assam |
5 | Goa,Assam,Kerala |
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 −
ID | COL1 |
---|---|
1 | Goa,Assam |
2 | Goa,Assam |
3 | Goa,Assam,Kerala |
4 | Goa,Assam |
5 | Goa,Assam,Kerala |
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 −
ID | COL1 |
---|---|
1 | Goa,Assam |
2 | Goa,Assam |
3 | Goa,Assam,Kerala |
4 | Goa |
5 | Goa,Assam,Kerala |
SET Datatype Using a Client Program
We can also create column of the SET datatype using the client program.
Syntax
To create a column of SET datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −
$sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )"; $mysqli->query($sql);
To create a column of SET datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −
sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )"; con.query(sql);
To create a column of SET datatype through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −
String sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala"))"; statement.execute(sql);
To create a column of SET datatype through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −
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();
Output
The output obtained is as follows −
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); }); });
Output
The output produced is as follows −
[ { 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); } } }
Output
The output obtained is as shown below −
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()
Output
Following is the output of the above code −
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''})