mysql 表设计规范讨论

CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL DEFAULT 0,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

有这样一张表，我知道的设计表规范其中一条就是字段尽量不要设为 null 。

现在这个表业务背景是这样的，会先录入数据，但是没有 user_id，user_id 是后续更新进去的，但是要保证 user_id 是唯一的。

如果不设置 user_id 为 null 的话，user_id 是没办法弄唯一键的。

那么这种背景下，有没有必要打破表设计规范约束？

我的想法：本场景可以打破字段不为 Null 的约束，把 user_id 设为 Null，这样从数据库层面设置唯一键，不仅安全，而且业务层，也能少一次查询，数据是否唯一。我觉得规范不是绝对的，就好像我们某些表多几个字段做数据冗余，不也是打破三范式的吗？

你们的看法呢？

第 1 条附言 · 2021-06-10 11:44:56 +08:00

我看到很多人让我用 user_id 做主键，这里对不起，是我贴出的表，省略了一些字段，造成了大家的误会。
是这样的，我这个表，是另外一张表，和 user 表是一对一关联关系来的，user_id 是逻辑外键来的，所以是不能作为主键和自增的。

36 条回复 • 2021-06-11 20:21:27 +08:00

lyusantu

2021-06-10 10:31:32 +08:00

规矩是人定的

cyril4free

2021-06-10 10:34:40 +08:00

单论 user_id 这个感觉可以代码层面做保证，不依赖数据库的唯一索引

T0m008

2021-06-10 10:36:45 +08:00

你知道为什么尽量不要设为 null 吗？如果知道了可能你也有答案了

devinww

2021-06-10 10:40:29 +08:00

业务需求多种多样，所以有的时候不会完全按照规范。
另外，尽量不要让数据库抛出异常，而在程序里就处理了。

ericls

2021-06-10 10:42:43 +08:00 via iPhone

解决什么问题
你有没有遇到同样的问题

DinnyXu

2021-06-10 10:44:35 +08:00

一个表设置 2 个唯一字段，你这不是多余么，你 id 都是自增唯一的了，为什么 user_id 也要求唯一

SuperXRay

2021-06-10 10:55:03 +08:00

@DinnyXu user_id 在该表中不允许重复，这种需求不是很正常

@brader 可以再挂一张表,user_id 放到那张表上。

SuperXRay

2021-06-10 10:56:12 +08:00

@brader 不要设唯一索引，程序判段是否重复，略过 Null

fatcube404

2021-06-10 10:58:32 +08:00

同意 DinnyXu 的说法
我建议直接拿 user_id 做主键自增唯一 not null unsigned
查询就直接用这个为 where 条件这不更能减少开销

limuyan44

2021-06-10 11:28:36 +08:00

字段不能设为 null,mysql 闲的搞个 null 出来，符合业务就行，哪有那么多硬要求。

DinnyXu

2021-06-10 11:35:29 +08:00

@SuperXRay 从你发表这个问题的时候就已经进入一个误区了，你说你的需求是 user_id 必须要唯一，然后你又说唯一的话不能为 null，很明显只有你勾选了该字段为主键，那么才会设定该字段不能为 null，一个表是可以有多个主键的，但是你能设置的主键就一个，其余的字段我们自己称之为主键，实质上也就是不重复而已，如果不重复的话，何必要采用 MySQL 的主键策略呢，可以通过代码层面去 set user_id 为 UUID，或者分布式 id，不也是达到了唯一的效果吗

xiaomingVTEX

2021-06-10 11:43:25 +08:00

直接默认插入一个 UUID 可以吗

xiaomingVTEX

2021-06-10 11:45:04 +08:00

后续更新的时候再更新为新的 user_id，如果有需要识别新录入的数据，加个状态字段

brader

2021-06-10 11:47:23 +08:00

@SuperXRay 那这样做的话，其实会多出一次查询，意思就是，这个场景下，多一次查询是值得的？

saulshao

2021-06-10 11:49:33 +08:00

是否允许为空是一个约束，不是所有的情况都不允许为空的，一般说的是尽量。
具体到你这个例子，最简单的办法显然就是允许为空就好。

liuhuansir

2021-06-10 12:39:02 +08:00

设计规范是尽量遵守，否则太教条了吧

xuanbg

2021-06-10 12:50:12 +08:00

为啥不能为空？你创建记录时没有的数据怎么就能够不为空？别理会那些破规则，该怎样就怎样。

leonme

2021-06-10 12:52:54 +08:00

