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"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  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);

  //Inserting records
  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);

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

  //Displaying list of indexes
  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
#establishing the connection
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.