Professional MySQL development design specifications and SQL writing specifications

In the process of team development, it is essential to formulate internal development and design specifications for the stability of the project, efficiency of the code, and convenience of management.

Here we share a list of MySQL development design specifications, including table design specifications, field design specifications, and SQL writing specifications.

Database object naming conventions

Database objects

The objects of naming standards refer to the naming conventions of database SCHEMA, table TABLE, index INDEX, constraint CONSTRAINTS, etc.

Database object naming principles

Use meaningful English words in the name, with underscores separating the words

Only English letters, numbers, and underscores can be used in names

Avoid using MySQL reserved words such as call, group, etc.

All database objects use lowercase letters

Database naming conventions

The database name cannot exceed 30 characters.

The database name must be the project English name or a meaningful abbreviation

The default character set and collation clauses must be added when the database is created. The default character set is UTF8 (the migrated dumbo uses utf8mb4)

Names should be lowercase

Table naming conventions

Tables in the same module should use the same prefix as much as possible, and the table names should be as expressive as possible.

Multiple words are separated by underscores (_).

Table names cannot exceed 30 characters

Ordinary table names start with t_, which means table. The naming rule is t_module name (or meaningful abbreviation)_+table_name

Temporary table (intermediate table temporarily used by operations, development or database personnel for data collection) Naming rules: add tmp prefix and 8-digit time suffix (tmp_test_user_20181109)

Backup table (DBA backup is used as an intermediate table to store historical data) naming rules: add bak prefix and 8-digit time suffix (bak_test_user_20181109)

Names should be lowercase

Field naming conventions

Field names must be English words or abbreviations that represent their actual meanings, and words must be connected with underscores (_).

Fields with the same meaning between tables must have the same name

Field names cannot exceed 30 characters.

User Naming Conventions

The user naming format used in production is code_application

The naming rule for read-only users is read_application

Database object design specifications

Storage engine selection

If there is no special requirement, the innodb storage engine must be used

Character set selection

If there is no special requirement, utf8 or utf8mb4 must be used

Table design specifications

The associations between database tables corresponding to different applications should be reduced as much as possible. Foreign keys are not allowed to be used to associate tables. This ensures the independence of tables corresponding to components and provides the possibility for reconstruction of the system or table structure.

The table design should not be done for the entire system, but should be done based on the components in the system architecture and for the business handled by each component.

The table must have a PK

A field has only one meaning

Table should not have duplicate columns

Complex data types (arrays, custom, etc.) are not allowed

The data types of the fields that need to be joined (join keys) must be absolutely consistent to avoid implicit conversion

The design should at least meet the third normal form and minimize data redundancy. Some special scenarios allow for denormalized design, but the design of redundant fields needs to be explained during project review

The TEXT field must be placed in a separate table and associated with the main table using a PK. If there is no special need, TEXT and BLOB fields are prohibited.

Tables that need to regularly delete (or transfer) expired data can be solved by splitting tables

The number of fields in a single table should not be too many. It is recommended that the maximum number should not exceed 50.

When MySQL processes large tables, its performance begins to degrade significantly, so it is recommended that the physical size of a single table be limited to 16GB and the data in the table be controlled within 20 million.

If the data volume or data growth is large in the early planning, then the table splitting strategy should be included in the design review.

Without special requirements, it is strictly forbidden to use partition tables

Field design specifications

INT: If there is no special need, use the UNSIGNED INT type to store integer numbers. The number after the integer field represents the display length

DATETIME: All fields that need to be accurate to the time (hour, minute, and second) should use DATETIME, not TIMESTAMP type.

VARCHAR: All dynamic-length strings use the VARCHAR type. Fields of limited categories, such as status, also use strings that can clearly express the actual meaning, and should not be replaced by numbers such as INT; VARCHAR(N), N represents the number of characters rather than the number of bytes. For example, VARCHAR(255) can store up to 255 characters (including English letters, Chinese characters, special characters, etc.). But N should be as small as possible, because the maximum length of all VARCHAR fields in a MySQL table is 65535 bytes, and the number of stored characters is determined by the selected character set. For example, UTF8 requires a maximum of 3 bytes to store a character, so varchar should not exceed 21845 characters when storing characters that take up 3 bytes. At the same time, when performing memory operations such as sorting and creating temporary tables, the length of N is used to request memory. (Unless there is a special need, in principle a single varchar field is not allowed to contain more than 255 characters)

