MySQL - 删除索引

MySQL NodeJS 连接器 mysql2 提供了一个名为 query() 的函数，用于在 MySQL 数据库中执行 DROP INDEX 查询。

sql= "DROP INDEX index_name ON tbl_name";
con.query(sql);

我们可以使用 JDBC type 4 驱动程序通过 Java 与 MySQL 通信。它提供了一个名为 executeUpdate() 的函数，用于在 MySQL 数据库中执行 DROP INDEX 查询。

String sql = "DROP INDEX index_name ON table_name";
statement.executeQuery(sql);

MySQL Connector/Python 提供了一个名为 execute() 的函数，用于在 MySQL 数据库中执行 DROP INDEX 查询。

drop_index_query = "DROP INDEX index_name ON tbl_name"
cursorObj.execute(drop_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 temp(ID INT, Name VARCHAR(255), age INT, Location VARCHAR(255));"
  con.query(sql);
 sql = "INSERT INTO temp values(1, 'Radha', 29, 'Vishakhapatnam'),(2, 'Dev', 30, 'Hyderabad');"
  con.query(sql);
 //Creating Indexes
  sql = "CREATE INDEX sample_index ON temp (name) USING BTREE;"
  con.query(sql);
 sql = "CREATE INDEX composite_index on temp (ID, Name);"
  con.query(sql);
 //Displaying Indexes
  sql = "SHOW INDEXES FROM temp;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
    console.log("--------------------------");
  });
 //Dropping Indexes
  sql = "DROP INDEX sample_index ON temp;"
  con.query(sql);
 sql = "DROP INDEX composite_index ON temp;"
  con.query(sql);
 //Displaying Indexes after deleting
  sql = "SHOW INDEXES FROM temp;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

输出

生成的输出如下 -

Connected!
--------------------------
[
  {
    Table: 'temp',
    Non_unique: 1,
    Key_name: 'sample_index',
    Seq_in_index: 1,
    Column_name: 'Name',
    Collation: 'A',
    Cardinality: 2,
    Sub_part: null,
    Packed: null,
    Null: 'YES',
    Index_type: 'BTREE',
    Comment: '',
    Index_comment: '',
    Visible: 'YES',
    Expression: null
  },
  {
    Table: 'temp',
    Non_unique: 1,
    Key_name: 'composite_index',
    Seq_in_index: 1,
    Column_name: 'ID',
    Collation: 'A',
    Cardinality: 2,
    Sub_part: null,
    Packed: null,
    Null: 'YES',
    Index_type: 'BTREE',
    Comment: '',
    Index_comment: '',
    Visible: 'YES',
    Expression: null
  },
  {
    Table: 'temp',
    Non_unique: 1,
    Key_name: 'composite_index',
    Seq_in_index: 2,
    Column_name: 'Name',
    Collation: 'A',
    Cardinality: 2,
    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.Statement;

public class DropIndex {
   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...!");
        //Drop index;
         String sql = "DROP INDEX tid ON tutorials_tbl";
         statement.executeUpdate(sql);
         System.out.println("Index has been dropped Successfully...!");
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

输出

获得的输出如下所示 -

Connected successfully...!
Index has been dropped Successfully...!

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
drop_index_query = "DROP INDEX idx_submission_date ON tutorials_tbl"
cursorObj.execute(drop_index_query)
connection.commit()
print("Index dropped successfully.")
cursorObj.close()
connection.close()

输出

以下是上述代码的输出 -

Index dropped successfully.