MySQLMySQL查询编写

Quick Reference

Character Set Traps

• - utf8 is broken—only 3 bytes, can't store emoji; always use INLINECODE5
• INLINECODE6 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
• INLINECODE13 for auto-increment—no RETURNING clause like PostgreSQL
• INLINECODE14 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
• INLINECODE17 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
• INLINECODE20 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 INLINECODE22
• Mixed engines in JOINs work but lose transaction guarantees

Query Quirks

• - LIMIT offset, count different order than PostgreSQL's INLINECODE24
• INLINECODE25 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
• INLINECODE32 instead of COALESCE for two args—though COALESCE works

Connection Management

• - wait_timeout kills idle connections—default 8 hours; pooler may not notice
• INLINECODE35 default 151—often too low; each uses memory
• Connection pools: don't exceed max_connections across all app instances
• INLINECODE36 to see active connections—kill long-running with INLINECODE37

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
• INLINECODE40 for fragmented tables—locks table; use pt-online-schema-change for big tables
• INLINECODE41—set to 70-80% of RAM for dedicated DB server