Biegral Blog

       创建表：

CREATE TABLE `tbtest` (    
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'student id',    
  `name` VARCHAR(32) NOT NULL COMMENT 'student name',    
  `score` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'student score'  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='student tb'

        看下：

mysql> show create table tbtest;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbtest | CREATE TABLE `tbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'student id',
  `name` varchar(32) NOT NULL COMMENT 'student name',
  `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'student score',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='student tb' |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

       显然：mysql主键是唯一索引。

       我们来为name创建索引， 如下：

mysql> ALTER TABLE `tbtest` ADD INDEX idx_name (`name`) ;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tbtest;                         
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                           |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbtest | CREATE TABLE `tbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'student id',
  `name` varchar(32) NOT NULL COMMENT 'student name',
  `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'student score',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='student tb' |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

        顺便来小结一下各类索引的创建：

       1. 添加PRIMARY KEY（主键索引， 一般来说， 如果设置为主键后， 它自动会成为索引， 不需要再设置） 

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ); 

       2. 添加UNIQUE(唯一索引) 

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) ;

       3. 添加INDEX(普通索引) 

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) ;

       4. 添加FULLTEXT(全文索引) 

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) ;

       5. 添加多列索引 (实际上就是捆绑索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );

       简单， 不多说。