MYSQL 主键的那些 “有意思” 故事-优快云博客

文章讨论了MySQL中主键设计的一些常见问题，包括允许NULL的主键可能导致的数据混乱，空字符串作为主键值的隐患，以及复合主键引发的性能下降、死锁和更新异常。建议避免使用复合主键，并谨慎处理主键更新逻辑，以防止潜在问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群。

任何数据库在设计之初都有主键，没有主键的表是不完整的，尤其在MYSQL中，而MYSQL中的主键设计中，总有一些 “奇葩” 的行为，来让MYSQL 在运行中，因为主键的奇葩设计而导致各种各样的问题，我们今天来总结总结。

1 主键输入时可以是空的

下面的表，中的确id 是主键，并且是自增的，但是插入的时候，的确可以在插入的位置写入 NULL ，但这里会实际上在上面插入对应的自增的数据。

CREATE TABLE IF NOT EXISTS test_p (

id int AUTO_INCREMENT,

date datetime,

PRIMARY KEY (id)

);

insert into test_p (id,date) values (null,'2022-01-29');

insert into test_p (id,date) values (1,'2022-01-29');

实际上这里的有一个问题，就是为什么主键可以插入NULL，这里利用了一个在主键设计中的没有写 NOT NULL 的漏洞，导致写入的数据可以是NULL 因为NULL 不代表任何，是或否，代表不知道。

所以在编写程序的时候，不要在对自增的主键字段使用null 作为插入的值使用。

2 主键看上去可以是空的

在字段是varchar类型的情况下，输入的值不能为NULL，但可以为‘’，而由于 VARCHAR类型的特性，一个表中如果输入'' 也只能有一个，在输入各种空格，则会提示重复主键。

CREATE TABLE IF NOT EXISTS test_p (

id varchar(20),

date datetime,

PRIMARY KEY (id)

);

insert into test_p (id,date) values (null,'2022-01-29');

insert into test_p (id,date) values ('','2022-01-29');

insert into test_p (id,date) values (' ','2022-01-29');

而如果我们在往深入的去想，如果ID 采用的是 char类型实际上结果和varchar是一样的。

3 复合主键

很多MYSQL设计中表的主键被设计成复合主键，而复合主键的使用中会存在一些问题

问题1 性能问题

在MYSQL 中的数据组织方式是 B+TREE的方式，而主键是根节点的组织中的通过排序的方式来存放数据的一种数据存储组织方式，如果是一个键值作为组织方式还好，至少占用的字节数要少，而字节数大的情况下，势必比字节数小的主键在性能上有一定的差异，具体还需要压测进行判断。

同时下面的这个例子就是一个问题，在更新中如果

INSERT INTO test(id,pid,cid,date) VALUES (2,2,3,'2023-01-28') ON DUPLICATE KEY UPDATE cid = 3;

这样的情况下那么如果CID 中很多都是3 的情况下，那么必然这个插入的性能会极低。

问题 2 死锁问题

因为在MYSQL中不同的隔离级别会对数据库产生不同的影响，实际就是GAP LOCK ，next-key-looking 的问题，具体参见专业描述 RR RC 在范围查询和数据插入，更新中的不同隔离级别的不同影响问题。

这些都还好说，更糟糕的，在开发中对于复合主键中的，一个字段的更新的问题，这样会导致并发高的情况下，update与insert 产生偶发死锁的问题。

问题3

mysql 的on duplicate key update 语句失效的问题

这个问题产生在如果是多个字段做主键的情况下，在我们更新多个字段中的一个字段后，这个字段的唯一性会产生问题导致业务逻辑与原先的设定不一致的问题，这也是导致一些 on duplicate key update 在正常工作后，被认为不正常的情况时有发生。

举例

id pid cid 三个字段中 id 必须是唯一的情况下，但是建立主键是 id pid cid 三个字段联合的情况下，在这样的情况下，如果单独使用这样的方式很难保证 id 是唯一的逻辑属性，尤其在UPDATE 的情况下。

大家注意下图,这里的 on duplicate key update 的语句含义是(第二句)

update cid = 3 where id = 2 and pid = 2 and cid = 2 ，最后影响了2行数据，实际上就是 delete + insert （个人认为），尤其在MYSQL中对于性能的影响会较大。

综上所述，复合主键使用 on duplicate key update 应该小心注意逻辑上是否符合最初的设计要求，同时在MYSQL 的表设计中应尽量不使用复合主键来进行数据表的设计，避免一些未知问题的产生。

这里也留下一个问题，如果我不使用复合主键，而使用复合唯一索引，又会是什么故事。