MySQL - SET 集合数据类型

要通过 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/Python 的 execute() 函数执行"CREATE TABLE"语句，如下所示 -

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

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

con.connect(function (err) {
   if (err) throw err;
   //console.log("Connected successfully...!");
   //console.log("--------------------------");
   sql = "USE TUTORIALS";
   con.query(sql);
  //创建一个 test_table 表，接受一个集合类型的列。
   sql = "Create table test_table (ID INT auto_increment primary key, COL1 set('Goa', 'Assam', 'Delhi', 'Kerala') )";
   con.query(sql);
   
   //将数据插入到创建的表中
   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
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
# 创建带有设置列的表
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_insert = [
    ('Goa,Assam',),
    ('Assam,Goa',),
    ('Goa,Assam,Goa',),
    ('Goa,Assam,Assam',),
    ('Assam,Goa,Assam',)
]
# 向创建的表中插入数据
insert_query = "INSERT INTO test_table (COL1) VALUES (%s)"
cursorObj.executemany(insert_query, data_to_insert)
# 插入操作后提交更改
connection.commit()
print("Rows inserted successfully.")
# 现在显示表记录
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'})