优化 invite_codes 表的 SQL 创建语句

在这里插入图片描述

-- auto-generated definition
create table invite_codes
(
    id                 int auto_increment
        primary key,
    invite_code        varchar(6)                                                       not null comment '邀请码，6位整数，确保在有效期内唯一',
    invitor            int                                                              null comment '邀请人的ID，对应admin表中的id字段，表示生成该邀请码的管理员或用户',
    invite_level       tinyint                                                          null comment '邀请层级，对应admin表中level字段的值，表示邀请人的层级',
    generated_date     datetime                               default CURRENT_TIMESTAMP not null comment '生成时间，默认为当前时间',
    expire_time        datetime                                                         null comment '失效时间，可为空',
    remark             text                                                             null comment '备注信息，可为空',
    status             enum ('effective', 'expired', 'bound') default 'effective'       not null comment '邀请码状态，默认为 "effective"',
    bound_phone        varchar(20)                                                      null comment '绑定的手机号，对应admin表中的phone字段，表示与邀请码关联的管理员或用户的手机号',
    weixin_nickname    varchar(255)                                                     null comment '微信用户的昵称，对应user表中的nickname字段，表示与邀请码关联的用户的微信昵称',
    weixin_headimg     varchar(255)                                                     null comment '微信用户的头像URL，对应user表中的avatar字段，表示与邀请码关联的用户的微信头像URL',
    bound_wx_uid       varchar(255)                                                     null comment '微信用户的唯一标识符，对应user表中的weChatUnionId字段，表示与邀请码关联的用户的微信唯一标识符',
    admin_id           int                                                              null comment '管理员ID，关联admin表，可为空',
    user_id            int                                                              null comment '用户ID，关联user表，表示与邀请码关联的用户',
    is_locked          tinyint(1)                             default 0                 not null comment '是否锁定，0表示未锁定，1表示已锁定，默认为0',
    created_date       timestamp                              default CURRENT_TIMESTAMP not null comment '记录创建的时间戳，默认为当前时间',
    last_modified_date timestamp                                                        null on update CURRENT_TIMESTAMP comment '记录最后更新的时间戳，默认为NULL，更新时自动设置为当前时间',
    constraint code
        unique (invite_code),
    constraint invite_codes_ibfk_1
        foreign key (admin_id) references productqualification.admin (id),
    constraint invite_codes_ibfk_2
        foreign key (invitor) references productqualification.admin (id),
    constraint invite_codes_ibfk_3
        foreign key (user_id) references productqualification.user (id)
)
    collate = utf8mb4_unicode_ci;

create index admin_id
    on invite_codes (admin_id);

create index invitor
    on invite_codes (invitor);

create index user_id
    on invite_codes (user_id);

好的，我来根据你提供的图片和 SQL 创建语句，详细解释一下 SQL 审核的优化建议，并给出相应的修改方案。

图片内容解读:

图片中展示的是一份 SQL 审核的优化建议，主要分为两个部分：

建议改进 (绿色标识):
1. 字段建议增加备注说明：id
2. invite_codes 表建议增加备注说明
潜在问题 (黄色标识):
1. invite_codes 表不建议使用外键
2. status 字段的类型 enum 不建议使用，建议用 tinyint/char 代替
3. id 是自增字段，建议增加无符号属性 (unsigned)
4. invite_codes 表的校验集建议为： utf8_general_ci | utf8_bin | utf8mb4_general_ci | utf8mb4_bin

针对性优化方案：

根据以上建议和提供的 CREATE TABLE 语句，我给出优化后的 SQL：

