10 Essential MySQL Table Naming Rules Every Engineer Should Follow

Many companies maintain lengthy MySQL specifications that are hard to enforce; Alibaba’s forced MySQL table creation standard contains only ten concise rules that can be used as a reference.

Rule 1: Boolean Fields

Use the is_xxx naming pattern for fields representing a yes/no concept.

Data type must be unsigned tinyint.

Store 1 for true and 0 for false.

Example: is_deleted (1 = deleted, 0 = not deleted). Incorrect names include deleted, if_deleted, delete_or_not.

Rule 2: Letters and Numbers

Table and column names must be lowercase.

Do not start with numbers, and avoid double underscores that contain only numbers.

Correct: aliyun_admin, level3_name. Incorrect: AliyunAdmin, level_3_name.

Rule 3: Singular Table Names

Table names should represent an entity, not a quantity, so avoid plural forms.

Rule 4: Avoid Reserved Words

Do not use MySQL reserved keywords such as desc, range, match, delayed, etc.

Rule 5: Index Naming Conventions

Primary key: pk_xxx Unique index: uk_xxx Regular index:

idx_xxx

Rule 6: Decimal Type Guidelines

Use decimal for precise numeric values.

Avoid float and double due to precision loss.

If the required range exceeds decimal, split into integer and fractional parts stored separately.

Rule 7: Fixed‑Length Strings

When string lengths are very close, use fixed‑length char to pre‑allocate storage and avoid reallocation.

Rule 8: Variable‑Length Strings

Use varchar for long strings with large length variance to save space.

If most strings exceed 5000 characters, store them in a separate table using text.

Rule 9: Mandatory Fields

id

: bigint unsigned, auto‑increment, step 1, no business meaning. create_time: datetime (or timestamp if timezone info is needed). update_time: same type as create_time.

Rule 10: No Physical Deletions

Prefer logical deletions to retain data assets and enable operation traceability.

Does your company have a MySQL specification? Do you think these table‑creation rules are reasonable? Share your thoughts. Source: Architect’s Path.