MySQL

Data Types

• UNSIGNED integers → only positive, double the max value range.
• SIGNED integers → supports negative numbers.
• Use INT unless you need big values → BIGINT for > 2 billion.

AUTO_INCREMENT

• Gaps happen if rows are deleted or transactions fail — can’t easily “fill holes”.
• Reset: ALTER TABLE table_name AUTO_INCREMENT = 1;

Row & Column Limits

• Max columns: 4096 (practically much less).
• Max row size: ~65,535 bytes logical limit, InnoDB often ≤ 8126 bytes (per row in index page).
• Large VARCHAR with utf8mb4 (4 bytes/char) can quickly hit limits → consider TEXT.

Common Errors

• Lock wait timeout: Increase innodb_lock_wait_timeout or optimize queries.
• Row size too large: Reduce column size, use TEXT, change ROW_FORMAT.
• Incorrect string value: Mismatched character sets, ensure utf8mb4 all the way.

Encoding

• utf8 in MySQL = 3-byte subset (no emojis).
• utf8mb4 = full Unicode (emojis, rare symbols).

InnoDB Notes

• Default page size: 16KB (check with SHOW VARIABLES LIKE 'innodb_page_size';).
• Row format: DYNAMIC or COMPRESSED helps with large rows.