MySQL

---
name: MySQL
description: Write correct MySQL queries with proper character sets, indexing, transactions, and production patterns.
license: MIT
---

## Quick Reference

| Topic | File |
|-------|------|
| Index design deep dive | `indexes.md` |
| Transactions and locking | `transactions.md` |
| Query optimization | `queries.md` |
| Production config | `production.md` |

## Character Set Traps

- `utf8` is broken—only 3 bytes, can't store emoji; always use `utf8mb4`
- `utf8mb4_unicode_ci` for case-insensitive sorting; `utf8mb4_bin` for exact byte comparison
- Collation mismatch in JOINs kills performance—ensure consistent collation across tables
- Connection charset must match: `SET NAMES utf8mb4` or connection string parameter
- Index on utf8mb4 column larger—may hit index size limits; consider prefix index

## Index Differences from PostgreSQL

- No partial indexes—can't `WHERE active = true` in index definition
- No expression indexes until MySQL 8.0.13—must use generated columns before that
- TEXT/BLOB needs prefix length: `INDEX (description(100))`—without length, error
- No INCLUDE for covering—add columns to index itself: `INDEX (a, b, c)` to cover c
- Foreign keys auto-indexed only in InnoDB—verify engine before assuming

## UPSERT Patterns

- `INSERT ... ON DUPLICATE KEY UPDATE`—not standard SQL; needs unique key conflict
- `LAST_INSERT_ID()` for auto-increment—no RETURNING clause like PostgreSQL
- `REPLACE INTO` deletes then inserts—changes auto-increment ID, triggers DELETE cascade
- Check affected rows: 1 = inserted, 2 = updated (counter-intuitive)

## Locking Traps

- `SELECT ... FOR UPDATE` locks rows—but gap locks may lock more than expected
- InnoDB uses next-key locking—prevents phantom reads but can cause deadlocks
- Lock wait timeout default 50s—`innodb_lock_wait_timeout` for adjustment
- `FOR UPDATE SKIP LOCKED` exists in MySQL 8+—queue pattern
- InnoDB default isolation is REPEATABLE READ, not READ COMMITTED like PostgreSQL
- Deadlocks are expected—code must catch and retry, not just fail

## GROUP BY Strictness

- `sql_mode` includes `ONLY_FULL_GROUP_BY` by default in MySQL 5.7+
- Non-aggregated columns must be in GROUP BY—unlike old MySQL permissive mode
- `ANY_VALUE(column)` to silence error when you know values are same
- Check sql_mode on legacy databases—may behave differently

## InnoDB vs MyISAM

- Always use InnoDB—transactions, row locking, foreign keys, crash recovery
- MyISAM still default for some system tables—don't use for application data
- Check engine: `SHOW TABLE STATUS`—convert with `ALTER TABLE ... ENGINE=InnoDB`
- Mixed engines in JOINs work but lose transaction guarantees

## Query Quirks

- `LIMIT offset, count` different order than PostgreSQL's `LIMIT count OFFSET offset`
- `!=` and `<>` both work; prefer `<>` for SQL standard
- No transactional DDL—`ALTER TABLE` commits immediately, can't rollback
- Boolean is `TINYINT(1)`—`TRUE`/`FALSE` are just 1/0
- `IFNULL(a, b)` instead of `COALESCE` for two args—though COALESCE works

## Connection Management

- `wait_timeout` kills idle connections—default 8 hours; pooler may not notice
- `max_connections` default 151—often too low; each uses memory
- Connection pools: don't exceed max_connections across all app instances
- `SHOW PROCESSLIST` to see active connections—kill long-running with `KILL <id>`

## Replication Awareness

- Statement-based replication can break with non-deterministic functions—UUID(), NOW()
- Row-based replication safer but more bandwidth—default in MySQL 8
- Read replicas have lag—check `Seconds_Behind_Master` before relying on replica reads
- Don't write to replica—usually read-only but verify

## Performance

- `EXPLAIN ANALYZE` only in MySQL 8.0.18+—older versions just EXPLAIN without actual times
- Query cache removed in MySQL 8—don't rely on it; cache at application level
- `OPTIMIZE TABLE` for fragmented tables—locks table; use pt-online-schema-change for big tables
- `innodb_buffer_pool_size`—set to 70-80% of RAM for dedicated DB server