mysql-patterns
/mysql-patterns
Use when designing MySQL schema features or applying advanced MySQL patterns in Laravel — upserts, JSON columns, full-text search, partitioning, replication/read-write splitting, and deadlock handling — beyond the query tuning already in the SQL rules.
- Author
- pekral ↗
- Repository
- cursor-rules ↗
- Licence
- MIT
- Last repository change
- 03.10.2026
Assessment and security
- Files in this package
- 1
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 03.10.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
What it does and when to use it
Use when designing MySQL schema features or applying advanced MySQL patterns in Laravel — upserts, JSON columns, full-text search, partitioning, replication/read-write splitting, and deadlock handling — beyond the query tuning already in the SQL rules.
Original author description
Use when designing MySQL schema features or applying advanced MySQL patterns in Laravel — upserts, JSON columns, full-text search, partitioning, replication/read-write splitting, and deadlock handling — beyond the query tuning already in the SQL rules.
How it works
- Constraints
- Use when
- Schema Standard in Laravel Migrations
- Upserts
- JSON Columns + Indexes
- Full-Text Search
- Generated / Virtual Columns
- Partitioning
- Replication & Read/Write Splitting
- Deadlock Retry
SKILL.md preview
---
name: mysql-patterns
description: "Use when designing MySQL schema features or applying advanced MySQL patterns in Laravel — upserts, JSON columns, full-text search, partitioning, replication/read-write splitting, and deadlock handling — beyond the query tuning already in the SQL rules."
license: MIT
metadata:
author: "Petr Král (pekral.cz)"
---
# MySQL Patterns
## Constraints
- Apply `@rules/sql/optimalize.mdc` — it already owns indexing, SARGable WHERE, seek/keyset pagination, EXPLAIN, transactions/locking basics, batch-over-per-row, CTE/window/recursive queries, schema basics, DB-level caching, and the **Performance Non-Regression on Query Changes** gate. Do not re-explain those here; defer to it. When a pattern in this skill *changes an existing query* (e.g. swapping `LIKE` for a FULLTEXT match, moving a filter onto a generated column, introducing partition pruning), capture the original query's baseline and confirm the new shape is equal or faster — if it is slower, document the reason and the remaining optimization options per that gate.
- For diagnosing an existing slow query, use `@skills/mysql-problem-solver/SKILL.md`. This skill is for *designing* features, not investigating regressions.
- If the project uses Laravel, also apply `@rules/laravel/laravel.mdc` and `@rules/laravel/architecture.mdc`.
- Apply `@rules/security/backend.md` — parameterized queries / ORM only, least-privilege DB users, never hardcode credentials.
- `final` classes, `declare(strict_types=1)`, Pest tests for any data-access code added.
- Verify the engine/version before using a version-specific feature (`SELECT VERSION();`). MySQL 8 and MariaDB diverge on JSON, `ON DUPLICATE KEY` aliases, and `SKIP LOCKED`.
## Use when
- Adding upserts, JSON columns, full-text search, generated columns, or partitioning to a Laravel app.
- Writing a migration that creates a new table and needs the schema standard expressed in Blueprint.
- Setting up read/write splitting against replicas, or hardening against deadlocks and connection exhaustion.
- Reviewing a migration that introduces any of the above on a large table.
These topics complement the query-tuning rules; they are not covered there.
## Schema Standard in Laravel Migrations
`@rules/sql/optimalize.mdc` *Schema Design* owns the standard (singular names, `NOT NULL` defaults, `_at` / `_date` suffixes, `DATETIME` over `TIMESTAMP`, adjective booleans, justified `VARCHAR` widths, `DECIMAL` money, `ON DELETE` by relation nature, `CHECK` invariants, `utf8mb4_0900_*`, PK width). This section only maps it onto Blueprint, where several defaults contradict it.
```php
Schema::create('post', function (Blueprint $table): void { // singular — Eloquent needs $table = 'post';
$table->increments('id'); // INT UNSIGNED; ->id() would give BIGINT
$table->string('slug', 100)->charset('utf8mb4')->collation('utf8mb4_0900_bin');
$table->string('meta_title', 160)->default(''); // NOT NULL + domain default
$table->boolean('published')->default(false); // adjective, positive polarity
$table->decimal('price', 9, 2); // money is never float/double
$table->date('invoice_date'); // calendar day → DATE
$table->dateTime('published_at')->nullable();
$table->dateTime('created_at');
$table->dateTime('updated_at')->useCurrent()->useCurrentOnUpdate();
$table->unsignedInteger('author_id'); // same width as user.id
$table->foreign('author_id', 'fk_post_author') // name the constraint yourself
->references('id')->on('user')
->restrictOnDelete()->cascadeOnUpdate();
$table->index(['author_id', 'published_at'], 'idx_post_author_published');
});
```
- `$table->timestamps()` emits **`TIMESTAMP`** columns — the 2038 + session-zone trap. Declare `dateTime('created_at')` / `dateTime('updated_at')` explicitly.
- **Pair the FK width to the PK it references.** `$table->id()` and `$table->foreignId()` are both `BIGINT UNSIGNED`; `increments()` is `INT UNSIGNED`. So `id()` pairs with `foreignId()->constrained()`, and `increments()` pairs with `unsignedInteger()` + `foreign()`. A mixed pair makes MySQL reject the constraint (errno 3780, incompatible column types).
- `constrained()` / `foreign()` generate a name like `post_author_id_foreign` — pass the constraint name (`constrained(table: 'user', indexName: 'fk_post_author')`, or the second argument of `foreign()`) so the schema keeps the naming the rule requires.
- FK actions read directly off the relation: `cascadeOnDelete()` (composition), `restrictOnDelete()` (association), `nullOnDelete()` (meaningful detachment).
- Blueprint has no DSL for `CHECK` constraints, triggers, or `COMMENT` on a magically maintained column — use `DB::statement(...)` and name the constraint after the rule:
```php
DB::statement('ALTER TABLE `customer_order` ADD CONSTRAINT `chk_order_shipped_needs_date`
CHECK (`status` <> \'shipped\' OR `shipped_at` IS NOT NULL)');
```
- Set the connection charset/collation and strict mode once in `config/database.php`: `'charset' => 'utf8mb4'`, `'collation' => 'utf8mb4_0900_ai_ci'`, `'strict' => true` (or an explicit `'modes' => [...]` list). MariaDB has no `utf8mb4_0900_*` family — verify with `SELECT VERSION();` before pinning it.
- An `updated_at` maintained by `ON UPDATE CURRENT_TIMESTAMP` and one maintained by Eloquent are two mechanisms for one column — pick one (`public $timestamps = false;` when the DB owns it) so the row version is not written twice with different values.
## Upserts
Pick the narrowest tool. All run as single statements — never loop per row.
```php
// Insert-or-update many rows in ONE statement.
// 2nd arg = unique-by columns; 3rd = columns to overwrite on conflict.
Product::upsert(
[['sku' => 'A1', 'price' => 10], ['sku' => 'B2', 'price' => 20]],
uniqueBy: ['sku'],
update: ['price'],
);
// Insert, silently skip rows that violate a unique key. No update.
DB::table('tags')->insertOrIgnore([['name' => 'php'], ['name' => 'sql']]);
// Single row: fetch-or-create-then-update. Fires model events; runs SELECT + INSERT/UPDATE.
Product::updateOrCreate(['sku' => 'A1'], ['price' => 10]);
```
- `upsert()` / `insertOrIgnore()` are bulk and do **not** fire model events or touch timestamps automatically (set them yourself). Prefer them for large batches.
- `updateOrCreate()` is per-row, fires events, and is race-prone under concurrency unless a unique index backs the match columns. Wrap in a deadlock retry (below) when concurrent.
- Raw form: `INSERT ... ON DUPLICATE KEY UPDATE price = VALUES(price)` (MariaDB) / `... AS new ON DUPLICATE KEY UPDATE price = new.price` (MySQL 8, `VALUES()` deprecated).
- A unique index on the conflict columns is mandatory or the upsert degrades to plain inserts.
## JSON Columns + Indexes
JSON columns cannot be indexed directly. Index a **generated column** extracted from the JSON, or a multi-valued index for arrays.
```php
Schema::table('products', function (Blueprint $table): void {
$table->json('attributes');
// Stored generated column promotes attributes->'$.color' to an indexable scalar.
$table->string('color')->storedAs("attributes->>'$.color'");
$table->index('color');
});
```
```php
// Querying JSON paths in Eloquent:
Product::where('attributes->color', 'red')->get();
Product::whereJsonContains('attributes->tags', 'sale')->get();
```
- `->>` (or `JSON_UNQUOTE(JSON_EXTRACT(...))`) returns the unquoted scalar; index that, not the raw `->`.
- `VIRTUAL` columns compute on read (no storage, index still allowed); `STORED` persists (faster read, costs disk + write). Default to `VIRTUAL` unless the column is read-heavy.
- Validate JSON shape in the application; MySQL only enforces well-formedness, not structure.
## Full-Text Search
For natural-language search on TEXT columns, a `FULLTEXT` index beats `LIKE '%term%'` (which is non-SARGable, see `@rules/sql/optimalize.mdc`).
```php
Schema::table('articles', function (Blueprint $table): void {
$table->fullText(['title', 'body']);
});
```
```php
// Boolean mode supports +required -excluded "phrase" operators.
Article::whereFullText(['title', 'body'], 'laravel +redis', ['mode' => 'boolean'])->get();
// Or raw:
Article::whereRaw(
'MATCH(title, body) AGAINST(? IN NATURAL LANGUAGE MODE)',
['laravel redis'],
)->get();
```
- Default `innodb_ft_min_token_size` is 3 — shorter terms are ignored unless you lower it and rebuild the index.
- FULLTEXT covers basic relevance ranking only. For typo tolerance, facets, or large corpora, reach for a dedicated engine (Meilisearch/Elasticsearch via Laravel Scout). Note the tradeoff; do not over-engineer for small tables.
## Generated / Virtual Columns
Beyond JSON, generated columns normalize derived values so they stay consistent and become indexable.
```php
$table->decimal('net', 15, 2);
$table->decimal('vat_rate', 5, 4);
$table->decimal('gross', 15, 2)->virtualAs('net * (1 + vat_rate)');
```
- A generated column cannot reference another generated column defined after it, nor non-deterministic functions (`NOW()`, `RAND()`).
- Use them to enforce an invariant in one place instead of recomputing it in every query.
## Partitioning
Partitioning splits one logical table across physical segments to prune scans and cheapen bulk drops. Use it for genuinely large, time- or range-sliced tables — not by default.
```sql
-- RANGE: time-series; drop old data instantly with ALTER TABLE ... DROP PARTITION.
CREATE TABLE events (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at) -- partition key MUST be in every unique key
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- LIST: discrete buckets (e.g. region/tenant).
PARTITION BY LIST (region_id) (
PARTITION eu VALUES IN (1, 2, 3),
PARTITION us VALUES IN (4, 5)
);
```
- Every UNIQUE / PRIMARY key must include the partition column — this often forces a composite PK and reshapes the schema. Decide early.
- Partition pruning only triggers when the partition key appears in the `WHERE`; a query without it scans every partition.
- Laravel migrations have no partition DSL — use `DB::statement(...)` with raw SQL.
## Replication & Read/Write Splitting
Route reads to replicas, writes to the primary, with a single connection config.
```php
// config/database.php — 'mysql' connection
'read' => ['host' => ['10.0.0.2', '10.0.0.3']],
'write' => ['host' => ['10.0.0.1']],
'sticky' => true,
```
- `sticky => true` routes reads back to the **write** host for the rest of the request after any write, so a just-written row is read back consistently despite replication lag. Keep it on for typical web flows.
- Force a specific connection when needed: `Model::on('mysql::read')` or `DB::connection('mysql')->select(...)`.
- Replicas are eventually consistent. Never read a balance/counter you just wrote from a replica without `sticky` or an explicit write-connection read.
## Deadlock Retry
InnoDB aborts one transaction in a deadlock (SQLSTATE `40001`, error `1213`). The correct response is to **retry the whole transaction**, not to catch-and-continue.
```php
DB::transaction(function (): void {
// ... ordered, consistent locking; lock rows in a stable order to reduce deadlocks
}, attempts: 3); // Laravel retries the closure on deadlock up to `attempts` times.
```
- Keep transactions short, lock rows in a consistent order across code paths, and touch the fewest rows possible.
- Idempotency matters: the closure runs up to N times, so it must be safe to re-run.
- See `@rules/sql/optimalize.mdc` for the transaction/locking fundamentals this builds on.
## Connection Config & Timeouts
```php
// config/database.php 'mysql' → 'options'
PDO::ATTR_TIMEOUT => 3, // connect timeout (seconds)
PDO::ATTR_PERSISTENT => false, // avoid stale persistent conns behind PgBouncer-style poolers
```
- Set `wait_timeout` / `interactive_timeout` server-side to reclaim idle connections; size `max_connections` to `(workers + queue workers + scheduler) × pool`.
- Enable the slow query log (`slow_query_log = 1`, `long_query_time = 1`) to surface candidates for `@skills/mysql-problem-solver/SKILL.md`.
## Diagnostics Cheatsheet
```sql
SHOW ENGINE INNODB STATUS\G -- LATEST DETECTED DEADLOCK + lock waits
SHOW FULL PROCESSLIST; -- live queries, state, time, lock waits
SHOW INDEX FROM orders; -- existing indexes (verify before adding one)
SELECT * FROM information_schema.innodb_trx; -- open transactions
SELECT * FROM performance_schema.data_lock_waits; -- who blocks whom (MySQL 8)
SELECT table_name, data_length, index_length
FROM information_schema.tables WHERE table_schema = DATABASE(); -- sizes
```
## Done when
- The chosen feature is verified against the actual engine/version.
- Any pattern that changed an existing query was benchmarked against its baseline and is equal or faster; a slower result carries the documented reason and remaining optimization options (`@rules/sql/optimalize.mdc` "Performance Non-Regression on Query Changes").
- Upserts run as single statements with a backing unique index; concurrent ones are deadlock-retried.
- JSON / FULLTEXT / partition queries are confirmed to hit the intended index (EXPLAIN — see `@rules/sql/optimalize.mdc`).
- Read/write splitting keeps `sticky` on for read-after-write paths.
- Pest tests cover the new data-access paths; no secrets are hardcoded.
Versions
| Repository | Score | Licence |
|---|---|---|
| This page · pekral/cursor-rules | 100 | MIT |
| affaan-m/ECC | 100 | MIT |
| SHRIJANAGAIN/Nemo-Cli | 100 | Apache-2.0 |
Database snapshot: 13.11.2026 · package version b80909ae2004 (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 100mysql-1213
排查 MySQL 报错「Deadlock found when trying to get lock; try restarting transaction」。当用户贴出包含这条报错的输出、或问这条报错怎么解决时使用。只读排查优先,不需要也不收集数据库密码。 - 100sql-expert
Expert in SQL query writing, optimization, and database design. Supports PostgreSQL, MySQL, SQLite, and BigQuery. Use for complex queries, performance tuning, and schema design. - 90dba
Database Administrator / Database Specialist (/dba) — operational database design and tuning: schema design & normalization, indexing strategy, query optimization & execution plans, migrations (safe, zero-downtime), replication & high availability, partitioning/sharding, backups… - 100laravel-migrations
Use when designing a database schema or managing Laravel 13 migrations — Schema Builder, columns, indexes, foreign keys, or seeders. - 90mysql
Plan and review MySQL/InnoDB schema, indexing, query tuning, transactions, and operations. Use when creating or modifying MySQL tables, indexes, or queries; diagnosing slow/locking behavior; planning migrations; or troubleshooting replication and connection issues. Load when using a MySQL database. - 100mysql-2003
排查 MySQL 报错「Error number: 2003; Symbol: CR_CONN_HOST_ERROR; Message: Can't connect to MySQL server on '%s:%u' (%d)」。当用户贴出包含这条报错的输出、或问这条报错怎么解决时使用。只读排查优先,不需要也不收集数据库密码。