Skip to content

project rulesDatabasesmysql

MySQL Rules

Project rules for MySQL 8: InnoDB and utf8mb4 defaults, careful indexes, online migrations, short transactions and parameterized queries.

npx workspacetemplates add mysql-rules --tool claude-code

Writes 1 file to your project root

CLAUDE.mdadd to your file21 lines · 1.5 KBFile
# Project rules: MySQL

## Engine and encoding
- Use InnoDB for every table. Set the database and tables to `utf8mb4` with `utf8mb4_0900_ai_ci` (or the server's modern default). Keep `sql_mode` strict so bad data raises errors instead of being truncated.
- Store times in UTC with `DATETIME` or `TIMESTAMP` and convert in the application. Use `DECIMAL` for money, `BIGINT UNSIGNED` auto-increment keys or ordered UUIDs stored as `BINARY(16)`.

## Schema
- Use snake_case names. Declare `NOT NULL`, defaults, unique keys and foreign keys. Keep the primary key short, because every secondary index repeats it.
- Prefer `VARCHAR` with a sensible length. Avoid `ENUM` for values that will change.

## Indexes and queries
- Index columns used in `WHERE`, `JOIN` and `ORDER BY`, with the most selective equality column first in composite indexes. Do not wrap an indexed column in a function or compare mismatched types.
- Check each new query with `EXPLAIN ANALYZE`. List columns instead of `SELECT *`. Use keyset pagination instead of large `OFFSET` values.
- Always use prepared statements and bound parameters.

## Transactions and migrations
- Keep transactions short and access rows in a consistent order to limit deadlocks; retry on deadlock errors.
- Apply schema changes with `ALGORITHM=INPLACE` or a tool such as gh-ost or pt-online-schema-change for large tables. One change per migration, tested on a copy with production-sized data.

## Checks
- Run the migrations from scratch and the test suite against a real MySQL 8 container.

What it does

MySQL has a few old defaults that cause trouble later, such as weak character sets and loose handling of bad data. These rules set the safe choices up front so an agent does not repeat them.

They also describe how to change tables that are in use and how to write queries that the optimizer can serve from an index.

Example

you › Add a last_login column and an index for inactive-user reports

agent › The agent writes an online migration adding a nullable column, backfills in batches, creates the index with a stated algorithm, and shows the EXPLAIN output for the report query.

Questions

Does this work for MariaDB?
Mostly. Collation names and some online DDL options differ, so check the ones your version supports.
Why avoid ENUM columns?
Changing the allowed values requires a table alteration, while a lookup table or a check constraint changes more easily.
  • MongoDB Rules

    Project rulesmongodb

    Free
  • Postgres SQL Rules

    Project rulespostgres

    Free
  • Database Connection Pooling

    SkillDatabases

    Free
  • Database Migration Safety

    SkillDatabases

    Free