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://: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.