mysql 表设计规范讨论

[brader]

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，这样从数据库层面设置唯一键，不仅安全，而且业务层，也能少一次查询，数据是否唯一。 我觉得规范不是绝对的，就好像我们某些表多几个字段做数据冗余，不也是打破三范式的吗？

你们的看法呢？

[ericls]

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

[brader]

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

[sandman511]

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

[sheepzh]

按你的业务场景，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]

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

[pcbl]

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

[brader]

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

[pcbl]

@brader 添加个绑定标记字段

[linxiaojialin]

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

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

[qq1340691923]

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

[brader]

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

[brader]

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

[akira]

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

[zlowly]

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

[LuckyLight]

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

[EminemW]

空字符串