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 1
mysql> alter table id add index (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 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    |          1 | id       |            1 | id          | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from idG
*************************** 1. row ***************************
        Table: id
   Non_unique: 1
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_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  Warnings: 0

mysql> alter table id add primary key (id);
ERROR 1068 (42000): Multiple primary key defined
mysql> alter table id drop index id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 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  Warnings: 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  Warnings: 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)