Mastering MySQL Naming Conventions: From Schemas to Indexes

1. Database Object Naming Conventions

Database objects include tables, indexes, views, diagrams, defaults, rules, triggers, stored procedures, users, etc. Naming conventions apply to schemas, tables, indexes, constraints, and other objects.

2. Global Naming Rules

Use meaningful English words separated by underscores.

Names must start with a letter and contain only letters, numbers, and underscores.

Avoid MySQL reserved words such as backup, call, group.

All object names are lowercase for consistency.

3. Database Naming

Limit to 30 characters.

Use projectName_shortDescription (e.g., im_flow).

Specify default character set and collation (UTF8/utf8mb4).

Use lowercase.

4. Table Naming

Regular tables start with t_ followed by module and purpose (e.g., t_user_eduinfo).

Temporary tables: temp_ + module + purpose + date (e.g., temp_user_eduinfo_20210719).

Backup tables: bak_ + module + purpose + date.

Use consistent prefixes within a module, keep names meaningful, separate words with underscores, and stay under 30 characters.

5. Column Naming

Use clear English words or abbreviations, separated by underscores (e.g., service_ip, service_port).

Columns with the same meaning across tables must share the same name (e.g., create_time).

Separate multiple words with underscores.

Limit to 30 characters and use lowercase.

6. Index Naming

Unique indexes: uni_ + column name (e.g., uni_uid).

Non‑unique indexes: idx_ + column name(s) (e.g., idx_uname_mobile).

Separate words with underscores, keep names under 50 characters, and use lowercase.

Follow the left‑most prefix rule for composite indexes.

7. View Naming

Prefix with v (e.g., v_user_summary).

If based on a single table, use v_ + table name; for multi‑table joins, concatenate table names with underscores.

Keep under 30 characters, use lowercase, and avoid creating views unless required.

8. Stored Procedure Naming

Prefix with sp_, use underscores to separate words, reflect functionality, and stay under 30 characters.

Input parameters start with i_, output parameters with o_.

create procedure sp_multi_param(in i_id bigint, in i_name varchar(32), out o_memo varchar(100))

9. Function Naming

Prefix with func_, use underscores, reflect purpose, and stay under 30 characters.

create function func_format_date(ctime datetime)

10. Trigger Naming

Prefix with trig, add suffix _i, _u, or _d for insert, update, delete.

Keep names under 30 characters and lowercase.

DROP TRIGGER IF EXISTS trig_attach_log_d;</code>
<code>CREATE TRIGGER trig_attach_log_d AFTER DELETE ON t_dept FOR EACH ROW;

11. Constraint Naming

Unique constraints: uk_ + table + column (e.g., uk_user_name).

Foreign keys: fk_ + child table + parent table (e.g., fk_user_dept).

Prefer NOT NULL and default values; avoid foreign keys in favor of application‑level integrity.

12. User Naming

Production users: code_ + application (e.g., code_app).

Read‑only users: read_ + application.

13. Database Object Design Guidelines

Use InnoDB as the default storage engine; avoid MyISAM unless a specific need exists.

Prefer UTF8 or UTF8MB4 character sets; UTF8MB4 fully supports Unicode.

14. Table Design Guidelines

Minimize cross‑component table relationships; avoid foreign keys.

Design tables per component, not per whole system.

Every table should have a primary key (PK).

One column = one meaning; avoid duplicate columns.

Limit table size to ~16 GB and rows to ~20 million (adjust per workload).

Apply third normal form; use denormalization only when justified.

15. Column Design Guidelines

INT: use UNSIGNED, specify display length (e.g., id int(11) NOT NULL).

DATETIME: preferred over TIMESTAMP for most cases; TIMESTAMP is UTC‑aware.

VARCHAR: specify length, keep total row length < 65535 bytes, avoid overly long values.

TEXT: use only when > 20000 characters; store in separate tables.

DECIMAL for precise numeric values; avoid FLOAT/DOUBLE.

Prefer NOT NULL with default values; avoid BLOB unless necessary.

Auto‑increment fields must be unsigned INT or BIGINT and part of the PK.

16. Index Design Guidelines

Create indexes on high‑selectivity columns (selectivity > 0.2).

Follow the left‑most prefix rule for composite indexes.

Limit indexes per table to about five; prioritize read‑heavy workloads.

Use covering indexes for ORDER BY, GROUP BY, DISTINCT.

Use prefix indexes for TEXT columns.

Avoid functions or operations on indexed columns.

Prefer value% over %value for LIKE patterns.

PRIMARY KEY (`id`),
UNIQUE KEY `uni_brand_define` (`app_id`,`define_id`)

17. Constraint Design Guidelines

PK should be short, ordered, auto‑increment, and immutable.

Unique constraints use uk_ prefix.

Do not create foreign key constraints; enforce referential integrity in application code.

All columns should be NOT NULL with sensible defaults unless a special case applies.

18. SQL Usage Standards

18.1 SELECT Best Practices

Avoid SELECT *; list required columns explicitly.

Never use SELECT * without a WHERE clause.

Do not place TEXT columns with other fields; keep them in separate tables.

Avoid nondeterministic functions (e.g., NOW(), RAND()) in SELECT lists and WHERE clauses.

Always include ORDER BY in pagination queries.

Prefer IN() over multiple ORs; keep IN list < 300 items.

Use value% instead of %value for prefix searches.

Replace subqueries with JOINs when possible; subqueries often prevent index usage and create temporary tables.

18.2 DML Best Practices

Always specify column lists in INSERT statements.

Batch large UPDATE/DELETE/INSERT operations to avoid master‑slave replication lag.

When using row‑based binlog, be aware of increased log volume.