主键,聚集索引,不是一个东西!

·  阅读 1745

练习一:建表时,可不可以不声明主键?

(1) create table user(

name varchar(10)

)engine=innodb;

(2) insert into user values_('shenjian')__;_

(3) insert into user values_('shenjian')__;_

画外音:建表时,不声明主键,插入两个相同的元素。

提问,连续执行上面的语句,执行结果是:

A 建表语句 (1) 报错

B 插入语句 (2) 报错

C 插入语句 (3) 报错

D 均不报错

实操如上,所以答案是【D 均不报错】

InnoDB 必须要有聚集索引,行记录按照聚集索引物理上排序。必须要有聚集索引,并不代表一定要有主键。

(1)如果定义了主键,主键就是聚集索引;

(2)如果没有定义主键,第一个非空 (not null) 且唯一 (unique) 列是聚集索引;

(3)如果没有符合条件的列,会自动创建一个隐藏的 row-id 作为聚集索引;

画外音:本例就属于第三种情况。

练习二:建表时,可不可以不声明主键非空?

(1) create table user(

id int,

name varchar(10),

primary key(id)

)engine=innodb;

(2) insert into user(name) values('shenjian');

(3) insert into user(name) values('shenjian');

画外音:建表时,不声明非空,插入两个相同的元素。

提问,连续执行上面的语句,执行结果是:

A 建表语句 (1) 报错

B 插入语句 (2) 报错

C 插入语句 (3) 报错

_D 均不报错
_

实操如上,所以答案是:【C 插入语句 (3) 报错】

建表语句可以不主动设置主键非空(上例中的 id),InnoDB 会自动添加非空 (not null) 且唯一 (unique) 的限制。

使用 show create table 查看,会发现主键列要求非空,且默认值是 0,因此:

insert into user(name) values('shenjian');

id 默认为 0,第一次插入成功,第二次插入主键冲突。
_画外音:_这一题很多人答错。

练习三:建表时,可不可以选择多个字段做主键?

(1) create table user(

id int not null,

name varchar(10) not null,

primary key(id, name)

)engine=innodb;

(2) insert into user values(1, 'shenjian');

(3) insert into user values(1, 'zhangsan');

(4) insert into user values(2, 'shenjian');

画外音:建表时,声明联合主键 (a,b),插入若干元素,有些 a 重复,有些 b 重复。

提问,连续执行上面的语句,执行结果是:

A 建表语句 (1) 报错

B 插入语句 (2) 报错

C 插入语句 (3) 报错

D 插入语句 (3) 报错

E 均不报错

实操如上,所以答案是:【E 均不报错】

这就是所谓的联合主键,不要求每个列唯一,而要求联合主键各个列的 “组合唯一”。

画外音:联合索引,联合主键,类似的。

练习四:可不可以主动插入自增主键?

(1) create table user(

id int auto_increment,

name varchar(10) not null,

primary key(id)

)engine=innodb;

(2) insert into user(name) values('shenjian');

(3) insert into user_(id, name)_ values_(10,'shenjian')__;_

(4) insert into user(name) values('shenjian');

画外音:建表时,自增 ID 为主键,插入若干元素,有些包含自增 ID,有些不包含。

提问,连续执行上面的语句,执行结果是:

A 建表语句 (1) 报错

B 插入语句 (2) 报错

C 插入语句 (3) 报错

D 插入语句 (3) 报错

E 均不报错

实操如上,所以答案是:【E 均不报错】

自增 ID 经常被用来作为主键,插入行记录时,不指定值,InnoDB 自动增加。同时,也可以手动指定值:

(1)手动指定的值与已有值重复,则主键冲突;

(2)与已有值不冲突,则插入成功;

(3)未来不指定值插入,会从手动插入值往后继续递增;

如上例,手动插入 10 后,未来插入的行会从 11 开始,自增 ID 中间出现空洞。

练习五:建表时,可不可以使用联合自增主键?

(1) create table user(

id int auto_increment,

name varchar(10) not null,

primary key(name, id)

)engine=innodb;

(2) insert into user(name) values('shenjian');

(3) insert into user_(id, name)_ values_(10,'shenjian');_

(4) insert into user(name) values('shenjian');

画外音:建表时,声明联合主键 (a,b),并且有一个是自增 ID,插入若干元素,包含自增 ID,有些不包含。

提问,连续执行上面的语句,执行结果是:

A 建表语句 (1) 报错

B 插入语句 (2) 报错

C 插入语句 (3) 报错

D 插入语句 (3) 报错

_E 均不报错
_

实操如上,所以答案是:【A 建表语句 (1) 报错】

这里不是指自增 ID 不能作为联合主键,而是如果作为联合主键,必须放在第一列。

primary key(name, id_)_

改为

primary key(id, name)

就可以执行成功了。
_画外音:_这一题很多人答错。

总结

一:InnoDB 建表时,可不可以不声明主键?

可以不声明主键,但必须要有聚集索引:

(1)有主键,主键是聚集索引;

(2)没有主键,首个非空唯一列是聚集索引;

(3)没有符合条件的列,row-id 是聚集索引;

主键和聚集索引不是一个东西,不要混淆。

二:InnoDB 建表时,可不可以不声明主键非空?

可以不声明主键非空,会自动加上非空限制。

三:InnoDB 建表时,可不可以选择多个字段做主键?

可以使用联合主键,组合列唯一即可。

四:InnoDB 插入时,可不可以主动插入自增主键?

可以指定自增列的值,但可能导致空洞。

五:InnoDB 建表时,可不可以使用联合自增主键?

可以,但自增 ID 必须在联合主键的第一列。

希望大家对主键有更系统性的认识。

架构师之路 - 分享技术思路

相关文章

缓冲池 (buffer pool),彻底懂了!
写缓冲 (change buffer),彻底懂了!

【DCEEA】,你答对了吗?

分类:
后端
标签:
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改