MySQL – SET


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''})




Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *