V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
brader
V2EX  ›  MySQL

mysql 表设计规范讨论

  •  
  •   brader · 2021-06-10 10:30:12 +08:00 · 3689 次点击
    这是一个创建于 1023 天前的主题,其中的信息可能已经有所发展或是发生改变。
    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
        1
    lyusantu  
       2021-06-10 10:31:32 +08:00
    规矩是人定的
    cyril4free
        2
    cyril4free  
       2021-06-10 10:34:40 +08:00
    单论 user_id 这个感觉可以代码层面做保证,不依赖数据库的唯一索引
    T0m008
        3
    T0m008  
       2021-06-10 10:36:45 +08:00
    你知道为什么尽量不要设为 null 吗?如果知道了可能你也有答案了
    devinww
        4
    devinww  
       2021-06-10 10:40:29 +08:00
    业务需求多种多样,所以有的时候不会完全按照规范。
    另外,尽量不要让数据库抛出异常,而在程序里就处理了。
    ericls
        5
    ericls  
       2021-06-10 10:42:43 +08:00 via iPhone
    解决什么问题
    你有没有遇到同样的问题
    DinnyXu
        6
    DinnyXu  
       2021-06-10 10:44:35 +08:00
    一个表设置 2 个唯一字段,你这不是多余么, 你 id 都是自增唯一的了,为什么 user_id 也要求唯一
    SuperXRay
        7
    SuperXRay  
       2021-06-10 10:55:03 +08:00
    @DinnyXu user_id 在该表中不允许重复,这种需求不是很正常

    @brader 可以再挂一张表,user_id 放到那张表上。
    SuperXRay
        8
    SuperXRay  
       2021-06-10 10:56:12 +08:00
    @brader 不要设唯一索引,程序判段是否重复,略过 Null
    fatcube404
        9
    fatcube404  
       2021-06-10 10:58:32 +08:00
    同意 DinnyXu 的说法
    我建议 直接拿 user_id 做主键 自增 唯一 not null unsigned
    查询就直接用这个为 where 条件 这不更能减少开销
    limuyan44
        10
    limuyan44  
       2021-06-10 11:28:36 +08:00
    字段不能设为 null,mysql 闲的搞个 null 出来,符合业务就行,哪有那么多硬要求。
    DinnyXu
        11
    DinnyXu  
       2021-06-10 11:35:29 +08:00
    @SuperXRay 从你发表这个问题的时候就已经进入一个误区了,你说你的需求是 user_id 必须要唯一,然后你又说唯一的话不能为 null,很明显只有你勾选了该字段为主键,那么才会设定该字段不能为 null,一个表是可以有多个主键的,但是你能设置的主键就一个,其余的字段我们自己称之为主键,实质上也就是不重复而已,如果不重复的话,何必要采用 MySQL 的主键策略呢,可以通过代码层面去 set user_id 为 UUID,或者分布式 id,不也是达到了唯一的效果吗
    xiaomingVTEX
        12
    xiaomingVTEX  
       2021-06-10 11:43:25 +08:00
    直接默认插入一个 UUID 可以吗
    xiaomingVTEX
        13
    xiaomingVTEX  
       2021-06-10 11:45:04 +08:00
    后续更新的时候再更新为新的 user_id,如果有需要识别新录入的数据,加个状态字段
    brader
        14
    brader  
    OP
       2021-06-10 11:47:23 +08:00
    @SuperXRay 那这样做的话,其实会多出一次查询,意思就是,这个场景下,多一次查询是值得的?
    saulshao
        15
    saulshao  
       2021-06-10 11:49:33 +08:00
    是否允许为空是一个约束,不是所有的情况都不允许为空的,一般说的是尽量。
    具体到你这个例子,最简单的办法显然就是允许为空就好。
    liuhuansir
        16
    liuhuansir  
       2021-06-10 12:39:02 +08:00
    设计规范是尽量遵守,否则太教条了吧
    xuanbg
        17
    xuanbg  
       2021-06-10 12:50:12 +08:00
    为啥不能为空?你创建记录时没有的数据怎么就能够不为空?别理会那些破规则,该怎样就怎样。
    leonme
        18
    leonme  
       2021-06-10 12:52:54 +08:00
    @T0m008 可以简单说一说
    raaaaaar
        19
    raaaaaar  
       2021-06-10 13:00:13 +08:00 via Android
    @xuanbg 不是不要理这“破规则”,“破规则”提出来有人遵守肯定有道理的,问题是你知不知道这个东西背后的原理是什么,为什么大家要这么做
    Dragonphy
        20
    Dragonphy  
       2021-06-10 13:05:32 +08:00
    如果用户和密码都相同咋分辨用户,如果用户名唯一,这不是和 user_id 作用重复了吗,还是因为 user_id 做索引效率更好?
    ericls
        21
    ericls  
       2021-06-10 13:06:44 +08:00
    @raaaaaar 所以不要看规则,要看问题
    brader
        22
    brader  
    OP
       2021-06-10 13:52:41 +08:00
    @xuanbg 这个你可以这样认为,这个表,是一个外部账号表,录入的时候,是没有人用的,没有绑定员工的,后续需要使用的时候,就绑一个员工 ID 上去,就这样。
    sandman511
        23
    sandman511  
       2021-06-10 14:05:56 +08:00
    话说不能为空的话 “创建记录时没有的数据”应该插入什么
    sheepzh
        24
    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
        25
    landers2015  
       2021-06-10 14:31:52 +08:00
    完全赞同,很多东西都不是绝对绝对的,只能说是相对的绝对
    pcbl
        26
    pcbl  
       2021-06-10 14:41:54 +08:00 via Android   ❤️ 1
    录入的时候直接 user_id=id 完事了
    brader
        27
    brader  
    OP
       2021-06-10 15:34:10 +08:00
    @pcbl 这样肯定不行啊,user_id 有了值,就代表账号被绑定了啊,如果你说判断 user_id 和 id 相等,难道没有概率遇到,两张表的 id 刚好一样的情况?
    pcbl
        28
    pcbl  
       2021-06-10 15:50:13 +08:00 via Android
    @brader 添加个绑定标记字段
    linxiaojialin
        29
    linxiaojialin  
       2021-06-10 16:07:19 +08:00
    @brader 可能 lz 还没吃透这些规范吧,帮你复述一遍哈, “设计表规范其中一条就是字段 [尽量] 不要设为 null”。
    即使不存在唯一性的约束,单看这个字段,既然是一个外键,那要么是对应主表的 ID,要么是 null,这很合理呀。总比强行写个 0 更有逻辑性...

    另外 null 的存在肯定是有意义,而这个所谓的规范好像是来源于某家公司内部的手册,不是国际标准吧。规矩都是人定的。
    qq1340691923
        30
    qq1340691923  
       2021-06-10 17:08:02 +08:00
    我用 go 的话,会尽量少设计有 null 的字段
    brader
        31
    brader  
    OP
       2021-06-10 20:01:19 +08:00
    @pcbl 和 id 一样自增,那也还会有问题,比如,是 1 2 3 4 5,你现在要给第五条记录绑定一个 ID=3 的用户,你会发现,user_id 重复了,是吧
    brader
        32
    brader  
    OP
       2021-06-10 20:03:45 +08:00
    @linxiaojialin 这个不为 Null 的规范出现也是有一定道理的,我去搜过一些相关的文章,有很多都是说,null 列会影响索引的效果
    akira
        33
    akira  
       2021-06-10 20:33:13 +08:00
    三范式是教学用的,实际生产环境不会去遵守的
    zlowly
        34
    zlowly  
       2021-06-11 01:59:49 +08:00
    说真的,我从来没有见过哪个通用的设计表规范中说字段尽量不要设为 null 。
    你只需要知道字段为 null 会出现哪些问题就足够了。除了对唯一索引有影响,通常它的存在会影响排序、统计等,例如大部分数据库 count,avg 等统计分析函数时会不计算 null 的记录,而 order 排序时 null 记录是排最前还是最后也是要注意?以上等等,如果你不清楚的话日后就有可能掉坑里,所以估计才会有字段尽量不要设为 null 的建议,但这并肯定不是通用设计规范。
    LuckyLight
        35
    LuckyLight  
       2021-06-11 10:31:55 +08:00
    这个表的数据为什么要先插入,为什么不在有了 user_id 之后再插入呢
    EminemW
        36
    EminemW  
       2021-06-11 20:21:27 +08:00
    空字符串
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5519 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 06:39 · PVG 14:39 · LAX 23:39 · JFK 02:39
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.