1、唯一性索引创建条件：
唯一性索引创建一个约束条件要求索引列上的所有值必须是不同的，如果你尝试添加一行新数据，但该行的键值与已经存在的其他行的键值相同将会报错。如果为唯一性索引指定前缀值，前缀长度的列值要保证唯一。唯一性索引允许null值。
2、唯一非空索引_rowid的使用：
如果一个表有主键索引或者唯一非空索引，且索引由一个单独整型列类型组成，你可以在select语句使用_rowid来引用索引列，具体如下：
_rowid引用主键列如果主键列由单个整型列组成，如果存在主键列但是他不是由一个单独整型列组成，_rowid不能被使用。
另外，_rowid引用第一个有单个整型列组成的唯一非空索引列，如果不是，_rowid不能被使用。
3、创建测试表，并将列修改为唯一非空进行试验：
1
2
3
4
5
6
7
8
9
10
11
12
13
| create table unique_t(id int,name varchar(10));
alter table unique_t add unique index id_unique_ind(id);
alter table unique_t modify id int not null;
show create table unique_t;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| unique_t | CREATE TABLE `unique_t` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `id_unique_ind` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
插入测试数据：
1
2
3
4
5
6
7
8
9
10
11
| insert into unique_t values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(6,'e');
select *,_rowid from unique_t;
+----+------+--------+
| id | name | _rowid |
+----+------+--------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 6 | e | 6 |
+----+------+--------+
|
4、删除唯一性索引，修改id默认列可以为空：
1
2
3
4
5
6
7
8
9
10
11
| alter table unique_t drop index id_unique_ind;
alter table unique_t modify id int null;
show create table unique_t;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| unique_t | CREATE TABLE `unique_t` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
|
为id列创建主键索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| alter table unique_t add primary key(id);
show create table unique_t;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| unique_t | CREATE TABLE `unique_t` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
select *,_rowid from unique_t;
+----+------+--------+
| id | name | _rowid |
+----+------+--------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 6 | e | 6 |
+----+------+--------+
5 rows in set (0.00 sec)
|
给id添加主键约束和唯一非空约束，_rowid都可以引用对应列。
5、删除id主键，将name列设置为主键测试
alter table unique_t drop primary key;
alter table unique_t add primary key(name);
show index from unique_t;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| unique_t | 0 | PRIMARY | 1 | name | A | 5 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
show create table unique_t;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| unique_t | CREATE TABLE `unique_t` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+
root@db 07:11: [exchange_market]> select *,_rowid from unique_t;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'
|
非单个整型列的主键，_rowid无法使用。