https://www.nowcoder.com/feed/main/detail/5508a788622d4b778fdd3eb63ec25d15?sourceSSR=search

在mysql中有多种索引，有普通索引，全文索引，唯一索引，多列索引，小伙伴们可以通过不同的应用场景来进行索引的新建，在此列出三种新建索引的方法

mysql 中添加索引的三种方法

1.1 新建表中添加索引

① 普通索引

<table><tbody><tr><td><pre><span>1<br><span>2<br><span>3<br><span>4<br><span>5<br><span>6<br><span>7<br></span></span></span></span></span></span></span></pre></td><td><pre><span>create table t_dept(<br><span> no int not null primary key,<br><span> name varchar(20) null,<br><span> sex varchar(2) null,<br><span> info varchar(20) null,<br><span> index index_no(no)<br><span> )<br></span></span></span></span></span></span></span></pre></td></tr></tbody></table>

② 唯一索引

<table><tbody><tr><td><pre><span>1<br><span>2<br><span>3<br><span>4<br><span>5<br><span>6<br><span>7<br></span></span></span></span></span></span></span></pre></td><td><pre><span>create table t_dept(<br><span> no int not null primary key,<br><span> name varchar(20) null,<br><span> sex varchar(2) null,<br><span> info varchar(20) null,<br><span> unique index index_no(no)<br><span> ）<br></span></span></span></span></span></span></span></pre></td></tr></tbody></table>

③ 全文索引

<table><tbody><tr><td><pre><span>1<br><span>2<br><span>3<br><span>4<br><span>5<br><span>6<br></span></span></span></span></span></span></pre></td><td><pre><span>create table t_dept(<br><span> no int not null primary key,<br><span> name varchar(20) null,<br><span> sex varchar(2) null,<br><span> info varchar(20) null,<br><span> fulltext index index_no(no)<br></span></span></span></span></span></span></pre></td></tr></tbody></table>

④ 多列索引

<table><tbody><tr><td><pre><span>1<br><span>2<br><span>3<br><span>4<br><span>5<br><span>6<br><span>7<br></span></span></span></span></span></span></span></pre></td><td><pre><span>create table t_dept(<br><span> no int not null primary key,<br><span> name varchar(20) null,<br><span> sex varchar(2) null,<br><span> info varchar(20) null,<br><span> key index_no_name(no,name)<br><span> )<br></span></span></span></span></span></span></span></pre></td></tr></tbody></table>

1.2 在已建表中添加索引

① 普通索引

<table><tbody><tr><td><pre><span>1<br><span>2<br></span></span></pre></td><td><pre><span>create index index_name<br><span> on t_dept(name);<br></span></span></pre></td></tr></tbody></table>

② 唯一索引

<table><tbody><tr><td><pre><span>1<br><span>2<br></span></span></pre></td><td><pre><span>create unique index index_name<br><span> on t_dept(name);<br></span></span></pre></td></tr></tbody></table>

③ 全文索引

<table><tbody><tr><td><pre><span>1<br><span>2<br></span></span></pre></td><td><pre><span>create fulltext index index_name<br><span> on t_dept(name);<br></span></span></pre></td></tr></tbody></table>