TEXT: The TEXT type can be used to store character data only when the number of characters may exceed 20,000, because all MySQL databases use the UTF8 character set. All fields using the TEXT type must be separated from the original table and stored in another table together with the original table primary key. Unless there is a special need, developers are strictly prohibited from using MEDIUMTEXT, TEXT, and LONGTEXT types.

For accurate floating-point data storage, DECIMAL must be used, and FLOAT and DOUBLE are strictly prohibited.

Unless there is a special need, developers are strictly prohibited from using BLOB types.

If there is no special need, it is recommended to use the NOT NULL attribute for the field, and the default value can be used instead of NULL

The auto-increment field type must be integer and must be UNSIGNED. The recommended type is INT or BIGINT. The auto-increment field must be the primary key or part of the primary key.

Index design specifications

The index must be created on a column with high selectivity. The selectivity is calculated as follows: select count(distinct(col_name))/count(*) from tb_name; if the result is less than 0.2, it is not recommended to create an index on this column, otherwise it will most likely slow down SQL execution.

The first field of the composite index must be in the where condition. For multiple fields that need to form a composite index, it is recommended to put the field with high selectivity at the front.

Foreign keys are not allowed

If you need to create an index for a Text type field, you must use a prefix index.

Theoretically, the number of indexes for a single table should be controlled within 5. If you often insert or update tables in large quantities, you should create as few indexes as possible.

ORDER BY, GROUP BY, and DISTINCT fields need to be added after the index to form a covering index.

Try to use Btree indexes instead of other types of indexes.

Constraint design specifications

PK should be ordered and meaningless, customized by developers as much as possible, as short as possible, and use an auto-incrementing sequence.

If there is a unique constraint in addition to the PK in the table, you can create a unique constraint index with "uidx_" as the prefix in the database.

PK fields cannot be updated.

Creation of foreign key constraints is prohibited. Foreign key constraints are controlled by the application.

Unless otherwise required, all fields must be added with non-null constraints, that is, not null .

Unless otherwise required, all fields must have default values.

SQL Writing Standards

Try to avoid using select * . Using select * in join statements may cause queries that only need to access the index to be completed to need to go back to the table to retrieve data.

It is strictly forbidden to use select * from table without any where condition

When storing text type fields in MySQL, they are not stored together with records consisting of fields of other common field types, and the reading efficiency itself is not as good as that of common field blocks. If you do not need to retrieve the text field and use select * , the SQL that completes the same function will consume much more IO, and the IO efficiency of the added part will also be lower.

You can use related functions on the extracted fields, but you should avoid using functions with uncertain results such as now() , rand() , sysdate() , current_user() , etc. It is strictly forbidden to use any functions, including data type conversion functions, on the filter condition fields in the Where condition.

All connected SQL statements must be connected using Join ... On ... method, and are not allowed to be directly connected using the normal Where condition method. For outer join SQL statements, you can use the Join method of Left Join On , and all outer joins should be written as Left Join instead of Right Join .

All paging query statements need to have sorting conditions, unless the application explicitly requests not to use any sorting to randomly display data.

Mathematical or functional operations on index columns are strictly prohibited in WHERE conditions.

Replace or with in() / union , and make sure the number of ins is less than 300.

It is strictly forbidden to use the % prefix for fuzzy prefix queries: such as: select id,val from table where val like '%name'; you can use % fuzzy suffix queries such as: select id,val from table where val like 'name%'

It is strictly forbidden to use INSERT ON DUPLICATE KEY UPDATE , REPLACE INTO , and INSERT IGNORE

This article is just a starting point. Every team has its own development and design specifications. MySQL development and design specifications are not limited to these. I hope this article will inspire you.