Course
SET
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
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 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 −
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
PHPNodeJSJavaPython
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);
Example
Following are the programs −
PHPNodeJSJavaPython
$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