CREATE TABLE `invite_codes` (
    `id` INT UNSIGNED AUTO_INCREMENT COMMENT '主键ID，自增长',
    `invite_code` VARCHAR(6) NOT NULL COMMENT '邀请码，6位整数，确保在有效期内唯一',
    `invitor` INT NULL COMMENT '邀请人的ID，对应admin表中的id字段，表示生成该邀请码的管理员或用户',
    `invite_level` TINYINT NULL COMMENT '邀请层级，对应admin表中level字段的值，表示邀请人的层级',
    `generated_date` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '生成时间，默认为当前时间',
    `expire_time` DATETIME NULL COMMENT '失效时间，可为空',
    `remark` TEXT NULL COMMENT '备注信息，可为空',
    `status` TINYINT NOT NULL DEFAULT 0 COMMENT '邀请码状态，0表示有效，1表示已过期，2表示已绑定',
    `bound_phone` VARCHAR(20) NULL COMMENT '绑定的手机号，对应admin表中的phone字段，表示与邀请码关联的管理员或用户的手机号',
    `weixin_nickname` VARCHAR(255) NULL COMMENT '微信用户的昵称，对应user表中的nickname字段，表示与邀请码关联的用户的微信昵称',
    `weixin_headimg` VARCHAR(255) NULL COMMENT '微信用户的头像URL，对应user表中的avatar字段，表示与邀请码关联的用户的微信头像URL',
    `bound_wx_uid` VARCHAR(255) NULL COMMENT '微信用户的唯一标识符，对应user表中的weChatUnionId字段，表示与邀请码关联的用户的微信唯一标识符',
    `admin_id` INT NULL COMMENT '管理员ID，关联admin表，可为空',
    `user_id` INT NULL COMMENT '用户ID，关联user表，表示与邀请码关联的用户',
    `is_locked` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否锁定，0表示未锁定，1表示已锁定，默认为0',
    `created_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '记录创建的时间戳，默认为当前时间',
    `last_modified_date` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最后更新的时间戳，默认为NULL，更新时自动设置为当前时间',
     PRIMARY KEY (`id`),
    UNIQUE KEY `code` (`invite_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请码表';

CREATE INDEX `admin_id` ON `invite_codes` (`admin_id`);
CREATE INDEX `invitor` ON `invite_codes` (`invitor`);
CREATE INDEX `user_id` ON `invite_codes` (`user_id`);

详细修改说明：

id 字段添加备注：
- 在 id 字段的定义中添加 COMMENT '主键ID，自增长'，以解释该字段的作用。
invite_codes 表添加备注：
- 在 CREATE TABLE 语句的末尾添加 COMMENT='邀请码表'，说明此表的作用。
id 字段添加 UNSIGNED 属性：
- 将 INT 修改为 INT UNSIGNED，表示 id 列的值为非负整数。这可以增加 id 列的最大值，因为无符号整数比有符号整数的范围更大。
移除外键约束（invite_codes_ibfk_1, invite_codes_ibfk_2, invite_codes_ibfk_3）：
- 根据建议，取消外键约束。虽然外键可以保证数据一致性，但是如果数据量过大，外键约束会降低性能。如果你的应用本身可以保证数据一致性，可以去掉外键约束。
- 如果你的应用确实需要外键约束，并且性能不会是瓶颈，那么可以保留外键约束。
status 字段类型修改为 TINYINT:
- 将 status 字段的类型从 enum ('effective', 'expired', 'bound') 修改为 TINYINT NOT NULL DEFAULT 0，并添加注释 COMMENT '邀请码状态，0表示有效，1表示已过期，2表示已绑定'。
- enum 类型在数据库中存储的是字符串，而 tinyint 存储的是整数。使用 tinyint 存储状态，性能更高。
- 使用 tinyint 类型时，需要在注释中明确每个数字的含义。
设置表的字符集和排序规则：
- 在 CREATE TABLE 语句中使用 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci。
- utf8mb4 字符集支持更广泛的 Unicode 字符，可以避免乱码问题。
- utf8mb4_unicode_ci 是一种不区分大小写的排序规则，在比较字符串时忽略大小写。
- 这里选择 utf8mb4_unicode_ci 排序规则，因为该表中有昵称等数据，选择此排序规则，比较字符串时可以忽略大小写。
- 如果你需要更精细的排序，可以选择 utf8mb4_bin。
- 你也可以使用 utf8_general_ci 或 utf8_bin，但考虑到 utf8mb4 对 Unicode 的支持更好，推荐使用 utf8mb4 的字符集和排序规则。
添加索引: