mysql-auto

MySQL Best Practices Auto Rule

Critical Rules

• Naming Conventions: Use descriptive, snake_case names for databases, tables, columns, indexes, and constraints (e.g., user_profiles, fk_user_profiles_user_id). Avoid abbreviations and reserved keywords; if unavoidable, use backticks (e.g., `order`).
• SELECT Specificity: Avoid SELECT *. Always specify required columns.
• Security: Prefer parameterized queries (driver-level). Do not build SQL via string concatenation. Use prepared statements to prevent SQL injection.
• Performance: Index columns used in JOIN and WHERE predicates. Prefer INNER JOIN over correlated subqueries when possible.
• Engine/Charset: Use ENGINE=InnoDB for transactional tables and set DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci (or utf8mb4_0900_ai_ci on MySQL 8.0+).
• Transactions: Group related writes in transactions. Avoid implicit autocommit for multi-statement operations.
• Data Types: Use precise types (INT UNSIGNED for identifiers, DECIMAL(precision,scale) for money). Avoid TEXT/LONGTEXT unless necessary. Prefer BOOLEAN (TINYINT(1)) for flags.
• Timestamps: Use TIMESTAMP/DATETIME with DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP where appropriate.
• Joins: Use explicit JOIN syntax; avoid implicit comma joins.
• Keep migrations idempotent and forward-only; include rollback plans where feasible.

Schema Creation

Design schemas for correctness first, then performance. Apply the following when creating or altering schemas:

• Version-Specific Charset/Collation

  • MySQL 8.0+: Prefer utf8mb4 with utf8mb4_0900_ai_ci (or _as_cs for case-sensitive needs). Avoid legacy collations like utf8_general_ci.
  • MySQL 5.7: Prefer utf8mb4 with utf8mb4_unicode_ci. Avoid utf8 (3-byte) and latin1 unless strictly required for legacy data.
  • Suggest migration to utf8/latin1 schemas to utf8mb4 proactively to support full Unicode and emojis.
  • If a project already has MySQL setup, keep the collation and charset choices of the project