mysql
installsforks
How to install mysql?
youlaitech/youlai-skills/mysql
$npx -y skills add youlaitech/youlai-skills --skill mysqlMySQL database design standards. Use this skill when designing table structures, field types, indexes, or naming conventions for MySQL databases.
SKILL.md
| 1 | # MySQL 数据库设计规范 |
| 2 | |
| 3 | ## 触发条件 |
| 4 | |
| 5 | - Design database table structures |
| 6 | - Define field types and lengths |
| 7 | - Create indexes |
| 8 | - Name tables and fields |
| 9 | |
| 10 | --- |
| 11 | |
| 12 | ## Part 1: 命名规范 |
| 13 | |
| 14 | ### 库名命名 |
| 15 | |
| 16 | | 规则 | 说明 | |
| 17 | |------|------| |
| 18 | | 小写字母 | `youlai_admin` | |
| 19 | | 下划线分隔 | 不用驼峰 | |
| 20 | | 业务前缀 | `youlai_` | |
| 21 | |
| 22 | ### 表名命名 |
| 23 | |
| 24 | | 类型 | 前缀 | 示例 | |
| 25 | |------|------|------| |
| 26 | | 系统表 | `sys_` | `sys_user`, `sys_role` | |
| 27 | | 业务表 | `biz_` | `biz_order`, `biz_product` | |
| 28 | | 关联表 | `rel_` 或主表前缀 | `sys_user_role`, `rel_user_dept` | |
| 29 | | 配置表 | `cfg_` | `cfg_dict`, `cfg_config` | |
| 30 | | 日志表 | `log_` | `log_login`, `log_operation` | |
| 31 | |
| 32 | ### 字段命名 |
| 33 | |
| 34 | | 类型 | 规范 | 示例 | |
| 35 | |------|------|------| |
| 36 | | 主键 | `id` | `id` | |
| 37 | | 外键 | 表名 + `_id` | `user_id`, `dept_id` | |
| 38 | | 名称 | `xxx_name` | `user_name`, `dept_name` | |
| 39 | | 状态 | `status` | `status` | |
| 40 | | 类型 | `xxx_type` | `menu_type`, `log_type` | |
| 41 | | 时间 | `xxx_time` | `create_time`, `login_time` | |
| 42 | | 布尔 | `is_xxx` | `is_deleted`, `is_enabled` | |
| 43 | | 数量 | `xxx_count` | `login_count`, `view_count` | |
| 44 | | 排序 | `sort` 或 `order_num` | `sort` | |
| 45 | |
| 46 | ### 索引命名 |
| 47 | |
| 48 | | 类型 | 前缀 | 示例 | |
| 49 | |------|------|------| |
| 50 | | 主键 | `pk_` | `pk_user` | |
| 51 | | 唯一索引 | `uk_` | `uk_username` | |
| 52 | | 普通索引 | `idx_` | `idx_dept_id` | |
| 53 | | 组合索引 | `idx_` | `idx_dept_status` | |
| 54 | | 全文索引 | `ft_` | `ft_content` | |
| 55 | |
| 56 | --- |
| 57 | |
| 58 | ## Part 2: 字段类型规范 |
| 59 | |
| 60 | ### 主键 |
| 61 | |
| 62 | ```sql |
| 63 | `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键' |
| 64 | ``` |
| 65 | |
| 66 | ### 字符串类型 |
| 67 | |
| 68 | | 场景 | 类型 | 长度 | |
| 69 | |------|------|------| |
| 70 | | 用户名 | VARCHAR | 50 | |
| 71 | | 昵称 | VARCHAR | 50 | |
| 72 | | 密码（加密后） | VARCHAR | 100 | |
| 73 | | 手机号 | VARCHAR | 20 | |
| 74 | | 邮箱 | VARCHAR | 100 | |
| 75 | | URL | VARCHAR | 500 | |
| 76 | | 标题 | VARCHAR | 200 | |
| 77 | | 简介 | VARCHAR | 500 | |
| 78 | | 详细描述 | TEXT | - | |
| 79 | | JSON 数据 | JSON | - | |
| 80 | |
| 81 | ### 数值类型 |
| 82 | |
| 83 | | 场景 | 类型 | 说明 | |
| 84 | |------|------|------| |
| 85 | | 主键/ID | BIGINT | 20 位 | |
| 86 | | 外键 | BIGINT | 关联主键类型 | |
| 87 | | 数量/计数 | INT | - | |
| 88 | | 金额（分） | BIGINT | 避免浮点精度问题 | |
| 89 | | 排序号 | INT | - | |
| 90 | | 状态 | TINYINT | 0/1/2 | |
| 91 | | 类型 | TINYINT | 枚举值 | |
| 92 | | 百分比 | DECIMAL(5,2) | 0.00-100.00 | |
| 93 | |
| 94 | ### 时间类型 |
| 95 | |
| 96 | | 场景 | 类型 | 说明 | |
| 97 | |------|------|------| |
| 98 | | 创建时间 | DATETIME | `create_time` | |
| 99 | | 更新时间 | DATETIME | `update_time` | |
| 100 | | 日期 | DATE | `birth_date` | |
| 101 | | 时间戳 | BIGINT | 毫秒级 | |
| 102 | |
| 103 | ### 布尔类型 |
| 104 | |
| 105 | ```sql |
| 106 | `is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否删除(0否 1是)' |
| 107 | `is_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用(0否 1是)' |
| 108 | ``` |
| 109 | |
| 110 | --- |
| 111 | |
| 112 | ## Part 3: 通用字段 |
| 113 | |
| 114 | ### 每张表必备字段 |
| 115 | |
| 116 | ```sql |
| 117 | `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', |
| 118 | `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', |
| 119 | `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', |
| 120 | `create_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '创建人', |
| 121 | `update_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '更新人', |
| 122 | `deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志(0未删除 1已删除)', |
| 123 | PRIMARY KEY (`id`) |
| 124 | ``` |
| 125 | |
| 126 | ### 可选字段 |
| 127 | |
| 128 | ```sql |
| 129 | `remark` VARCHAR(500) DEFAULT NULL COMMENT '备注', |
| 130 | `version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号', |
| 131 | `tenant_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '租户ID', |
| 132 | ``` |
| 133 | |
| 134 | --- |
| 135 | |
| 136 | ## Part 4: 索引规范 |
| 137 | |
| 138 | ### 索引原则 |
| 139 | |
| 140 | | 原则 | 说明 | |
| 141 | |------|------| |
| 142 | | 最左前缀 | 组合索引按查询顺序建立 | |
| 143 | | 高选择性 | 选择区分度高的列 | |
| 144 | | 控制数量 | 单表索引不超过 5 个 | |
| 145 | | 组合优先 | 组合索引优于多个单列索引 | |
| 146 | |
| 147 | ### 必建索引 |
| 148 | |
| 149 | ```sql |
| 150 | -- 主键索引 |
| 151 | PRIMARY KEY (`id`) |
| 152 | |
| 153 | -- 外键索引 |
| 154 | INDEX `idx_dept_id` (`dept_id`) |
| 155 | |
| 156 | -- 状态索引（常用于筛选） |
| 157 | INDEX `idx_status` (`status`) |
| 158 | |
| 159 | -- 创建时间索引（常用于排序） |
| 160 | INDEX `idx_create_time` (`create_time`) |
| 161 | |
| 162 | -- 唯一索引（业务唯一字段） |
| 163 | UNIQUE INDEX `uk_username` (`username`) |
| 164 | ``` |
| 165 | |
| 166 | ### 组合索引 |
| 167 | |
| 168 | ```sql |
| 169 | -- 组合索引：部门 + 状态 |
| 170 | INDEX `idx_dept_status` (`dept_id`, `status`) |
| 171 | |
| 172 | -- 组合索引：用户 + 创建时间 |
| 173 | INDEX `idx_user_time` (`user_id`, `create_time`) |
| 174 | ``` |
| 175 | |
| 176 | ### 索引避坑 |
| 177 | |
| 178 | | 避免 | 原因 | |
| 179 | |------|------| |
| 180 | | `LIKE '%xxx%'` | 前缀模糊不走索引 | |
| 181 | | `OR` 条件 | 可能不走索引，改用 UNION | |
| 182 | | 函数操作 | `WHERE YEAR(create_time) = 2024` 不走索引 | |
| 183 | | 类型转换 | 字符串与数字比较会隐式转换 | |
| 184 | | `NOT IN` | 改用 `NOT EXISTS` 或 `LEFT JOIN ... IS NULL` | |
| 185 | |
| 186 | --- |
| 187 | |
| 188 | ## Part 5: 表设计规范 |
| 189 | |
| 190 | ### 用户表示例 |
| 191 | |
| 192 | ```sql |
| 193 | CREATE TABLE `sys_user` ( |
| 194 | `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', |
| 195 | `username` VARCHAR(50) NOT NULL COMMENT '用户名', |
| 196 | `password` VARCHAR(100) NOT NULL COMMENT '密码', |
| 197 | `nickname` VARCHAR(50) NOT NULL COMMENT '昵称', |
| 198 | `mobile` VARCHAR(20) DEFAULT NULL COMMENT '手机号', |
| 199 | `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱', |
| 200 | `gender` TINYINT NOT NULL DEFAULT 0 COMMENT '性别(0未知 1男 2女)', |
| 201 | `avatar` VARCHAR(500) DEFAULT NULL COMMENT '头像URL', |
| 202 | `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(1正常 0禁用)', |
| 203 | `dept_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '部门ID', |
| 204 | `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', |
| 205 | `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', |
| 206 | `create_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '创建人', |
| 207 | `update_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '更新人', |
| 208 | `deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志(0未删除 1已删除)', |
| 209 | PRIMARY KEY (`id`), |
| 210 | UNIQUE INDEX `uk_username` (`username`), |
| 211 | INDEX `idx_dept_id` (`dept_id`), |
| 212 | INDEX `idx_status` (`status`), |
| 213 | INDEX `idx_create_time` (`create_time`) |
| 214 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci CO |
People who liked this also viewed
6 picksType
- azure-storageAzure Storage Services including Blob Storage, File Shares, Queue Storage, Table Storage, and Data Lake. Answers questions about storage access tiers (hot, cool, cold, archive), when to use each tier,SkillsJul 2026
- supabase-postgres-best-practicesPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.SkillsJul 2026
- supabaseUse when doing ANY task involving Supabase. Triggers: Supabase products (Database, Auth, Edge Functions, Realtime, Storage, Vectors, Cron, Queues); client libraries and SSR integrations (supabase-js, SkillsJul 2026
- convex-quickstartCreates or adds Convex to an app. Use for new Convex projects, npm create convex@latest, frontend setup, env vars, or the first npx convex dev run.SkillsJul 2026
- convex-performance-auditAudits Convex performance for reads, subscriptions, write contention, and function limits. Use for slow features, insights findings, OCC conflicts, or read amplification.SkillsJul 2026
- convex-setup-authSets up Convex auth, identity mapping, and access control. Use for login, auth providers, users tables, protected functions, or roles in a Convex app.SkillsJul 2026
