mysql>desc students;+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00sec)
mysql>show create table students;+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students |CREATE TABLE `students` (
`id`int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql>create index idx_1 on students(name);
Query OK,0 rows affected (0.14sec)
Records:0 Duplicates: 0 Warnings: 0mysql>show create table students;+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students |CREATE TABLE `students` (
`id`int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_1` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01sec)
mysql> delete fromstudents;
Query OK,6 rows affected (0.06sec)
mysql>create unique index idx_2 on students(name);
Query OK,0 rows affected (0.04sec)
Records:0 Duplicates: 0 Warnings: 0mysql>show create table students;+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students |CREATE TABLE `students` (
`id`int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_2` (`name`),
KEY `idx_1` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql> insert into students(name) values('a');
Query OK,1 row affected (0.05sec)
mysql> insert into students(name) values('b');
Query OK,1 row affected (0.06sec)
mysql> insert into students(name) values('a');
ERROR1062 (23000): Duplicate entry 'a' for key 'idx_2'mysql> insert into students(id) values(20);
Query OK,1 row affected (0.10sec)
mysql> select * fromstudents;+----+------+
| id | name |
+----+------+
| 20 | NULL |
| 12 | a |
| 13 | b |
+----+------+
3 rows in set (0.01sec)
mysql> insert into students(id) values(21);
Query OK,1 row affected (0.09sec)
mysql> select * fromstudents;+----+------+
| id | name |
+----+------+
| 20 | NULL |
| 21 | NULL |
| 12 | a |
| 13 | b |
+----+------+
4 rows in set (0.00sec)
mysql> insert into students values(22,'');
Query OK,1 row affected (0.03sec)
mysql> insert into students values(23,'');
ERROR1062 (23000): Duplicate entry '' for key 'idx_2'mysql> alter table students add gender int;
Query OK,0 rows affected (0.18sec)
Records:0 Duplicates: 0 Warnings: 0mysql>desc students;+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | UNI | NULL | |
| gender | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.01sec)
mysql>create unique index idx_3 on students(gender);
Query OK,0 rows affected (0.12sec)
Records:0 Duplicates: 0 Warnings: 0mysql>show create table students;+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students |CREATE TABLE `students` (
`id`int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`gender`int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_2` (`name`),
UNIQUE KEY `idx_3` (`gender`),
KEY `idx_1` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql>drop index idx_2 on students;
Query OK,0 rows affected (0.13sec)
Records:0 Duplicates: 0 Warnings: 0mysql>drop index idx_3 on students;
Query OK,0 rows affected (0.04sec)
Records:0 Duplicates: 0 Warnings: 0mysql>create unique index idx_3 on students(name,gender);
Query OK,0 rows affected (0.17sec)
Records:0 Duplicates: 0 Warnings: 0mysql>show create table students;+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students |CREATE TABLE `students` (
`id`int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`gender`int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_3` (`name`,`gender`),
KEY `idx_1` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)
mysql> select * fromstudents;+----+------+--------+
| id | name | gender |
+----+------+--------+
| 20 | NULL | NULL |
| 21 | NULL | NULL |
| 22 | | NULL |
| 12 | a | NULL |
| 13 | b | NULL |
+----+------+--------+
5 rows in set (0.00sec)
mysql> insert into students values(23,'a',1);
Query OK,1 row affected (0.08sec)
mysql> update students set gender=1 where id=12;
ERROR1062 (23000): Duplicate entry 'a-1' for key 'idx_3'