Claude Code with MySQL: Schema, Migrations, Queries
Using Claude Code with MySQL
MySQL 8.x still powers a significant share of production backends: PHP monoliths, Spring Boot services, Node.js APIs, Django apps. It is also the database where Claude Code produces the most correctness issues without explicit project configuration. The reasons are structural: MySQL and PostgreSQL share most SQL syntax but diverge precisely where it matters - window function syntax, JSON operators, full-text indexing, generated columns, and transaction isolation semantics. Claude's training data includes both, so without a CLAUDE.md declaring which database you are using and which version, it samples freely from both dialects and produces queries that look right but fail at runtime.
The second failure mode is operational defaults. Claude defaults to MyISAM for certain table types, picks utf8 instead of utf8mb4, generates TEXT columns without a length where a VARCHAR(255) would serve, writes string concatenation instead of prepared statements, and proposes ALTER TABLE ADD COLUMN operations that will lock a high-traffic table for the duration. None of these are wrong by the letter of SQL, but all of them are wrong for a production MySQL 8.x project.
The fix is the same one that works across every other stack in this series: a project-specific CLAUDE.md that tells Claude which database engine, version, and conventions apply to your codebase. If you are new to Claude Code, the Claude Code setup guide covers installation and authentication first. If you have a polyglot data layer, the Claude Code database guide covers the broader decision matrix.
The MySQL CLAUDE.md template
The CLAUDE.md at your project root loads before every Claude Code session. For MySQL 8.x, it needs to encode engine choice, charset, ORM and migration tooling, naming conventions, query rules, and the operational gates that prevent destructive commands.
# MySQL project rules
## Stack
- MySQL: 8.0.x (production), 8.0.x (local via Docker)
- Storage engine: InnoDB for all tables, always (never MyISAM)
- Charset: utf8mb4, collation: utf8mb4_unicode_ci, applied at DB + table + column level
- Connection pooling: Node: mysql2 with pool (min 2, max 10); Python: SQLAlchemy pool_size=5 max_overflow=10; Java: HikariCP
- ORM: [choose one and delete others]
- Node/TypeScript: Prisma 5.x with @prisma/client
- Node/TypeScript: Drizzle 0.36.x with drizzle-kit
- Python: SQLAlchemy 2.x ORM
- PHP: Laravel Eloquent
- Java: Spring Data JPA (Hibernate)
- Raw SQL: mysql2 (Node) or PyMySQL (Python) with prepared statements
- Migration tool: [choose one]
- Prisma: prisma migrate dev / prisma migrate deploy
- Drizzle: drizzle-kit generate / drizzle-kit migrate
- Flyway: flyway migrate (for Java and PHP)
- Knex: knex migrate:latest
- Local dev DB: docker compose service `mysql`, port 3306, database: app_dev
## Database conventions
- Table names: snake_case, plural (users, order_items, email_verifications)
- Column names: snake_case
- Primary keys: BIGINT UNSIGNED AUTO_INCREMENT (or UUID via CHAR(36) if distributed)
- Timestamps: created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
- Soft deletes: deleted_at DATETIME(6) NULL DEFAULT NULL (NULL means active)
- All boolean columns: TINYINT(1) NOT NULL DEFAULT 0 (MySQL has no native BOOLEAN)
- Foreign keys: always declared with explicit ON DELETE and ON UPDATE
- Indexes named: idx_{table}_{columns} for standard, udx_{table}_{columns} for unique
## Project structure
- prisma/schema.prisma (or db/schema/): ORM schema files
- prisma/migrations/ (or db/migrations/): migration files, never hand-edited after generation
- db/queries/: reusable parameterised query functions
- db/seed/: seed scripts for local dev only
## Migration workflow
- Always: generate a migration file before applying
- Always: review generated SQL before running migration
- Never: hand-edit a migration that has already been applied anywhere
- Never: combine schema changes (DDL) and data backfills (DML) in the same migration
- Data backfills go in a separate migration or a dedicated backfill script
- For large tables: use pt-online-schema-change or gh-ost for ALTER TABLE operations
## Query rules
- NEVER concatenate user input into SQL. Always use parameterised queries or prepared statements.
- Never use SELECT *. Always list explicit columns.
- Use transactions for multi-statement writes. MySQL default isolation is REPEATABLE READ.
- All queries on soft-deletable tables must include WHERE deleted_at IS NULL.
- Before proposing an index, run EXPLAIN and check the query plan.
- Never hardcode connection strings. Read from DATABASE_URL or individual env vars.
## Hard rules
- NEVER run DROP TABLE, DROP DATABASE, TRUNCATE, or DELETE FROM {table} without --confirm
- NEVER apply migrations to production without running them on staging first
- NEVER use MyISAM for any new table under any circumstances
- NEVER use utf8 charset. Always utf8mb4.
- All destructive operations require a backup confirmation before proceeding
- Generated columns (stored or virtual): declare them explicitly, never let Claude infer
Three rules in this CLAUDE.md carry the most preventive weight.
The utf8mb4 rule is not optional. MySQL's utf8 charset is a bug: it stores only three-byte UTF-8 sequences, which means it silently drops any Unicode character outside the Basic Multilingual Plane, including the full emoji range and many CJK characters. utf8mb4 is the correct four-byte implementation. Claude defaults to utf8 because most tutorial material predates the widespread awareness of this issue. Locking utf8mb4 at the database level prevents column-by-column drift.
The InnoDB rule is structural. InnoDB is the MySQL 8 default, but some schema templates and older tutorials still reference MyISAM or MEMORY. MyISAM lacks foreign key support, row-level locking, and crash recovery. There is no scenario where a new table in a modern application should use MyISAM. Stating it explicitly in CLAUDE.md means Claude never asks and never defaults to it.
The DDL + DML separation rule prevents the most common production incident from Claude-assisted schema changes. Adding a column to a large table in MySQL acquires a metadata lock. If the migration also backfills the new column in the same transaction, the lock is held for the duration of the backfill. On a table with millions of rows that is an outage. Splitting them means the schema change can use online DDL, and the backfill runs independently in batches.
Storage engine, charset, and the InnoDB specifics Claude misses
MySQL 8's InnoDB has capabilities that Claude frequently misses or misconfigures. The three that matter most for a Claude Code workflow are generated columns, JSON columns, and the DATETIME(6) precision requirement.
Generated columns let you derive a value from other columns and persist it (stored) or compute it at read time (virtual). Claude generates them without the STORED or VIRTUAL keyword, which defaults to virtual. If you then build an index on a virtual generated column, MySQL computes it inline. For a high-cardinality column used in frequent queries, a stored generated column with an index is significantly faster. Declare the intent in CLAUDE.md and Claude will write the correct syntax.
-- Virtual generated column (computed at read time, not persisted)
ALTER TABLE orders
ADD COLUMN year_month VARCHAR(7)
GENERATED ALWAYS AS (DATE_FORMAT(created_at, '%Y-%m')) VIRTUAL;
-- Stored generated column (persisted, indexable efficiently)
ALTER TABLE orders
ADD COLUMN year_month VARCHAR(7)
GENERATED ALWAYS AS (DATE_FORMAT(created_at, '%Y-%m')) STORED,
ADD INDEX idx_orders_year_month (year_month);
JSON columns arrived in MySQL 5.7 and work well in 8.x. Claude generates JSON columns correctly but consistently forgets to add functional indexes, which means JSON path queries default to full table scans.
-- Table with a JSON metadata column
CREATE TABLE events (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
payload JSON NOT NULL,
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Functional index on a JSON path (MySQL 8.0.13+)
ALTER TABLE events
ADD INDEX idx_events_payload_type
((CAST(payload->>'$.type' AS CHAR(50))));
-- Query that uses the functional index
SELECT id, payload->>'$.user_id' AS user_id
FROM events
WHERE payload->>'$.type' = 'purchase'
AND created_at >= '2026-01-01 00:00:00.000000';
CTEs and window functions are fully supported in MySQL 8.x but were absent in 5.7. Claude's training set includes MySQL 5.7 content, so it sometimes generates subquery equivalents or falls back to temporary table patterns. A rule in CLAUDE.md: Use CTEs and window functions freely. Target MySQL 8.0 minimum. Do not write MySQL 5.7 compatible equivalents.
The DATETIME(6) precision note matters for distributed systems: DATETIME stores seconds only, DATETIME(6) stores microseconds. Microsecond precision is needed for events, audit logs, and anything where two rows might arrive in the same second. Add it to the conventions section and Claude applies it consistently.
Connection pooling per stack
MySQL's default max_connections is 151. Each unclosed connection holds a thread, memory, and a file descriptor. Without a pool, every request opens a new connection and closes it after the query, which adds latency and exhausts the connection limit under any real load. Claude generates database clients without pools by default in every stack. The CLAUDE.md pool configuration rules fix this.
// Node.js with mysql2 - correct pool configuration
import mysql from "mysql2/promise";
const pool = mysql.createPool({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
port: Number(process.env.DB_PORT) || 3306,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
enableKeepAlive: true,
keepAliveInitialDelay: 0,
// Required for MySQL 8 caching_sha2_password auth
authPlugins: undefined,
});
export async function query<T>(sql: string, params: unknown[]): Promise<T[]> {
const [rows] = await pool.execute<mysql.RowDataPacket[]>(sql, params);
return rows as T[];
}
# Python with SQLAlchemy 2.x - correct pool configuration
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
engine = create_engine(
url=f"mysql+pymysql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}",
pool_size=5,
max_overflow=10,
pool_timeout=30,
pool_recycle=1800, # recycle connections after 30 min (prevents MySQL gone-away)
pool_pre_ping=True, # test connection before using from pool
echo=False,
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
The pool_pre_ping=True and pool_recycle=1800 settings in SQLAlchemy prevent the "MySQL server has gone away" error that Claude-generated code frequently triggers. MySQL closes idle connections after wait_timeout seconds (default: 8 hours, often lower on managed instances). Without pre-ping, a worker picks up a dead connection from the pool and fails. pool_recycle caps how long a connection stays in the pool; pool_pre_ping validates it before use. Add both to CLAUDE.md for Python projects.
For Java with HikariCP, which is the Spring Boot default, the critical settings are maximumPoolSize, minimumIdle, connectionTimeout, and idleTimeout. Claude generates HikariCP config without validationTimeout and keepaliveTime, which produces connection reuse failures on long-idle connections. State the full HikariCP config in CLAUDE.md.
Prepared statements and SQL injection prevention
MySQL prepared statements are the non-negotiable safety mechanism for user input. Claude generates raw string interpolation in roughly 30% of query code when there is no explicit rule against it. The failure modes range from broken queries to full database compromise.
The correct pattern by stack:
// Node.js - mysql2 pool.execute() parameterises automatically
// CORRECT: parameterised
const [rows] = await pool.execute(
"SELECT id, email, role FROM users WHERE email = ? AND deleted_at IS NULL",
[userEmail]
);
// WRONG: string concatenation (never do this)
// const [rows] = await pool.query(`SELECT * FROM users WHERE email = '${userEmail}'`);
// Prisma - parameterises all ORM calls automatically
const user = await prisma.user.findFirst({
where: { email: userEmail, deletedAt: null },
select: { id: true, email: true, role: true },
});
// Prisma raw query - use Prisma.sql tagged template
import { Prisma } from "@prisma/client";
const result = await prisma.$queryRaw<User[]>(
Prisma.sql`SELECT id, email FROM users WHERE email = ${userEmail} AND deleted_at IS NULL`
);
# Python - SQLAlchemy core, parameterised via :param syntax
with engine.connect() as conn:
result = conn.execute(
text("SELECT id, email FROM users WHERE email = :email AND deleted_at IS NULL"),
{"email": user_email}
)
rows = result.fetchall()
# Python - SQLAlchemy ORM, parameterised automatically
user = session.query(User).filter(
User.email == user_email,
User.deleted_at.is_(None)
).first()
// PHP - PDO prepared statements
$stmt = $pdo->prepare(
"SELECT id, email FROM users WHERE email = :email AND deleted_at IS NULL"
);
$stmt->execute(["email" => $userEmail]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
The CLAUDE.md rule: NEVER interpolate variables into SQL strings. Use positional placeholders (?) for mysql2, named placeholders (:param) for PDO and SQLAlchemy, and Prisma.sql tagged templates for Prisma raw queries. The ORM query builder parameterises automatically when you use its API; raw queries require explicit parameterisation.
The Claude Code database guide covers how to enforce this rule via Claude Code's permission system, blocking execution of any query string that contains user-supplied variables outside a parameterised call.
Migration workflow by ORM
The migration tool determines how Claude generates and applies schema changes. CLAUDE.md must specify the tool and the exact workflow commands. Without this, Claude mixes migration strategies across sessions: sometimes generating SQL files, sometimes using ORM push commands, sometimes writing manual ALTER TABLE statements.
Prisma (Node.js, TypeScript) is the most common ORM for MySQL in TypeScript projects. Schema is defined in prisma/schema.prisma, migrations are generated SQL files that Prisma creates and tracks.
# Local development
npx prisma migrate dev --name add_payment_status
# Review the generated migration
cat prisma/migrations/20260514_add_payment_status/migration.sql
# Production deployment
npx prisma migrate deploy
# Inspect current state
npx prisma migrate status
// prisma/schema.prisma - MySQL 8 specific conventions
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
model User {
id Int @id @default(autoincrement()) @db.UnsignedBigInt
email String @unique @db.VarChar(255)
role String @default("user") @db.VarChar(50)
createdAt DateTime @default(now()) @db.DateTime(6) @map("created_at")
updatedAt DateTime @updatedAt @db.DateTime(6) @map("updated_at")
deletedAt DateTime? @db.DateTime(6) @map("deleted_at")
orders Order[]
@@map("users")
@@index([deletedAt], name: "idx_users_deleted_at")
}
The @db.DateTime(6) annotation is required for microsecond precision. Without it Prisma generates DATETIME with second precision. The @@map decorator keeps Prisma's camelCase model names mapped to snake_case table names. Add both to CLAUDE.md or Claude will omit them.
Flyway (Java, PHP) uses versioned SQL migration files that Flyway tracks in a flyway_schema_history table. Files are named V{version}__{description}.sql. Claude generates Flyway migrations correctly when told the convention, but defaults to applying DDL and DML in the same file.
-- V20260514__add_payment_status_to_orders.sql
-- DDL only: add the column (runs in milliseconds via online DDL)
ALTER TABLE orders
ADD COLUMN payment_status VARCHAR(50)
NOT NULL DEFAULT 'pending'
AFTER total_cents;
ALTER TABLE orders
ADD INDEX idx_orders_payment_status (payment_status);
-- V20260515__backfill_payment_status.sql
-- DML only: separate migration, runs after schema is stable
UPDATE orders
SET payment_status = 'paid'
WHERE stripe_charge_id IS NOT NULL
AND payment_status = 'pending';
Knex (Node.js) uses JavaScript migration files with up and down functions. Claude generates Knex migrations without MySQL-specific type definitions and without explicit charset/collation on table creation.
// migrations/20260514_add_payment_status.js
exports.up = function (knex) {
return knex.schema.table("orders", (table) => {
table
.string("payment_status", 50)
.notNullable()
.defaultTo("pending")
.after("total_cents");
table.index(["payment_status"], "idx_orders_payment_status");
});
};
exports.down = function (knex) {
return knex.schema.table("orders", (table) => {
table.dropIndex(["payment_status"], "idx_orders_payment_status");
table.dropColumn("payment_status");
});
};
// knexfile.js - charset/collation at the connection level
module.exports = {
development: {
client: "mysql2",
connection: { /* ... */ },
pool: { min: 2, max: 10 },
},
};
Specify the migration tool and workflow commands in CLAUDE.md. Claude will follow the exact commands listed. Without them, it generates whichever migration pattern appears most frequently in its training data for your stack, which is usually not the one you are using.
Indexing strategy and EXPLAIN ANALYZE
MySQL indexes are the largest source of performance problems in Claude-generated schema. Claude adds indexes reactively when told a query is slow, rather than proactively from the query patterns in the schema. Without CLAUDE.md index rules, it generates tables with only the primary key, then adds indexes one by one as queries fail.
The rules that prevent this:
## Indexing rules (add to CLAUDE.md)
- Every foreign key column gets a btree index immediately at table creation
- Columns used in WHERE filters on high-traffic queries get an index
- Columns used in ORDER BY clauses get an index (or a covering index including WHERE columns)
- JSON path queries need a functional index on the extracted path
- Unique constraints create implicit indexes; do not add a redundant btree index
- Composite index column order: most selective filter first, then ORDER BY columns
- Full-text search: use FULLTEXT index, not LIKE '%term%'
- Before adding an index to an existing table: run EXPLAIN on the slow query first
Reading EXPLAIN output is how you confirm whether an index is used. Claude reads EXPLAIN output when you paste it and proposes fixes. The workflow:
-- Step 1: run EXPLAIN on the query
EXPLAIN SELECT o.id, o.total_cents, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.payment_status = 'pending'
AND o.deleted_at IS NULL
ORDER BY o.created_at DESC
LIMIT 50;
-- Read the output: look for type = 'ALL' (full table scan, bad)
-- vs type = 'ref' or 'range' (index used, good)
-- and Extra = 'Using filesort' (sort not using index, fixable)
-- Step 2: add the composite index that fixes the plan
ALTER TABLE orders
ADD INDEX idx_orders_status_created
(payment_status, created_at DESC, deleted_at);
-- Step 3: re-run EXPLAIN to confirm the plan changed
EXPLAIN SELECT o.id, o.total_cents, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.payment_status = 'pending'
AND o.deleted_at IS NULL
ORDER BY o.created_at DESC
LIMIT 50;
-- Now: type = 'ref', Extra = 'Using index condition' (good)
The EXPLAIN ANALYZE variant (MySQL 8.0.18+) runs the query and returns actual timing alongside estimates. Use it for queries where the estimate is unreliable due to stale statistics.
-- EXPLAIN ANALYZE: runs the query, returns actual vs estimated rows and timing
EXPLAIN ANALYZE
SELECT count(*) FROM orders
WHERE payment_status = 'pending'
AND created_at >= '2026-01-01 00:00:00.000000';
Paste the output to Claude with I got this EXPLAIN output. What index should I add? and it will read the plan and propose the correct composite index. The Claude Code with Prisma guide covers how Prisma's query introspection tools supplement EXPLAIN for ORM-generated queries. The Claude Code with Drizzle guide covers the equivalent for Drizzle's query logger.
Five failure modes Claude hits without CLAUDE.md
These are the patterns that appear most consistently in MySQL projects where no project configuration is in place. Each has a specific CLAUDE.md rule that prevents it.
Failure 1: utf8 instead of utf8mb4. Claude writes CHARSET=utf8 in CREATE TABLE statements. Any column that will store emoji, certain CJK characters, or any four-byte Unicode codepoint will silently truncate or error. The fix is a CLAUDE.md rule that states charset and collation explicitly. Claude applies them at the database, table, and column level when the rule is present.
Failure 2: MyISAM tables. Less common with MySQL 8 defaults, but still appears when Claude generates a schema for full-text search (FULLTEXT indexes work with InnoDB in MySQL 5.6+, but older training data suggests MyISAM). Add Storage engine: InnoDB for all tables. No exceptions. FULLTEXT indexes work with InnoDB in MySQL 8. to CLAUDE.md.
Failure 3: Unchecked ALTER TABLE on large tables. MySQL's online DDL handles many ALTER TABLE operations without locking, but not all. Adding a column with a default value in older MySQL versions copies the full table. In MySQL 8.x, adding a NOT NULL column with a default is instant for InnoDB, but changing column types (VARCHAR to TEXT, INT to BIGINT) can still copy the table. Claude does not check table size before proposing schema changes. Add a CLAUDE.md rule: For any ALTER TABLE on a table that may have more than 100,000 rows, note the operation type and whether it uses online DDL before proposing the migration.
Failure 4: N+1 queries in ORM code. The same pattern from the PostgreSQL guide: Claude writes a loop that queries per row instead of a join or batch lookup. For Prisma: use include or select with nested relations. For Drizzle: use .with() on a relational query or db.select().from().leftJoin(). For SQLAlchemy: use joinedload or selectinload in the query options. Add the anti-pattern explicitly to CLAUDE.md so Claude recognises it when generating list endpoints.
Failure 5: Missing deleted_at IS NULL filter. On tables with soft-delete columns, Claude consistently omits the filter on roughly 20% of generated queries, especially in aggregate and count queries. The symptom is counts and sums that include deleted records. The CLAUDE.md rule: All queries against tables that have a deleted_at column MUST include WHERE deleted_at IS NULL in the filter, unless the query is explicitly auditing deleted records.
Permission hooks for destructive operations
MySQL migrations and schema commands have a clear risk hierarchy. Reading is safe. Generating migration files is safe. Applying migrations to production, running DROP, TRUNCATE, or DELETE without a WHERE clause are all gated. Claude Code's permission system handles this declaratively.
{
"permissions": {
"allow": [
"Bash(npx prisma migrate dev*)",
"Bash(npx prisma migrate status*)",
"Bash(npx prisma studio*)",
"Bash(npx prisma db pull*)",
"Bash(mysql * -e 'EXPLAIN*')",
"Bash(mysql * -e 'SHOW*')",
"Bash(mysql * -e 'SELECT*')"
],
"deny": [
"Bash(npx prisma migrate deploy*)",
"Bash(npx prisma db push*)",
"Bash(npx prisma migrate reset*)",
"Bash(mysql * -e 'DROP*')",
"Bash(mysql * -e 'TRUNCATE*')",
"Bash(mysql * -e 'DELETE FROM*')",
"Bash(mysql * -e 'ALTER TABLE*')",
"Bash(flyway migrate*)"
]
}
}
This config lets Claude generate migrations, inspect schema state, run EXPLAIN and SELECT queries freely. It blocks applying migrations to production (which needs a CI pipeline), running Prisma's db push command (which applies schema changes without generating a reviewable migration file), and any DDL or DML that modifies or destroys data. When Claude attempts a denied command, it surfaces the request for manual approval rather than executing it.
The prisma migrate reset block is important. That command drops and recreates the entire database. It is useful in local development and catastrophic anywhere else. Blocking it globally and allowing it conditionally with a --local flag pattern is the safest configuration. The Claude Code setup guide covers the full permissions configuration anatomy.
MySQL 8 specifics: CTEs, window functions, and JSON
MySQL 8.0 added CTE support, window functions, and improved JSON operators. Claude's training data spans MySQL 5.x and 8.x, so without explicit versioning in CLAUDE.md it sometimes falls back to MySQL 5.7 compatible patterns.
-- CTEs: use freely in MySQL 8.0+
WITH monthly_revenue AS (
SELECT
DATE_FORMAT(created_at, '%Y-%m') AS month,
SUM(total_cents) AS revenue_cents,
COUNT(*) AS order_count
FROM orders
WHERE deleted_at IS NULL
AND payment_status = 'paid'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
),
ranked_months AS (
SELECT
month,
revenue_cents,
order_count,
RANK() OVER (ORDER BY revenue_cents DESC) AS revenue_rank
FROM monthly_revenue
)
SELECT month, revenue_cents / 100.0 AS revenue_gbp, order_count, revenue_rank
FROM ranked_months
WHERE revenue_rank <= 12
ORDER BY revenue_rank;
-- Window functions: LAG, LEAD, ROW_NUMBER, RANK, DENSE_RANK all work in MySQL 8
SELECT
id,
total_cents,
created_at,
LAG(total_cents) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_total,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS order_num
FROM orders
WHERE deleted_at IS NULL;
-- JSON extraction and path queries (MySQL 8 syntax)
SELECT
id,
metadata->>'$.shipping_address.city' AS city,
metadata->>'$.shipping_address.postcode' AS postcode
FROM orders
WHERE metadata->>'$.shipping_method' = 'express'
AND deleted_at IS NULL;
The ->> operator (inline path + unquote) is MySQL 8's shorthand for JSON_UNQUOTE(JSON_EXTRACT(col, path)). Claude uses both forms; pin the ->> form in CLAUDE.md as the project standard for readability.
Recursive CTEs are also supported in MySQL 8 and are the correct pattern for hierarchical data (category trees, org charts, threaded comments). Claude generates recursive CTEs correctly when told the target version, and falls back to application-level tree walking without the version hint.
Conclusion
A MySQL 8.x project without a CLAUDE.md is a project where Claude might write CHARSET=utf8, propose a table-locking ALTER TABLE against a billion-row table, generate string concatenation instead of prepared statements, or produce queries that return soft-deleted records in your public API responses. None of these are detectable from the code alone without running it.
The CLAUDE.md template in this guide prevents each of those outcomes. It locks charset to utf8mb4, engine to InnoDB, establishes prepared statement patterns per stack, sets migration workflow commands so Claude follows the same steps every session, and defines the indexing rules that prevent N+1 queries and missing filters from shipping quietly.
The permission hooks gate every destructive command so that dropping a table or running a migration to production requires explicit human approval, not just a model that decided it knew what you meant.
If you are running a polyglot data layer with MySQL alongside Redis or MongoDB, the Claude Code database guide covers the inter-tool conventions. If you are choosing between ORMs for a new MySQL project, the Claude Code with Prisma guide and Claude Code with Drizzle guide cover both in depth. Claudify includes a MySQL-specific CLAUDE.md template as part of the Claude Code workflow kit, pre-configured for mysql2, Prisma, and the safety rules that keep a production MySQL database standing through agentic development sessions.
More like this
Ready to upgrade your Claude Code setup?
Get Claudify