优化 invite_codes 表的 SQL 创建语句

-- auto-generated definition
create table invite_codes
(id                 int auto_incrementprimary 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 codeunique (invite_code),constraint invite_codes_ibfk_1foreign key (admin_id) references productqualification.admin (id),constraint invite_codes_ibfk_2foreign key (invitor) references productqualification.admin (id),constraint invite_codes_ibfk_3foreign key (user_id) references productqualification.user (id)
)collate = utf8mb4_unicode_ci;create index admin_idon invite_codes (admin_id);create index invitoron invite_codes (invitor);create index user_idon 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`);

详细修改说明：

1. id 字段添加备注：
   • 在 id 字段的定义中添加 COMMENT '主键ID，自增长'，以解释该字段的作用。
2. invite_codes 表添加备注：
   • 在 CREATE TABLE 语句的末尾添加 COMMENT='邀请码表'，说明此表的作用。
3. id 字段添加 UNSIGNED 属性：
   • 将 INT 修改为 INT UNSIGNED，表示 id 列的值为非负整数。 这可以增加 id 列的最大值，因为无符号整数比有符号整数的范围更大。
4. 移除外键约束（invite_codes_ibfk_1, invite_codes_ibfk_2, invite_codes_ibfk_3）：
   • 根据建议，取消外键约束。 虽然外键可以保证数据一致性， 但是如果数据量过大， 外键约束会降低性能。 如果你的应用本身可以保证数据一致性，可以去掉外键约束。
   • 如果你的应用确实需要外键约束，并且性能不会是瓶颈，那么可以保留外键约束。
5. status 字段类型修改为 TINYINT:
   • 将 status 字段的类型从 enum ('effective', 'expired', 'bound') 修改为 TINYINT NOT NULL DEFAULT 0， 并添加注释 COMMENT '邀请码状态，0表示有效，1表示已过期，2表示已绑定'。
   • enum 类型在数据库中存储的是字符串，而 tinyint 存储的是整数。 使用 tinyint 存储状态，性能更高。
   • 使用 tinyint 类型时，需要在注释中明确每个数字的含义。
6. 设置表的字符集和排序规则：
   • 在 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 的字符集和排序规则。
7. 添加索引:

• 保留原有的 admin_id, invitor, user_id 的索引。

其他说明：

• 注释 (COMMENT)： 在代码中添加合适的注释很重要，可以提高代码的可读性，方便后期维护。
• 外键约束： 外键约束通常用于保证数据完整性，但是会降低性能， 你需要根据实际情况选择是否使用。
• 数据类型选择： 选择合适的数据类型可以有效地利用存储空间， 并提高查询效率。
• COLLATE 和 CHARSET： 正确设置字符集和排序规则可以避免乱码问题，并且影响查询时的排序和比较规则。

总结：

优化后的 SQL 语句，根据了图片上的建议，添加了注释， 修改了 id 列的属性， 移除了外键约束，修改了 status 列的类型， 并设置了表的字符集和排序规则。 从而使 SQL 语句更清晰，结构更合理，也更能提高性能。