一键导入
mysql
MySQL patterns, schema design, indexing, query optimization with Prisma. Trigger: When designing MySQL schemas, optimizing queries, or configuring MySQL.
用 Codex 或 Claude 帮你安装 复制这段 Prompt,粘贴到 Codex、Claude 或其他助手里,让它检查 Skill 页面并帮你完成安装。
菜单
MySQL patterns, schema design, indexing, query optimization with Prisma. Trigger: When designing MySQL schemas, optimizing queries, or configuring MySQL.
用 Codex 或 Claude 帮你安装 复制这段 Prompt,粘贴到 Codex、Claude 或其他助手里,让它检查 Skill 页面并帮你完成安装。
基于 SOC 职业分类
Vercel AI SDK 5 patterns. Trigger: When building AI chat features - breaking changes from v4.
Docker patterns, Dockerfile, multi-stage builds, Compose, dev/prod. Trigger: When writing Dockerfiles, docker-compose, or container configuration.
Creates Jira epics for large features following Prowler's standard format. Trigger: When user asks to create an epic, large feature, or multi-task initiative.
Creates Jira tasks following Prowler's standard format. Trigger: When user asks to create a Jira task, ticket, or issue.
NestJS 11 patterns, modules, decorators, guards, pipes, CQRS. Trigger: When writing NestJS backend code - controllers, services, modules, DTOs.
Nuxt 4 patterns, composables, server routes, data fetching. Trigger: When writing Nuxt code - pages, composables, server routes, useFetch, layouts.
| name | mysql |
| description | MySQL patterns, schema design, indexing, query optimization with Prisma. Trigger: When designing MySQL schemas, optimizing queries, or configuring MySQL. |
| license | Apache-2.0 |
| metadata | {"author":"gentleman-programming","version":"1.0"} |
| Use Case | MySQL Type | Prisma Type | Prisma Attribute |
|---|---|---|---|
| Short text (name, email) | VARCHAR(n) | String | @db.VarChar(255) |
| Long text (content, bio) | TEXT | String | @db.Text |
| Money/decimal | DECIMAL(10,2) | Decimal | @db.Decimal(10, 2) |
| Boolean | TINYINT(1) | Boolean | (auto) |
| Date/time | DATETIME | DateTime | @db.DateTime(0) |
| Positive integers | INT UNSIGNED | Int | @db.UnsignedInt |
| JSON data | JSON | Json | @db.Json |
| UUID/CUID | VARCHAR(30) | String | @id @default(cuid()) |
| Enum | ENUM | Enum | Prisma enum type |
DATETIME(0) not TIMESTAMP — TIMESTAMP has Y2038 limit and timezone conversionDECIMAL for money — NEVER FLOAT or DOUBLEmodel User {
id String @id @default(cuid())
email String @unique @db.VarChar(255) // Unique = automatic index
name String @db.VarChar(100)
role Role @default(USER)
deletedAt DateTime? @db.DateTime(0)
@@index([role]) // Filtered queries on role
@@index([deletedAt]) // Soft delete queries
@@index([role, deletedAt]) // Composite for both
@@map("users")
}
model Post {
authorId String
published Boolean @default(false)
createdAt DateTime @default(now()) @db.DateTime(0)
@@index([authorId]) // Foreign key
@@index([published, createdAt(sort: Desc)]) // Published posts sorted by date
@@map("posts")
}
| Rule | Reason |
|---|---|
| Index ALL foreign keys | MySQL doesn't auto-index FKs |
| Composite index: most selective column first | Reduces scan range |
| Don't index low-cardinality alone (boolean) | Full scan may be faster |
| Composite index covers left-prefix queries | [a, b, c] covers WHERE a and WHERE a, b |
| Max 5-6 indexes per table | Write performance degrades |
const explain = await prisma.$queryRaw`EXPLAIN SELECT * FROM users WHERE email = 'test@test.com'`;
// ✅ Select only what you need
const users = await prisma.user.findMany({
select: { id: true, name: true, email: true },
});
// ✅ Cursor pagination for large datasets
const posts = await prisma.post.findMany({
take: 20,
skip: 1,
cursor: { id: lastId },
orderBy: { id: 'asc' },
});
// ❌ AVOID: Offset pagination on large tables
const posts = await prisma.post.findMany({
skip: 10000, // MySQL still scans 10000 rows
take: 20,
});
// ✅ Count with where (uses index)
const count = await prisma.post.count({
where: { published: true, authorId: userId },
});
// ❌ AVOID: N+1 queries
// BAD:
const users = await prisma.user.findMany();
for (const user of users) {
const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}
// ✅ GOOD: Include in single query
const users = await prisma.user.findMany({
include: { posts: true },
});
# docker-compose.yml
services:
mysql:
image: mysql:8.4
command: >
--default-authentication-plugin=mysql_native_password
--character-set-server=utf8mb4
--collation-server=utf8mb4_unicode_ci
--max-connections=200
--innodb-buffer-pool-size=256M
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
MYSQL_DATABASE: ${MYSQL_DATABASE}
MYSQL_USER: ${MYSQL_USER}
MYSQL_PASSWORD: ${MYSQL_PASSWORD}
volumes:
- mysql_data:/var/lib/mysql
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
interval: 10s
timeout: 5s
retries: 5
DATABASE_URL="mysql://user:password@localhost:3306/dbname?connection_limit=10"
Need full-text search? → FULLTEXT index + Prisma raw query
Need JSON queries? → @db.Json + path queries ($.field)
Large table pagination? → Cursor-based (not offset)
Need unique + soft delete? → Composite unique without deletedAt (app-level check)
Money calculations? → DECIMAL(10,2) — never FLOAT
Need audit trail? → createdAt + updatedAt + separate audit table
// Full-text search (MySQL)
const results = await prisma.$queryRaw`
SELECT * FROM posts
WHERE MATCH(title, content) AGAINST(${searchTerm} IN NATURAL LANGUAGE MODE)
`;
// JSON field query
const products = await prisma.product.findMany({
where: { metadata: { path: '$.color', equals: 'red' } },
});
// Group by with aggregation
const stats = await prisma.post.groupBy({
by: ['authorId'],
_count: { id: true },
_avg: { views: true },
orderBy: { _count: { id: 'desc' } },
take: 10,
});
// Upsert
const user = await prisma.user.upsert({
where: { email: 'test@test.com' },
update: { name: 'Updated' },
create: { email: 'test@test.com', name: 'New' },
});
# MySQL CLI (Docker)
docker exec -it mysql mysql -u root -p # Root access
docker exec -it mysql mysql -u user -p dbname # User access
# Useful MySQL commands
SHOW PROCESSLIST; # Active connections
SHOW INDEX FROM users; # Table indexes
EXPLAIN SELECT * FROM users WHERE email = 'x'; # Query plan
SHOW TABLE STATUS; # Table sizes
SELECT @@version; # MySQL version