MySQL - SET 数据类型



MySQL SET 数据类型

MySQL 的SET数据类型用于存储从预定义值列表中选择的一组值。SET 数据类型的每一列可以包含从其值列表中选择的零个或多个值。在插入或更新数据时,这些值以逗号分隔列表的形式指定。

需要注意的是,SET 列中可以存储的值列表是在创建表时定义的,任何不在此列表中的值都是不允许的。

例如,如果我们这样定义一个SET列:

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

此列的可能值如下:

  • 空字符串('')
  • 'one'
  • 'two'
  • 'one,two'

SET 数据类型的存储

MySQL SET 列最多可以包含64 个不同的成员,这意味着不允许重复值。如果存在重复值,并且启用了严格 SQL 模式,MySQL 将生成错误或警告。此外,MySQL 在创建表时会自动删除 SET 值后面的空格。

在 MySQL 中,当您将数字存储在 SET 列中时,该数字的二进制表示中设置的位决定了哪些集合成员包含在列值中。请考虑以下查询以更好地理解:

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

在上面的查询中,每个集合成员都被分配了一个位,并具有相应的十进制和二进制值:

集合成员 十进制值 二进制值
Goa 1 0001
Assam 2 -0010
Delhi 4 0100
Kerala 8 1000

因此,如果将值 3 分配给列(二进制:0011),它将选择前两个 SET 成员,结果为 'Goa,Assam'。

示例

首先,让我们使用以下查询创建一个名为 test_table 的表:

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

获得的输出如下:

Query OK, 0 rows affected (0.02 sec)

将值插入 SET 列时,列出元素不需要任何特定顺序。即使某个元素被列出多次,稍后检索时,每个元素都只出现一次,并遵循创建表时指定的顺序。

在这里,我们将值插入集合中:

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

输出

生成的输出如下所示:

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

验证

让我们使用 SELECT 语句显示 'test_table' 的所有记录,如下所示:

SELECT * FROM test_table;

正如我们在下面的输出中看到的,'COL1' 中的所有值都将显示为 'Goa,Assam':

ID COL1
1 Goa,Assam
2 Goa,Assam
3 Goa,Assam
4 Goa,Assam
5 Goa,Assam

示例

在下面的查询中,我们使用 MySQL LIKE 运算符搜索表中的 SET 值。它查找 'COL1' 任何位置包含 'GOA' 的行,即使它是子字符串:

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

输出

执行给定查询后,将显示以下输出:

ID COL1
1 Goa,Assam
2 Goa,Assam
3 Goa,Assam
4 Goa,Assam
5 Goa,Assam

示例

在这里,我们获取值完全为 'Goa,Assam' 并且顺序与 'COL1' 定义中列出的顺序相同的行:

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

输出

上述查询的输出如下所示:

ID COL1
1 Goa,Assam
2 Goa,Assam
3 Goa,Assam
4 Goa,Assam
5 Goa,Assam

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

更新 SET 值

在 MySQL 中,您可以通过多种方式更新 SET 元素:替换元素、添加元素或从 SET 数据中删除元素。以下是每种方法的示例:

替换 SET 数据

在下面的查询中,我们将第 5 行的值替换为数字 11,它对应于 Goa + Assam + Kerala (8 + 2 + 1):

UPDATE test_table SET COL1 = 11 WHERE Id = 5;

输出

查询成功执行并产生以下输出:

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

验证

要验证在 test_table 中所做的更改,请使用以下 SELECT 查询:

SELECT * FROM test_table;

生成的输出如下:

ID COL1
1 Goa,Assam
2 Goa,Assam
3 Goa,Assam
4 Goa,Assam
5 Goa,Assam,Kerala

向 SET 添加数据

您可以使用 CONCAT() 函数向现有的 SET 列添加元素。在此示例中,我们将“Kerala”添加到第 3 行的值:

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

输出

此查询的输出如下:

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

验证

要验证在 test_table 中所做的更改,请使用以下 SELECT 查询:

SELECT * FROM test_table;

结果显示更新后的值:

ID COL1
1 Goa,Assam
2 Goa,Assam
3 Goa,Assam,Kerala
4 Goa,Assam
5 Goa,Assam,Kerala

从 SET 中删除数据

要删除特定的 SET 元素,可以使用& ~ 位运算。在此示例中,我们将从第 4 行的值中删除“Assam”元素:

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

输出

此查询的输出如下:

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

验证

让我们使用以下查询验证test_table

SELECT * FROM test_table;

获得的表如下:

ID COL1
1 Goa,Assam
2 Goa,Assam
3 Goa,Assam,Kerala
4 Goa
5 Goa,Assam,Kerala

使用客户端程序操作 SET 数据类型

我们还可以使用客户端程序创建 SET 数据类型的列。

语法

要通过 PHP 程序创建 SET 数据类型的列,我们需要使用mysqli 函数query()执行“CREATE TABLE”语句,如下所示:

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

要通过 JavaScript 程序创建 SET 数据类型的列,我们需要使用mysql2 库的query()函数执行“CREATE TABLE”语句,如下所示:

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

要通过 Java 程序创建 SET 数据类型的列,我们需要使用JDBC 函数execute()执行“CREATE TABLE”语句,如下所示:

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

要通过 python 程序创建 SET 数据类型的列,我们需要使用MySQL Connector/Pythonexecute()函数执行“CREATE TABLE”语句,如下所示:

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

示例

以下是程序:

$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://#: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'})
广告