MySQL - 唯一索引

要通过 JavaScript 程序在 MySQL 表中创建唯一索引，我们需要使用 mysql2 库的 query() 函数执行 CREATE UNIQUE INDEX 语句，如下所示 -

sql = "CREATE UNIQUE INDEX unique_ind ON CUSTOMERS (SALARY)";
con.query(sql);

要通过 Java 程序在 MySQL 表中创建唯一索引，我们需要使用 JDBC 的 executeUpdate() 函数执行 CREATE UNIQUE INDEX 语句，如下所示 -

String sql = "CREATE UNIQUE INDEX UIID ON tutorials_tbl (tutorial_id)";
st.executeUpdate(sql);

要通过 Python 程序在 MySQL 表中创建唯一索引，我们需要使用 MySQL Connector/Python 的 execute() 函数执行 CREATE UNIQUE INDEX 语句，如下所示 -

create_unique_index_query = "CREATE UNIQUE INDEX idx_unique_tutorial_id ON tutorials_tbl (tutorial_id)"
cursorObj.execute(create_unique_index_query)

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});
 //连接到 MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");
 //创建数据库
  sql = "create database TUTORIALS"
  con.query(sql);
 //选择数据库
  sql = "USE TUTORIALS"
  con.query(sql);
  //创建表
  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL,NAME VARCHAR(15) NOT NULL,AGE INT NOT NULL,ADDRESS VARCHAR(25),SALARY DECIMAL(10, 2),PRIMARY KEY(ID));"
  con.query(sql);
 //插入记录
  sql = "INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, 'Ramesh', '32', 'Ahmedabad', 2000),(2, 'Khilan', '25', 'Delhi', 1500),(3, 'kaushik', '23', 'Kota', 2500),(4, 'Chaitali', '26', 'Mumbai', 6500),(5, 'Hardik','27', 'Bhopal', 8500),(6, 'Komal', '22', 'MP', 9000),(7, 'Muffy', '24', 'Indore', 5500);"
  con.query(sql);
 //创建唯一索引
  sql = "CREATE UNIQUE INDEX unique_ind ON CUSTOMERS (SALARY)";
  con.query(sql);
 //显示索引列表
  sql = "SHOW INDEX FROM CUSTOMERS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**List of indexes:**")
    console.log(result)
  });
});  

输出

生成的输出如下 -

Connected!
--------------------------
**List of indexes:**
[
  {
    Table: 'customers',
    Non_unique: 0,
    Key_name: 'PRIMARY',
    Seq_in_index: 1,
    Column_name: 'ID',
    Collation: 'A',
    Cardinality: 7,
    Sub_part: null,
    Packed: null,
    Null: '',
    Index_type: 'BTREE',
    Comment: '',
    Index_comment: '',
    Visible: 'YES',
    Expression: null
  },
  {
    Table: 'customers',
    Non_unique: 0,
    Key_name: 'unique_ind',
    Seq_in_index: 1,
    Column_name: 'SALARY',
    Collation: 'A',
    Cardinality: 7,
    Sub_part: null,
    Packed: null,
    Null: 'YES',
    Index_type: 'BTREE',
    Comment: '',
    Index_comment: '',
    Visible: 'YES',
    Expression: null
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class UniqueIndex {
   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...!");
        //Create a unique index on the tutorials_tbl...!;
         String sql = "CREATE UNIQUE INDEX UIID ON tutorials_tbl (tutorial_id)";
         statement.executeUpdate(sql);
         System.out.println("Unique Index created Successfully...!");
        //showing the indexes...!
         ResultSet resultSet = statement.executeQuery("SHOW INDEXES FROM tutorials_tbl");
         System.out.println("Following are the indexes in tutorials_tbl");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)
                    +" "+resultSet.getString(3)+" " + resultSet.getString(4)
                    +" " + resultSet.getString(4));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}      

输出

获得的输出如下所示 -

Connected successfully...!
Unique Index created Successfully...!
Following are the indexes in tutorials_tbl
tutorials_tbl 0 PRIMARY 1 1
tutorials_tbl 0 UIID 1 1  

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
create_unique_index_query = "CREATE UNIQUE INDEX idx_unique_tutorial_id ON tutorials_tbl (tutorial_id)"
cursorObj.execute(create_unique_index_query)
connection.commit()
print('unique index created successfully.')
cursorObj.close()
connection.close()

输出

以下是上述代码的输出 -

unique index created successfully.