MySQL創(chuàng)建、刪除索引的操作代碼
一、創(chuàng)建索引
MySQL支持多種方法在單個(gè)或多個(gè)列上創(chuàng)建索引：
- 在創(chuàng)建表的定義語(yǔ)句CREATE TABLE中指定索引列
- 使用ALTER TABLE語(yǔ)句在存在的表上創(chuàng)建索引
- 或者使用CREATE INDEX語(yǔ)句在已存在的表上添加索引
1、創(chuàng)建表時(shí)創(chuàng)建索引
使用CREATE TABLE創(chuàng)建表時(shí)，除了可以定義列的數(shù)據(jù)類型外，還可以定義主鍵約束、外鍵約束或者唯一性約束，而不論創(chuàng)建哪種約束，在定義約束的同時(shí)，相當(dāng)于在指定列上創(chuàng)建了一個(gè)索引。
舉例：
CREATE TABLE dept( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(20) ); CREATE TABLE emp( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(20) UNIQUE, dept_id INT, CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id) )
但是，如果顯式創(chuàng)建表時(shí)，創(chuàng)建索引的話，基本語(yǔ)法格式如下：
CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
- UNIQUE 、 FULLTEXT 和 SPATIAL 為可選參數(shù)，分別表示唯一索引、全文索引和空間索引；
- INDEX 與 KEY 為同義詞，兩者的作用相同，用來(lái)指定創(chuàng)建索引；
- index_name 指定索引的名稱，為可選參數(shù)，如果不指定，那么MySQL 默認(rèn) col_name 為索引名；
- col_name 為需要?jiǎng)?chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中定義的多個(gè)列中選擇；
- length 為可選參數(shù)，表示索引的長(zhǎng)度，只有字符串類型的字段，才能指定索引長(zhǎng)度；
- ASC 或 DESC 指定升序或者降序的索引值存儲(chǔ)。
（1）創(chuàng)建普通索引
在book表中的book_name字段上建立普通索引，SQL語(yǔ)句如下：
INDEX idx_bname (book_name)
#顯式的方式創(chuàng)建
#1創(chuàng)建普通的索引
CREATE TABLE book (
book_id INT ,
book_name VARCHAR (100) ,
AUTHORS VARCHAR (100) ,
info VARCHAR(100) ,
COMMENT VARCHAR (100) ,
year_publication YEAR,
#聲明索引
INDEX idx_bname (book_name))
;查看索引
#通過命令查看索引
#方式l:
mysql> show create table book \G
*************************** 1. row ***************************
Table: book
Create Table: CREATE TABLE `book` (
`book_id` int(11) DEFAULT NULL,
`book_name` varchar(100) DEFAULT NULL,
`AUTHORS` varchar(100) DEFAULT NULL,
`info` varchar(100) DEFAULT NULL,
`COMMENT` varchar(100) DEFAULT NULL,
`year_publication` year(4) DEFAULT NULL,
KEY `idx_bname` (`book_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
# 方式2：
show index from book;（2）創(chuàng)建唯一索引
舉例：
# 創(chuàng)建唯一索引
CREATE TABLE book (
book_id INT ,
book_name VARCHAR (100) ,
#聲明索引，聲明有唯一索引的字段，在添加數(shù)據(jù)時(shí)，要保證唯一性，但是可以添加nu1l
UNIQUE INDEX uk_idx_bname (book_name))
;
show index from book;該語(yǔ)句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結(jié)構(gòu)
（3）主鍵索引
設(shè)定為主鍵后，數(shù)據(jù)庫(kù)會(huì)自動(dòng)建立主鍵索引，innodb為聚簇索引，語(yǔ)法：
CREATE TABLE book (
# 創(chuàng)建主鍵索引
book_id INT primary key,
book_name VARCHAR (100)
;
CREATE TABLE student (
id INT(10) UNSIGNED AUTO_INCREMENT ,
student_no VARCHAR(200),
student_name VARCHAR(200),
PRIMARY KEY(id)
);刪除主鍵索引：
ALTER TABLE student drop PRIMARY KEY ;
修改主鍵索引：
必須先刪除掉(drop)原索引，再新建(add)索引
針對(duì)salaries表emp_no字段創(chuàng)建索引idx_emp_no，查詢emp_no為10005, 使用強(qiáng)制索引。
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date)
);
create index idx_emp_no on salaries(emp_no);
select * from salaries force index(idx_emp_no)
where emp_no = 10005;（4）創(chuàng)建單列索引
引舉:
CREATE TABLE test2( id INT NOT NULL, name CHAR(50) NULL, INDEX single_idx_name(name(20)) );
該語(yǔ)句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結(jié)構(gòu)：
SHOW INDEX FROM test2 \G
（5）創(chuàng)建組合索引
舉例：創(chuàng)建表test3，在表中的id、name和age字段上建立組合索引，SQL語(yǔ)句如下：
CREATE TABLE test3( id INT(11) NOT NULL, name CHAR(30) NOT NULL, age INT(11) NOT NULL, info VARCHAR(255), INDEX multi_idx(id,name,age) );
該語(yǔ)句執(zhí)行完畢之后，使用SHOW INDEX 查看：
SHOW INDEX FROM test3 \G
在test3表中，查詢id和name字段，使用EXPLAIN語(yǔ)句查看索引的使用情況：
EXPLAIN SELECT * FROM test3 WHERE id=1 AND name='songhongkang' \G
可以看到，查詢id和name字段時(shí)，使用了名稱為MultiIdx的索引，如果查詢 (name, age) 組合或者單獨(dú)查詢name和age字段，會(huì)發(fā)現(xiàn)結(jié)果中possible_keys和key值為NULL, 并沒有使用在t3表中創(chuàng)建的索引進(jìn)行查詢。
針對(duì)如下表actor結(jié)構(gòu)創(chuàng)建索引： (注:在SQLite中,除了重命名表和在已有的表中添加列,ALTER TABLE命令不支持其他操作，mysql支持ALTER TABLE創(chuàng)建索引) CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update datetime NOT NULL); 對(duì)first_name創(chuàng)建唯一索引uniq_idx_firstname，對(duì)last_name創(chuàng)建普通索引idx_lastname alter table actor add unique index uniq_idx_firstname(first_name); alter table actor add index idx_lastname(last_name) 或 create unique index uniq_idx_firstname on actor(first_name); create index idx_lastname on actor(last_name);
（6）創(chuàng)建全文索引
FULLTEXT全文索引可以用于全文檢索，并且只為 CHAR 、VARCHAR 和 TEXT 列創(chuàng)建索引。索引總是對(duì)整個(gè)列進(jìn)行，不支持局部 (前綴) 索引。
舉例1：創(chuàng)建表test4，在表中的info字段上建立全文索引，SQL語(yǔ)句如下：
CREATE TABLE test4( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), FULLTEXT INDEX futxt_idx_info(info(50)) ) ENGINE=MyISAM;
在MySQL5.7及之后版本中可以不指定最后的ENGINE了，因?yàn)樵诖税姹局蠭nnoDB支持全文索引。
語(yǔ)句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結(jié)構(gòu)：
SHOW INDEX FROM test4 \G
由結(jié)果可以看到，info字段上已經(jīng)成功建立了一個(gè)名為futxt_idx_info的FULLTEXT索引。
舉例2：
CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR (200), body TEXT, FULLTEXT index (title, body) ) ENGINE = INNODB;
創(chuàng)建了一個(gè)給title和body字段添加全文索引的表。
舉例3：
CREATE TABLE `papers` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `content` text, PRIMARY KEY (`id`), FULLTEXT KEY `title` (`title`,`content`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
不同于like方式的的查詢：
SELECT * FROM papers WHERE content LIKE ‘%查詢字符串%’;
全文索引用match+against方式查詢：
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查詢字符串’);
明顯的提高查詢效率。
注意點(diǎn)
- 使用全文索引前，搞清楚版本支持情況；
- 全文索引比 like + % 快 N 倍，但是可能存在精度問題；
- 如果需要全文索引的是大量數(shù)據(jù)，建議先添加數(shù)據(jù)，再創(chuàng)建索引。
6.創(chuàng)建空間索引
空間索引創(chuàng)建中，要求空間類型的字段必須為 非空 。
舉例：創(chuàng)建表test5，在空間類型為GEOMETRY的字段上創(chuàng)建空間索引，SQL語(yǔ)句如下：
CREATE TABLE test5( geo GEOMETRY NOT NULL, SPATIAL INDEX spa_idx_geo(geo) ) ENGINE=MyISAM;
該語(yǔ)句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結(jié)構(gòu)：
SHOW INDEX FROM test5 \G
可以看到，test5表的geo字段上創(chuàng)建了名稱為spa_idx_geo的空間索引。注意創(chuàng)建時(shí)指定空間類型字段值的非空約束，并且表的存儲(chǔ)引擎為MyISAM。
2、在已經(jīng)存在的表上創(chuàng)建索引
在已經(jīng)存在的表中創(chuàng)建索引，可以使用ALTER TABLE語(yǔ)句或者CREATE INDEX語(yǔ)句。
1）使用ALTER TABLE語(yǔ)句創(chuàng)建索引
ALTER TABLE語(yǔ)句創(chuàng)建索引的基本語(yǔ)法如下：
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC] ALTER TABLE book ADD INDEX index_name(book_name); ALTER TABLE book ADD UNIQUE uk_idx_bname(book_name); ALTER TABLE book ADD UNIQUE mul_bid_na(book_name,author);
2）使用CREATE INDEX創(chuàng)建索引
CREATE INDEX語(yǔ)句可以在已經(jīng)存在的表上添加索引，在MySQL中，CREATE INDEX被映射到一個(gè)ALTER TABLE語(yǔ)句上，基本語(yǔ)法結(jié)構(gòu)為：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC] create 索引類型 索引名稱 on 表名(字段); create index idx_cmt on book(comment); create unique index idx_cmt on book(comment); create index idx_cmt on book(comment,author);
二、刪除索引
1）使用ALTER TABLE刪除索引
ALTER TABLE刪除索引的基本語(yǔ)法格式如下：
ALTER TABLE table_name DROP INDEX index_name;
2）使用DROP INDEX語(yǔ)句刪除索引
DROP INDEX刪除索引的基本語(yǔ)法格式如下：
DROP INDEX index_name ON table_name;
在需要大量刪除表數(shù)據(jù)，修改表數(shù)據(jù)時(shí)，可以考慮先刪除索引。等修改完數(shù)據(jù)之后再插入
AUTO_INCREMENT 約束字段的唯一索引不能被刪除
提示：刪除表中的列時(shí)，如果要?jiǎng)h除的列為索引的組成部分，則該列也會(huì)從索引中刪除。
如果組成索引的所有列都被刪除，則整個(gè)索引將被刪除。
到此這篇關(guān)于MySQL創(chuàng)建、刪除索引的操作代碼的文章就介紹到這了,更多相關(guān)mysql創(chuàng)建、刪除索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要為大家介紹了CentOS 7.2 Yum編譯安裝MySQL 5.6詳細(xì)代碼,具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-11-11
這篇文章主要為大家詳細(xì)介紹了mysql5.5.28安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-08-08
MySQL中UPDATE語(yǔ)句使用的實(shí)例教程
這篇文章主要介紹了MySQL中UPDATE語(yǔ)句使用的實(shí)例教程,包括UPDATE的使用中所容易引起的性能問題的分析,需要的朋友可以參考下2015-11-11
這篇文章主要介紹了在C#和MySQL中存取中文字符時(shí)避免亂碼的方法,主要還是老辦法先轉(zhuǎn)換成Unicode編碼,需要的朋友可以參考下2015-05-05
MySQL數(shù)據(jù)庫(kù)觸發(fā)器從小白到精通
觸發(fā)器是SQLserver提供給程序員和數(shù)據(jù)分析員來(lái)保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲(chǔ)過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動(dòng)，而是由事件來(lái)觸發(fā)，比如當(dāng)對(duì)一個(gè)表進(jìn)行操作時(shí)就會(huì)激活它執(zhí)行。觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等2022-03-03
在使用Navicat導(dǎo)入DBF文件到Oracle數(shù)據(jù)庫(kù)時(shí),如果遇到中文亂碼問題,需要在導(dǎo)入時(shí)指定正確的編碼格式,通常是GBK,否則,可能會(huì)出現(xiàn)部分字符亂碼的情況2025-12-12
這篇文章主要介紹了MySQL存儲(chǔ)過程概念、原理與常見用法,結(jié)合實(shí)例形式詳細(xì)分析了mysql存儲(chǔ)過程的概念、原理、創(chuàng)建、刪除、調(diào)用等各種常用技巧與相關(guān)注意事項(xiàng),需要的朋友可以參考下2019-07-07
這篇文章主要為大家介紹了幾處MySQL中易被我們誤會(huì)的地方，分享給大家，一來(lái)為了有趣，二來(lái)為了不讓自己踩坑。2016-07-07
從數(shù)據(jù)庫(kù)中取出最近三十天的數(shù)據(jù)并生成柱狀圖
從數(shù)據(jù)庫(kù)中取出最近三十天的數(shù)據(jù)并生成柱狀圖的代碼，需要的朋友可以參考下。2011-05-05
這篇文章主要介紹了MySQL學(xué)習(xí)筆記小結(jié),整體介紹了mysql的很多細(xì)節(jié)我呢體，學(xué)習(xí)優(yōu)化mysql的朋友可以參考下，需要的朋友可以參考下2016-05-05