Name: MySQL
Availability: InStock
Author: ivangdavila

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

MySQL

AI Skill Market Insights

Be Part of the 0+ Developer Community

Quick Reference

Character Set Traps

Index Differences from PostgreSQL

UPSERT Patterns

Locking Traps

GROUP BY Strictness

InnoDB vs MyISAM

Query Quirks

Connection Management

Replication Awareness

Performance

Quick Start

Manual Installation

TEAR & SHARE

Tags

Skills of A-share market data released by ft.tech.

AI Data Analysis

Options Strategy Advisor

Analytics Platform Base

Simul8or Trader

Channels

Learn

Compare

Company