mysql如何新增索引

mysql添加索引的方法：可以透過【create table】語句來添加，如【CONSTRAINT PRIMARY KEY | INDEX [<索引名>] [<索引類型>] <列名> ;】，表示建立一般索引。

在mysql中可以在建立表格（CREATE TABLE）的同時建立索引；也可以在建立表格後建立索引，使用CREATE INDEX語句或ALTER TABLE 語句。

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

#1、使用CREATE INDEX語句

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

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

語法說明如下：

●　<索引名>：指定索引名。一個表可以建立多個索引，但每個索引在該表中的名稱是唯一的。

●　<表名>：指定要建立索引的表名。

●　<列名>：指定要建立索引的列名。通常可以考慮將查詢語句中在 JOIN 子句和 WHERE 子句中經常出現的欄位作為索引列。

●　<長度>：可選項。指定使用列前的 le​​ngth 個字元來建立索引。使用列的一部分建立索引有利於減少索引檔案的大小，節省索引列所佔的空間。在某些情況下，只能對列的前綴進行索引。索引列的長度有一個最大上限 255 個位元組（MyISAM 和 InnoDB 表的最大上限為 1000 個位元組），如果索引列的長度超過了這個上限，就只能用列的前綴進行索引。另外，BLOB 或 TEXT 類型的欄位也必須使用前綴索引。

●　ASC|DESC：可選項。 ASC指定索引依照升序來排列，DESC指定索引依照降序來排列，預設為ASC。

二、使用CREATE TABLE 語句

#索引可以在建立表（CREATE TABLE）的同時創建，語法格式：

1、建立主鍵索引

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

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

2、建立一般索引

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

3、建立唯一性索引

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

4、建立外鍵索引

FOREIGN KEY <索引名> <列名>

範例1： 建立一個表格tb_stu_info，在該表的height 欄位建立一般索引。

mysql> CREATE TABLE tb_stu_info
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> INDEX(height)
    -> );

範例2：建立一個表格 tb_stu_info2，在該表格的 id 欄位上使用 UNIQUE 關鍵字建立唯一索引。

mysql> CREATE TABLE tb_stu_info2
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> UNIQUE INDEX(id)
    -> );

三、使用ALTER TABLE 語句

在使用ALTER TABLE 語句修改表格的同時，可以向已有的表新增索引。具體的做法是在 ALTER TABLE 語句中加入下列語法成分的某一項或幾項。

1、建立主鍵索引

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

2、建立一般索引

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

3、建立唯一性索引

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

4、建立外鍵索引

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

範例1：建立一個表格tb_stu_info3後，在該表的id 欄位上使用UNIQUE 關鍵字建立唯一索引。

mysql> CREATE TABLE tb_stu_info3
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> );
Query OK，0 rows affected (0.40 sec)
mysql>ALTER TABLE tb_stu_info3 ADD UNIQUE (id) ;

四、顯示索引資訊

#以使用 SHOW INDEX 指令來列出表格中的相關的索引資訊。可以透過新增 \G 來格式化輸出資訊。

範例：

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,
  KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.01 sec)

以上是mysql如何新增索引的詳細內容。更多資訊請關注PHP中文網其他相關文章！