Database Table Usage

All created MySQL tables must use the InnoDB engine.
The decimal type must be DECIMAL. Do not use FLOAT or DOUBLE.

FLOAT and DOUBLE have lower precision than DECIMAL and may cause rounding errors. If a value to be stored is beyond the range of DECIMAL, split the value into INTEGER and DECIMAL parts and store them separately.
The following reserved words cannot be used: desc, range, match, and delayed. For details, see MySQL official website.
Every data table can have a primary key. The primary key can be an ordered and unique field related to business or an auto-increment field unrelated to business.
Each table field must have a default value and NOT NULL. If the field is the numeric type, use 0 as its default value. If the field is the character type (such as VARCHAR), use an empty string (").

No primary key may cause slow execution of the primary database and replication delay.
You are not advised to use partitioned tables. If necessary, use multiple independent tables.
Disadvantages of partitioned tables:
- All partitions will be locked during DDL operations. As a result, operations on the partitions will be blocked.
- When a partitioned table contains a large amount of data, it is difficult and risky to perform DDL or other O&M operations on the table.
- Partition tables are seldom used, which may cause unknown risks.
- When a single server is poor in performance, splitting a partitioned table is expensive.
- When all partitions are accessed due to improper operations on a partitioned table, severe performance problems may occur.
Each table contains two DATETIME fields: create_time and update_time.

You can obtain the required data from a data warehouse based on these two fields without consulting services.

When an exception occurs in the database, you can use the two fields to determine the time when the data is inserted and updated. In extreme cases, you can determine whether to restore data based on the fields.
Sharding is recommended only when the number of rows in a single table exceeds 5 million or the size of a single table exceeds 2 GB.
VARCHAR is a variable-length character data type. The length of VARCHAR cannot exceed 2,048.
If the length of a field exceeds 2,048, define the field type as TEXT or create an independent table and use a primary key to associate the related tables. In this way, the index efficiency of other fields is not affected.
The length of a single row in a table cannot exceed 1,024 bytes and the number of rows in a single table cannot exceed 5 million.
The maximum number of fields in a single table is 50.
If the lengths of strings are almost the same, use the fixed-length character strings.
On the premise of ensuring data consistency, cross-table redundant fields are allowed to avoid associated queries and improve query performance.
Redundant fields must comply with the following rules:
- Fields are not frequently modified.
- Fields are not larger VARCHAR and TEXT.
The data types with proper storage size can save database tablespace and index storage space while improving the search speed. LONG TEXT and BLOB are not recommended.