本文介绍了MySQL中如何为students表创建及管理索引，包括单一字段索引和组合索引，并展示了创建过程中的一些注意事项。
mysql>desc students;+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(64) | YES | UNI | NULL | |
| gender | varchar(12) | YES | | NULL | |
| dept_id | int(11) | NO | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.01sec)
mysql>create index idx_123 on students(sid,sname);
Query OK,0 rows affected (0.06sec)
Records:0 Duplicates: 0 Warnings: 0mysql>show create table students;+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students |CREATE TABLE `students` (
`sid`int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(64) DEFAULT NULL,
`gender` varchar(12) DEFAULT NULL,
`dept_id`int(11) NOT NULL,
PRIMARY KEY (`sid`),
UNIQUE KEY `idx_1` (`sname`),
KEY `for_1` (`dept_id`),
KEY `idx_123` (`sid`,`sname`),
CONSTRAINT `for_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql>create index idx_124 on students(sid);
Query OK,0 rows affected (0.12sec)
Records:0 Duplicates: 0 Warnings: 0mysql>create index idx_124 on students(sname);
ERROR1061 (42000): Duplicate key name 'idx_124'mysql>create index idx_125 on students(sname);
Query OK,0 rows affected (0.07sec)
Records:0 Duplicates: 0 Warnings: 0mysql>show create table students;+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students |CREATE TABLE `students` (
`sid`int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(64) DEFAULT NULL,
`gender` varchar(12) DEFAULT NULL,
`dept_id`int(11) NOT NULL,
PRIMARY KEY (`sid`),
UNIQUE KEY `idx_1` (`sname`),
KEY `for_1` (`dept_id`),
KEY `idx_123` (`sid`,`sname`),
KEY `idx_124` (`sid`),
KEY `idx_125` (`sname`),
CONSTRAINT `for_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql>create index idx_126 on students(sname);
Query OK,0 rows affected, 1 warning (0.07sec)
Records:0 Duplicates: 0 Warnings: 1mysql>show create table students;+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students |CREATE TABLE `students` (
`sid`int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(64) DEFAULT NULL,
`gender` varchar(12) DEFAULT NULL,
`dept_id`int(11) NOT NULL,
PRIMARY KEY (`sid`),
UNIQUE KEY `idx_1` (`sname`),
KEY `for_1` (`dept_id`),
KEY `idx_123` (`sid`,`sname`),
KEY `idx_124` (`sid`),
KEY `idx_125` (`sname`),
KEY `idx_126` (`sname`),
CONSTRAINT `for_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
