例： 创建一个表存放服务器允许或拒绝的IP和port，但记录中IP和port要唯一。
MariaDB [book]> create table firewall(host varchar(15) not null,port smallint(4) not null,access enum("deny","allow") not null,primary key(host,port)); # 用host和port列作复合索引 Query OK, 0 rows affected (0.11 sec) MariaDB [book]> insert into firewall values("192.168.81.100",22,"deny"); Query OK, 1 row affected (0.02 sec) MariaDB [book]> select * from firewall; +---------------+------+--------+ | host | port | access | +---------------+------+--------+ | 192.168.81.100 | 22 | deny | +---------------+------+--------+ 1 row in set (0.00 sec) MariaDB [book]> insert into firewall values("192.168.81.101",22,"allow"); Query OK, 1 row affected (0.04 sec) MariaDB [book]> insert into firewall values("192.168.81.100",22,"deny"); # 插入数据时，IP和port要唯一，否则会报错 ERROR 1062 (23000): Duplicate entry '192.168.81.100-22' for key 'PRIMARY' MariaDB [book]> select * from firewall; +----------------+------+--------+ | host | port | access | +----------------+------+--------+ | 192.168.81.100 | 22 | deny | | 192.168.81.101 | 22 | allow | +----------------+------+--------+ 2 rows in set (0.01 sec) MariaDB [book]> desc firewall; # 查看索引 +--------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+-------+ | host | varchar(15) | NO | PRI | NULL | | | port | smallint(4) | NO | PRI | NULL | | | access | enum('deny','allow') | NO | | NULL | | +--------+----------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)建表的时候如果加各种索引，顺序如下：
create table 表名（字段定义，PRIMARYKEY (`bId`),UNIQUE KEY `bi` (`bImg`),KEY `bn` (`bName`),KEY `ba` (`author`)） 5. 全文索引 (FULLTEXT INDEX)全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。
它能够利用"分词技术"等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果
mysql 在数据量较大的情况下，高并发连接的情况下:
select 语句 where bName like '%网%' 使用% 和 _ 通配符进行查找，因为索引里面保存的是一堆指针，不会保存具体内容，只能进行全表扫描，造成数据库压力大全文索引只能用在varchar和text字段中
5.1 创建全文索引 5.1.1 方法一：创建表时创建语法：
create table 表名（ 列定义， fulltext key 索引名 （字段）; ） 5.1.2 方法二：修改表时添加语法：
alter table 表名 add fulltext 索引名 （字段）;例子：
MariaDB [book]> show create table books; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | books | CREATE TABLE `books` ( `bId` int(4) NOT NULL AUTO_INCREMENT, `bName` varchar(255) DEFAULT NULL, `bTypeId` enum('1','2','3','4','5','6','7','8','9','10') DEFAULT NULL, `publishing` varchar(255) DEFAULT NULL, `price` int(4) DEFAULT NULL, `pubDate` date DEFAULT NULL, `author` varchar(30) DEFAULT NULL, `ISBN` varchar(255) DEFAULT NULL, PRIMARY KEY (`bId`) ) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) MariaDB [book]> alter table books add fulltext index_bName (`bName`); Query OK, 43 rows affected (0.04 sec) Records: 43 Duplicates: 0 Warnings: 0 MariaDB [book]> desc books; +------------+------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------------------------------------+------+-----+---------+----------------+ | bId | int(4) | NO | PRI | NULL | auto_increment | | bName | varchar(255) | YES | MUL | NULL | | | bTypeId | enum('1','2','3','4','5','6','7','8','9','10') | YES | | NULL | | | publishing | varchar(255) | YES | | NULL | | | price | int(4) | YES | | NULL | | | pubDate | date | YES | | NULL | | | author | varchar(30) | YES | | NULL | | | ISBN | varchar(255) | YES | | NULL | | +------------+------------------------------------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) MariaDB [book]> alter table books drop key index_bName; Query OK, 43 rows affected (0.03 sec) Records: 43 Duplicates: 0 Warnings: 0 MariaDB [book]> desc books; +------------+------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------------------------------------+------+-----+---------+----------------+ | bId | int(4) | NO | PRI | NULL | auto_increment | | bName | varchar(255) | YES | | NULL | | | bTypeId | enum('1','2','3','4','5','6','7','8','9','10') | YES | | NULL | | | publishing | varchar(255) | YES | | NULL | | | price | int(4) | YES | | NULL | | | pubDate | date | YES | | NULL | | | author | varchar(30) | YES | | NULL | | | ISBN | varchar(255) | YES | | NULL | | +------------+------------------------------------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) MariaDB [book]> alter table books add fulltext index_bName (`bName`,`publishing`); Query OK, 43 rows affected (0.02 sec) Records: 43 Duplicates: 0 Warnings: 0 MariaDB [book]> desc books; +------------+------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------------------------------------+------+-----+---------+----------------+ | bId | int(4) | NO | PRI | NULL | auto_increment | | bName | varchar(255) | YES | MUL | NULL | | | bTypeId | enum('1','2','3','4','5','6','7','8','9','10') | YES | | NULL | | | publishing | varchar(255) | YES | | NULL | | | price | int(4) | YES | | NULL | | | pubDate | date | YES | | NULL | | | author | varchar(30) | YES | | NULL | | | ISBN | varchar(255) | YES | | NULL | | +------------+------------------------------------------------+------+-----+---------+----------------+ 8 rows in set (0.01 sec) MariaDB [book]> alter table books drop key index_bName; Query OK, 43 rows affected (0.02 sec) Records: 43 Duplicates: 0 Warnings: 0 MariaDB [book]> desc books; +------------+------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------------------------------------+------+-----+---------+----------------+ | bId | int(4) | NO | PRI | NULL | auto_increment | | bName | varchar(255) | YES | | NULL | | | bTypeId | enum('1','2','3','4','5','6','7','8','9','10') | YES | | NULL | | | publishing | varchar(255) | YES | | NULL | | | price | int(4) | YES | | NULL | | | pubDate | date | YES | | NULL | | | author | varchar(30) | YES | | NULL | | | ISBN | varchar(255) | YES | | NULL | | +------------+------------------------------------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) MariaDB [book]> alter table books add fulltext index_bName (`publishing`); Query OK, 43 rows affected (0.02 sec) Records: 43 Duplicates: 0 Warnings: 0 MariaDB [book]> desc books; +------------+------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------------------------------------+------+-----+---------+----------------+ | bId | int(4) | NO | PRI | NULL | auto_increment | | bName | varchar(255) | YES | | NULL | | | bTypeId | enum('1','2','3','4','5','6','7','8','9','10') | YES | | NULL | | | publishing | varchar(255) | YES | MUL | NULL | | | price | int(4) | YES | | NULL | | | pubDate | date | YES | | NULL | | | author | varchar(30) | YES | | NULL | | | ISBN | varchar(255) | YES | | NULL | | +------------+------------------------------------------------+------+-----+---------+----------------+ 8 rows in set (0.01 sec) 5.2 删除全文索引 MariaDB [book]> alter table books drop key index_bName; Query OK, 39 rows affected (0.02 sec) Records: 39 Duplicates: 0 Warnings: 0