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
yuann72
V2EX  ›  MySQL

这两种表设计, 用哪种好?

  •  
  •   yuann72 · 2021-04-20 11:37:57 +08:00 · 3538 次点击
    这是一个创建于 1073 天前的主题,其中的信息可能已经有所发展或是发生改变。

    有个规则表,需要判断规则适用于星期几
    是采用方式 1 这种反范式的方式,还是创建一张一对多关联表的方式

    方式 1

    CREATE TABLE `xx_rule` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `content` varchar(255) DEFAULT '' COMMENT '规则内容(这个字段不是重点)',
      `week` varcahr(255) DEFAULT '' COMMENT '(重点字段)规则适用于星期几;逗号分割,如果星期一到星期天都适用则填:1,2,3,4,5,6,7',
      PRIMARY KEY (`id`)
    );
    

    方式 2

    CREATE TABLE `xx_rule` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `content` varchar(255) DEFAULT '' COMMENT '规则内容(这个字段不是重点)',
      PRIMARY KEY (`id`)
    );
    
    CREATE TABLE `xx_rule_week` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `rule_id` int(11) DEFAULT 0 COMMENT '规则表 ID',
      `week` int(11) DEFAULT 0 COMMENT '规则适用于星期几;填 1 表示星期一',
      PRIMARY KEY (`id`)
    );
    
    22 条回复    2021-04-20 22:01:33 +08:00
    ChoateYao
        1
    ChoateYao  
       2021-04-20 11:44:46 +08:00   ❤️ 4
    用位运算来存储星期,当需要检索是否包含某个星期的时候,先通过代码计算出所有包含该星期的数值,然后进行 IN 操作。

    如果只是用于调试查询,可以在 SQL 中直接用位运算进行检索。
    yuann72
        2
    yuann72  
    OP
       2021-04-20 11:46:41 +08:00
    @ChoateYao 这个方法有点意思。就是如果直接查看数据库就没法直观看出这个数值表示星期几
    bluekz
        3
    bluekz  
       2021-04-20 11:47:16 +08:00
    你的业务需求有:
    “查询适合星期 5 的所有规则”吗?如果有,方式 1 怎么办?
    yuann72
        4
    yuann72  
    OP
       2021-04-20 11:50:10 +08:00
    @bluekz
    select * from xx_rule where FIND_IN_SET('5',week)

    select * from xx_rule where week like '%5%'
    ChoateYao
        5
    ChoateYao  
       2021-04-20 11:54:07 +08:00   ❤️ 1
    @yuann72 这是必然的,但是你可以用 bin 函数来解决这个问题,把 10 进制转换成二进制,然后看非 0 的位置这样子就能快速直观的了解到是否包含某个星期了,跟你第一个方案一样。
    yuann72
        6
    yuann72  
    OP
       2021-04-20 11:54:21 +08:00
    @bluekz

    一般每个代理商下的规则行数都是个位数, 经过 where agent_id=1 条件过滤后的行数是个位数,所以我认为性能不会太慢,我自己还是倾向方式一
    select * from xx_rule where agent_id=1 AND week like '%5%'

    完整一点的表结构是这样的: 多出来一个 agent_id
    CREATE TABLE `xx_rule` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `agent_id` int(11) DEFAULT 0 COMMENT '代理商的 ID',
    `content` varchar(255) DEFAULT '' COMMENT '规则内容(这个字段不是重点)',
    `week` varcahr(255) DEFAULT '' COMMENT '(重点字段)规则适用于星期几;逗号分割,如果星期一到星期天都适用则填:1,2,3,4,5,6,7',
    PRIMARY KEY (`id`)
    );
    markgor
        7
    markgor  
       2021-04-20 12:06:10 +08:00
    如果是答题的话,我觉得 1 是最佳的选择。
    如果是实际应用的话,我觉得 2 是最好的选择。

    当哪一天规则改变的时候,2 的扩展代价相对低。
    Rache1
        8
    Rache1  
       2021-04-20 12:43:02 +08:00
    就要看这个表增长了,如果表增长快,那第一种方案走不到索引上去,可就难受了
    EscYezi
        9
    EscYezi  
       2021-04-20 12:46:53 +08:00 via iPhone
    还有个方法是,建七个字段表示 1-7,比位运算看起来直观些,就是字段太多🌚
    pkupyx
        10
    pkupyx  
       2021-04-20 13:31:41 +08:00
    第一种就不能整个 byte,按 bit 来判断星期几么。。。
    raaaaaar
        11
    raaaaaar  
       2021-04-20 13:39:40 +08:00 via Android
    怎么说呢,其实两种都能行,主要看你对这个的操作还有表的规模,如果对规则内容操作得少的话,第二种应该要好点
    buster
        12
    buster  
       2021-04-20 13:40:17 +08:00
    实际存储可以使用 Bit 位或者 1,....,7 来做都行,倒是可以在从 DB 取到数据之后映射入实体对象类,新建一个属性(枚举类,list 。。。),针对这个数据再解析出星期的数据
    liuky
        13
    liuky  
       2021-04-20 13:41:20 +08:00
    第一方案, week 换乘 int, 周一到周日改成 2^n 表示(1,2,4,8,16,32,64,128), 然后按位(& | ^)进行运算就好了, 数据库也是支持位运算的
    liuky
        14
    liuky  
       2021-04-20 13:47:19 +08:00   ❤️ 1
    @liuky
    select * from xx_rule where week & 2 = 2 , 就可以查出是否具有 2 这个权限
    1 | 2 | 4 | 8 等于 15
    select * from xx_rule where week & 15 = 15 , 就可以查出是否具有 1 | 2 | 4 | 8 这个权限
    同理添加也是一样 update xx_rule set week = week | 2 就可以添加 2 这个权限
    maocat
        15
    maocat  
       2021-04-20 13:47:57 +08:00
    和上面一样的想法,七位二进制,1111111, 每一位为 1 表示对应的天数是存在的
    SjwNo1
        16
    SjwNo1  
       2021-04-20 14:02:13 +08:00
    如果你的操作仅限于单条数据判断,可以用二进制,否则 find_in_set 较好一点感觉
    play78
        17
    play78  
       2021-04-20 14:03:04 +08:00
    主要靠是否经常查询和变动。也可以写成这样
    ```
    create table xx_rule(
    id int,
    content varchar,
    sun int(2),
    mon int(2),
    tue int(2),
    wed int(2),
    thu int(2),
    fri int(2),
    sat int(2)
    );
    ```
    mlcq
        18
    mlcq  
       2021-04-20 14:51:04 +08:00
    @liuky #14 之前项目这样用过,靠谱
    bluekz
        19
    bluekz  
       2021-04-20 17:39:27 +08:00
    @yuann72
    一般每个代理商下的规则行数都是个位数, 经过 where agent_id=1 条件过滤后的行数是个位数,所以我认为性能不会太慢,我自己还是倾向方式一

    这样的话,方案 1 我是觉得没什么致命大错。
    就是不"方便扩展"。
    rayduan
        20
    rayduan  
       2021-04-20 18:25:09 +08:00
    大兄弟,
    EVERYDAY(0,"每天"),

    MONDAY(1,"星期一"),

    TUESDAY(2,"星期二"),

    WEDNESDAY(4,"星期三"),

    THURSDAY(8,"星期四"),

    FRIDAY(16,"星期五"),

    SATURDAY(32,"星期六"),

    SUNDAY(64,"星期天"),
    一个枚举解决,定时任务每天获取当前是星期几,然后和数据库与预算,匹配到就需要做任务
    nine
        21
    nine  
       2021-04-20 19:43:20 +08:00
    换 PostgreSQL 吧,array 类型解决你烦恼。
    akira
        22
    akira  
       2021-04-20 22:01:33 +08:00
    CREATE TABLE `xx_rule_week` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `rule_id` int(11) DEFAULT 0 COMMENT '规则表 ID',
    `sunday` int(11) DEFAULT 0 COMMENT '周日规则是否启用',
    `monday` int(11) DEFAULT 0 COMMENT 周一'规则是否启用',
    `tuesday` int(11) DEFAULT 0 COMMENT '周二规则是否启用',
    ...
    `satday` int(11) DEFAULT 0 COMMENT '周六规则是否启用',

    PRIMARY KEY (`id`)
    );
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3094 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 12:52 · PVG 20:52 · LAX 05:52 · JFK 08:52
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.