Mysql中的索引
索引：为了加快查找速度普通索引: index唯一索引: unique index维一索引可以有多个主键索引: primary key 不能重复主键必定要维一一张表上只有一个主键全文索引: fulltext index查看索引:show index from table_name;建立索引：alter table table_name add index/unique/primary key/fulltex 索引名(列名) #索引名可省略， 不加时默认为当前列名建立主键:alter table table_name add primary key (列名);删除索引:alter table table_name drop index 索引名;
删除主键：
alter table table_name drop primary key;
全文索引:
alter table table_name add fulltext (列名)
全文索引用法:
select * from table_name match(索引名) against('关键字');
注意， 如果关键字太常见， 索引不是查找， 会认为是停止词。
mysql> create table id(id int, name text);Query OK, 0 rows affected (0.04 sec)mysql> show index from id;Empty set (0.00 sec)mysql> alter table id add index id;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1mysql> alter table id add index (id);Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Waings: 0mysql> show index from id;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| id| 1 | id|1 | id | A |NULL | NULL | NULL| YES | BTREE | ||+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)mysql> show index from id\G*************************** 1. row ***************************Table: idNon_unique: 1 Key_name: id Seq_in_index: 1 Column_name: idCollation: A Cardinality: NULL Sub_part: NULLPacked: NULL Null: YESIndex_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec)mysql> alter table id add primary key (id);Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Waings: 0mysql> alter table id add primary key (id);ERROR 1068 (42000): Multiple primary key definedmysql> alter table id drop index id;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Waings: 0
mysql> show index from id;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| id | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> alter table id drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Waings: 0
mysql> show index from id;
Empty set (0.00 sec)
--------------------------下面是全文索引的例子-------------------------------
mysql> create table text(id int primary key, `in` text);
Query OK, 0 rows affected (0.04 sec)
mysql> desc text;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| in | text | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into text('i love perl6 very much!');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''i love perl6 very much!')' at line 1
mysql> insert into text values ('i love perl6 very much!');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into text values (1,'i love perl6 very much!');
Query OK, 1 row affected (0.00 sec)
mysql> insert into text values (2,'www.0668sec.info');
Query OK, 1 row affected (0.00 sec)
mysql> select * from text;
+----+-------------------------+
| id | in |
+----+-------------------------+
| 1 | i love perl6 very much! |
| 2 | www.0668sec.info |
+----+-------------------------+
2 rows in set (0.00 sec)
mysql> alter table text add fulltext (id);
ERROR 1283 (HY000): Column 'id' cannot be part of FULLTEXT index
mysql> alter table text add fulltext (in);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'in)' at line 1
mysql> alter table text add fulltext (`in`);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Waings: 0
mysql> show index from text;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| text | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| text | 1 | in | 1 | in | NULL | NULL | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> select * from text match(`in`) against('sec');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match(`in`) against('sec')' at line 1
mysql> select * from text match(`in`) against('sec');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match(`in`) against('sec')' at line 1
mysql> select * from text where match(`in`) against('sec');
Empty set (0.00 sec)
mysql> select * from text where match(`in`) against('0668sec');
Empty set (0.00 sec)
mysql> select * from text where match(`in`) against('0668sec.info');
Empty set (0.00 sec)
mysql> insert into text values (2,'nubbs you team data2 metasploit');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> insert into text values (3,'nubbs you team data2 metasploit');
Query OK, 1 row affected (0.00 sec)
mysql> select * from text where match(`in`) against('metasploit');
+----+---------------------------------+
| id | in |
+----+---------------------------------+
| 3 | nubbs you team data2 metasploit |
+----+---------------------------------+
1 row in set (0.00 sec)
来源链接：https://www.cnblogs.com/perl6/p/7114673.html
版权声明：
1、JavaClub（https://www.javaclub.cn）以学习交流为目的，由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容，包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网，本站不享有版权，版权归原作者所有。
2、本站提供的内容仅用于个人学习、研究或欣赏，以及其他非商业性或非盈利性用途，但同时应遵守著作权法及其他相关法律的规定，不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容，请及时通知本站（javaclubcn@163.com），我们将第一时间核实后及时予以删除。
