mysql主键必定是唯一索引------顺便用实例说说索引的创建

来源：互联网发布：强制uefi安装 ubuntu 编辑：程序博客网时间：2024/05/18 03:33

创建表：

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: 0mysql> 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` );

简单，不多说。

阅读全文

0 0