@T0m008 可以简单说一说

raaaaaar

2021-06-10 13:00:13 +08:00 via Android

@xuanbg 不是不要理这“破规则”，“破规则”提出来有人遵守肯定有道理的，问题是你知不知道这个东西背后的原理是什么，为什么大家要这么做

Dragonphy

2021-06-10 13:05:32 +08:00

如果用户和密码都相同咋分辨用户，如果用户名唯一，这不是和 user_id 作用重复了吗，还是因为 user_id 做索引效率更好？

ericls

2021-06-10 13:06:44 +08:00

@raaaaaar 所以不要看规则，要看问题

brader

2021-06-10 13:52:41 +08:00

@xuanbg 这个你可以这样认为，这个表，是一个外部账号表，录入的时候，是没有人用的，没有绑定员工的，后续需要使用的时候，就绑一个员工 ID 上去，就这样。

sandman511

2021-06-10 14:05:56 +08:00

话说不能为空的话 “创建记录时没有的数据”应该插入什么

sheepzh

2021-06-10 14:29:37 +08:00

按你的业务场景，user_id 应该是在关联之后，才必须唯一吧。
我的思路：

第一种. 增加关联状态字段，唯一索引挂两个字段

CREATE TABLE `test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`user_id` nvarchar(36) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
`linked_flag` boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`link_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `test` (`user_id`,`name`) values (uuid(), ?);
UPDATE `test` set `user_id` = ?, `linked_flag` = TRUE where id = ? ;

或者

INSERT INTO `test` (`user_id`,`name`, `linked_flag`) values (?, ?, TRUE);

第二种. 把关联信息用新的关联表存储

CREATE TABLE `test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT ''
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `test_rel` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned NOT NULL,
`foreign_user_id` int NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `foreign_user_id` (`foreign_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

landers2015

2021-06-10 14:31:52 +08:00

完全赞同，很多东西都不是绝对绝对的，只能说是相对的绝对

pcbl

2021-06-10 14:41:54 +08:00 via Android

录入的时候直接 user_id=id 完事了

brader

2021-06-10 15:34:10 +08:00

@pcbl 这样肯定不行啊，user_id 有了值，就代表账号被绑定了啊，如果你说判断 user_id 和 id 相等，难道没有概率遇到，两张表的 id 刚好一样的情况？

pcbl

2021-06-10 15:50:13 +08:00 via Android

@brader 添加个绑定标记字段

linxiaojialin

2021-06-10 16:07:19 +08:00

@brader 可能 lz 还没吃透这些规范吧，帮你复述一遍哈， “设计表规范其中一条就是字段 [尽量] 不要设为 null”。
即使不存在唯一性的约束，单看这个字段，既然是一个外键，那要么是对应主表的 ID，要么是 null，这很合理呀。总比强行写个 0 更有逻辑性...

另外 null 的存在肯定是有意义，而这个所谓的规范好像是来源于某家公司内部的手册，不是国际标准吧。规矩都是人定的。

qq1340691923

2021-06-10 17:08:02 +08:00

我用 go 的话，会尽量少设计有 null 的字段

brader

2021-06-10 20:01:19 +08:00

@pcbl 和 id 一样自增，那也还会有问题，比如，是 1 2 3 4 5，你现在要给第五条记录绑定一个 ID=3 的用户，你会发现，user_id 重复了，是吧

brader

2021-06-10 20:03:45 +08:00

@linxiaojialin 这个不为 Null 的规范出现也是有一定道理的，我去搜过一些相关的文章，有很多都是说，null 列会影响索引的效果

akira

2021-06-10 20:33:13 +08:00

三范式是教学用的，实际生产环境不会去遵守的

zlowly

2021-06-11 01:59:49 +08:00

说真的，我从来没有见过哪个通用的设计表规范中说字段尽量不要设为 null 。
你只需要知道字段为 null 会出现哪些问题就足够了。除了对唯一索引有影响，通常它的存在会影响排序、统计等，例如大部分数据库 count,avg 等统计分析函数时会不计算 null 的记录，而 order 排序时 null 记录是排最前还是最后也是要注意？以上等等，如果你不清楚的话日后就有可能掉坑里，所以估计才会有字段尽量不要设为 null 的建议，但这并肯定不是通用设计规范。

LuckyLight

2021-06-11 10:31:55 +08:00

这个表的数据为什么要先插入，为什么不在有了 user_id 之后再插入呢

EminemW

2021-06-11 20:21:27 +08:00

空字符串