MySQL創(chuàng)建、刪除索引的操作代碼
一、創(chuàng)建索引
MySQL支持多種方法在單個或多個列上創(chuàng)建索引：
- 在創(chuàng)建表的定義語句CREATE TABLE中指定索引列
- 使用ALTER TABLE語句在存在的表上創(chuàng)建索引
- 或者使用CREATE INDEX語句在已存在的表上添加索引
1、創(chuàng)建表時創(chuàng)建索引
使用CREATE TABLE創(chuàng)建表時，除了可以定義列的數據類型外，還可以定義主鍵約束、外鍵約束或者唯一性約束，而不論創(chuàng)建哪種約束，在定義約束的同時，相當于在指定列上創(chuàng)建了一個索引。
舉例：
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)建表時，創(chuàng)建索引的話，基本語法格式如下：
CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
- UNIQUE 、 FULLTEXT 和 SPATIAL 為可選參數，分別表示唯一索引、全文索引和空間索引；
- INDEX 與 KEY 為同義詞，兩者的作用相同，用來指定創(chuàng)建索引；
- index_name 指定索引的名稱，為可選參數，如果不指定，那么MySQL 默認 col_name 為索引名；
- col_name 為需要創(chuàng)建索引的字段列，該列必須從數據表中定義的多個列中選擇；
- length 為可選參數，表示索引的長度，只有字符串類型的字段，才能指定索引長度；
- ASC 或 DESC 指定升序或者降序的索引值存儲。
（1）創(chuàng)建普通索引
在book表中的book_name字段上建立普通索引，SQL語句如下：
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) ,
#聲明索引，聲明有唯一索引的字段，在添加數據時，要保證唯一性，但是可以添加nu1l
UNIQUE INDEX uk_idx_bname (book_name))
;
show index from book;該語句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結構
（3）主鍵索引
設定為主鍵后，數據庫會自動建立主鍵索引，innodb為聚簇索引，語法：
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)索引
針對salaries表emp_no字段創(chuàng)建索引idx_emp_no，查詢emp_no為10005, 使用強制索引。
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)) );
該語句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結構：
SHOW INDEX FROM test2 \G
（5）創(chuàng)建組合索引
舉例：創(chuàng)建表test3，在表中的id、name和age字段上建立組合索引，SQL語句如下：
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) );
該語句執(zhí)行完畢之后，使用SHOW INDEX 查看：
SHOW INDEX FROM test3 \G
在test3表中，查詢id和name字段，使用EXPLAIN語句查看索引的使用情況：
EXPLAIN SELECT * FROM test3 WHERE id=1 AND name='songhongkang' \G
可以看到，查詢id和name字段時，使用了名稱為MultiIdx的索引，如果查詢 (name, age) 組合或者單獨查詢name和age字段，會發(fā)現(xiàn)結果中possible_keys和key值為NULL, 并沒有使用在t3表中創(chuàng)建的索引進行查詢。
針對如下表actor結構創(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); 對first_name創(chuàng)建唯一索引uniq_idx_firstname，對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)建索引。索引總是對整個列進行，不支持局部 (前綴) 索引。
舉例1：創(chuàng)建表test4，在表中的info字段上建立全文索引，SQL語句如下：
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了，因為在此版本中InnoDB支持全文索引。
語句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結構：
SHOW INDEX FROM test4 \G
由結果可以看到，info字段上已經成功建立了一個名為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)建了一個給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 (‘查詢字符串’);
明顯的提高查詢效率。
注意點
- 使用全文索引前，搞清楚版本支持情況；
- 全文索引比 like + % 快 N 倍，但是可能存在精度問題；
- 如果需要全文索引的是大量數據，建議先添加數據，再創(chuàng)建索引。
6.創(chuàng)建空間索引
空間索引創(chuàng)建中，要求空間類型的字段必須為 非空 。
舉例：創(chuàng)建表test5，在空間類型為GEOMETRY的字段上創(chuàng)建空間索引，SQL語句如下：
CREATE TABLE test5( geo GEOMETRY NOT NULL, SPATIAL INDEX spa_idx_geo(geo) ) ENGINE=MyISAM;
該語句執(zhí)行完畢之后，使用SHOW CREATE TABLE查看表結構：
SHOW INDEX FROM test5 \G
可以看到，test5表的geo字段上創(chuàng)建了名稱為spa_idx_geo的空間索引。注意創(chuàng)建時指定空間類型字段值的非空約束，并且表的存儲引擎為MyISAM。
2、在已經存在的表上創(chuàng)建索引
在已經存在的表中創(chuàng)建索引，可以使用ALTER TABLE語句或者CREATE INDEX語句。
1）使用ALTER TABLE語句創(chuàng)建索引
ALTER TABLE語句創(chuàng)建索引的基本語法如下：
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語句可以在已經存在的表上添加索引，在MySQL中，CREATE INDEX被映射到一個ALTER TABLE語句上，基本語法結構為：
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刪除索引的基本語法格式如下：
ALTER TABLE table_name DROP INDEX index_name;
2）使用DROP INDEX語句刪除索引
DROP INDEX刪除索引的基本語法格式如下：
DROP INDEX index_name ON table_name;
在需要大量刪除表數據，修改表數據時，可以考慮先刪除索引。等修改完數據之后再插入
AUTO_INCREMENT 約束字段的唯一索引不能被刪除
提示：刪除表中的列時，如果要刪除的列為索引的組成部分，則該列也會從索引中刪除。
如果組成索引的所有列都被刪除，則整個索引將被刪除。
到此這篇關于MySQL創(chuàng)建、刪除索引的操作代碼的文章就介紹到這了,更多相關mysql創(chuàng)建、刪除索引內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要為大家介紹了CentOS 7.2 Yum編譯安裝MySQL 5.6詳細代碼,具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-11-11
這篇文章主要為大家詳細介紹了mysql5.5.28安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-08-08
這篇文章主要介紹了MySQL中UPDATE語句使用的實例教程,包括UPDATE的使用中所容易引起的性能問題的分析,需要的朋友可以參考下2015-11-11
這篇文章主要介紹了在C#和MySQL中存取中文字符時避免亂碼的方法,主要還是老辦法先轉換成Unicode編碼,需要的朋友可以參考下2015-05-05
觸發(fā)器是SQLserver提供給程序員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊的存儲過程，它的執(zhí)行不是由程序調用，也不是手工啟動，而是由事件來觸發(fā)，比如當對一個表進行操作時就會激活它執(zhí)行。觸發(fā)器經常用于加強數據的完整性約束和業(yè)務規(guī)則等2022-03-03
在使用Navicat導入DBF文件到Oracle數據庫時,如果遇到中文亂碼問題,需要在導入時指定正確的編碼格式,通常是GBK,否則,可能會出現(xiàn)部分字符亂碼的情況2025-12-12
這篇文章主要介紹了MySQL存儲過程概念、原理與常見用法,結合實例形式詳細分析了mysql存儲過程的概念、原理、創(chuàng)建、刪除、調用等各種常用技巧與相關注意事項,需要的朋友可以參考下2019-07-07
這篇文章主要為大家介紹了幾處MySQL中易被我們誤會的地方，分享給大家，一來為了有趣，二來為了不讓自己踩坑。2016-07-07
從數據庫中取出最近三十天的數據并生成柱狀圖的代碼，需要的朋友可以參考下。2011-05-05
這篇文章主要介紹了MySQL學習筆記小結,整體介紹了mysql的很多細節(jié)我呢體，學習優(yōu)化mysql的朋友可以參考下，需要的朋友可以參考下2016-05-05