首頁 > 文章 > 資料庫 > mysql索引的增刪改查怎麼實現？

mysql索引的增刪改查怎麼實現？

青灯夜游原創: 2020-10-14 16:45:563210瀏覽

mysql中可使用「CREATE INDEX」語句來新增索引；使用「SHOW INDEX」語句來查看索引；使用「DROP INDEX」語句來對索引進行刪除；而修改索引可以透過刪除原始索引，再根據需要建立一個同名的索引，從而實現修改索引的操作。

mysql索引的增刪改查怎麼實現？

（推薦教學：mysql影片教學）

mysql索引的建立

建立索引是指在某個表的一列或多列上建立一個索引，可以提高對錶的存取速度。建立索引對 MySQL 資料庫的高效運作來說是很重要的。

基本語法

MySQL 提供了三種建立索引的方法：

1) 使用CREATE INDEX 語句

#可以使用專門用於建立索引的CREATE INDEX 語句在一個已有的表上建立索引，但該語句不能建立主鍵。

語法格式：

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

語法說明如下：

#b493f009a433abb929a361542484fbbf：指定索引名。一個表可以建立多個索引，但每個索引在該表中的名稱是唯一的。
a26d98d33123a70024fa8ba5642906c6：指定要建立索引的表名。
dbbf19904bed3b90013ba2fcce963ac6：指定要建立索引的列名。通常可以考慮將查詢語句中在 JOIN 子句和 WHERE 子句中經常出現的欄位作為索引列。
ba904eac313ad550f30b0e6148d7c6f4：可選項。指定使用列前的 length 個字元來建立索引。使用列的一部分建立索引有利於減少索引檔案的大小，節省索引列所佔的空間。在某些情況下，只能對列的前綴進行索引。索引列的長度有一個最大上限 255 個位元組（MyISAM 和 InnoDB 表的最大上限為 1000 個位元組），如果索引列的長度超過了這個上限，就只能用列的前綴進行索引。另外，BLOB 或 TEXT 類型的欄位也必須使用前綴索引。
ASC|DESC：可選項。 ASC指定索引依照升序來排列，DESC指定索引依照降序來排列，預設為ASC。

2) 使用 CREATE TABLE 語句

索引也可以在建立表格（CREATE TABLE）的同時建立。在 CREATE TABLE 語句中加入以下語句。語法格式：

CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)

在 CREATE TABLE 語句中新增此語句，表示在建立新表的同時建立該表的主鍵。

語法格式：

KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)

在 CREATE TABLE 語句中新增此語句，表示在建立新表的同時建立該表的索引。

語法格式：

UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

在 CREATE TABLE 語句中新增此語句，表示在建立新表的同時建立該表的唯一性索引。

語法格式：

FOREIGN KEY <索引名> <列名>

在 CREATE TABLE 語句中新增此語句，表示在建立新表的同時建立該表的外鍵。

在使用 CREATE TABLE 語句定義列選項的時候，可以透過直接在某個列定義後面新增 PRIMARY KEY 的方式來建立主鍵。而當主鍵是由多個列組成的多列索引時，則不能使用這種方法，只能用在語句的最後加上一個PRIMARY KRY(dbbf19904bed3b90013ba2fcce963ac6，…) 子句的方式來實現。

3) 使用ALTER TABLE 語句

CREATE INDEX 語句可以在一個已有的表上建立索引，ALTER TABLE 語句也可以在一個已有的表上建立索引。在使用 ALTER TABLE 語句修改表格的同時，可以為現有的資料表新增索引。具體的做法是在 ALTER TABLE 語句中加入下列語法成分的某一項或幾項。

語法格式：

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

在 ALTER TABLE 語句中加入此語法成分，表示在修改表的同時為該表新增索引。

語法格式：

ADD PRIMARY KEY [<索引类型>] (<列名>,…)

在 ALTER TABLE 語句中加入此語法成分，表示在修改表的同時為該表新增主鍵。

語法格式：

ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

在 ALTER TABLE 語句中加入此語法成分，表示在修改表的同時為該表新增唯一性索引。

語法格式：

ADD FOREIGN KEY [<索引名>] (<列名>,…)

在 ALTER TABLE 語句中加入此語法成分，表示在修改表的同時為該表新增外鍵。

mysql索引的檢視

索引建立完成後，可以利用 SQL 語句查看已經存在的索引。在 MySQL 中，可以使用 SHOW INDEX 語句來查看表中所建立的索引。

查看索引的語法格式如下：

SHOW INDEX FROM <表名> [ FROM <数据库名>]

語法說明如下：

#a26d98d33123a70024fa8ba5642906c6：指定需要查看索引的資料表名。
50319af392acef3f82ca5b570a39170d：指定需要查看索引的資料表所在的資料庫，可省略。例如，SHOW INDEX FROM student FROM test; 語句表示查看 test 資料庫中 student 資料表的索引。

範例

SQL 語句和執行結果如下所示。

mysql> SHOW INDEX FROM tb_stu_info2\G
*************************** 1. row ***************************
        Table: tb_stu_info2
   Non_unique: 0
     Key_name: height
 Seq_in_index: 1
  Column_name: height
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.03 sec)

mysql索引的修改和刪除

删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

在 MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

基本语法

当不再需要索引时，可以使用 DROP INDEX 语句或 ALTER TABLE 语句来对索引进行删除。

1) 使用 DROP INDEX 语句

语法格式：

DROP INDEX <索引名> ON <表名>

语法说明如下：

b493f009a433abb929a361542484fbbf：要删除的索引名。
a26d98d33123a70024fa8ba5642906c6：指定该索引所在的表名。

2) 使用 ALTER TABLE 语句

根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。

DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
DROP INDEX index_name：表示删除名称为 index_name 的索引。
DROP FOREIGN KEY fk_symbol：表示删除外键。

注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

删除索引

【实例 1】删除表 tb_stu_info 中的索引，输入的 SQL 语句和执行结果如下所示。

mysql> DROP INDEX height
    -> ON tb_stu_info;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_stu_info\G
*************************** 1. row ***************************
       Table: tb_stu_info
Create Table: CREATE TABLE `tb_stu_info` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

【实例 2】删除表 tb_stu_info2 中名称为 id 的索引，输入的 SQL 语句和执行结果如下所示。

mysql> ALTER TABLE tb_stu_info2
    -> DROP INDEX height;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_stu_info2\G
*************************** 1. row ***************************
       Table: tb_stu_info2
Create Table: CREATE TABLE `tb_stu_info2` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

以上是mysql索引的增刪改查怎麼實現？的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

上一篇：mysql查詢資料庫下的表格結構？下一篇：mysql查詢資料庫下的表格結構？

看更多