MySQL 44th article ~ talk about the most basic specifications

A brief introduction: Today we talk about research and development norms

Two specifications
1 table
1 Choosing the right character set and storage engine recommended InnoDB UTF8MB4
2 table name do not include MySQL keyword, conform to naming convention
3 foreign keys are not recommended
4 Try not to use partition table, the use of sub-database table is the best solution
2 columns
1 To set comment comments for each column
2 Each column is best set to non-null, setting the default value
3 varchar and char selection fixed length Select char variable length select varchar, character length try not to be too large, otherwise maintenance and index creation will be limited later
4 datetime and timestamp selection 1 timestamp 1 default system time and UPDATE trigger attribute 2 time limit (1970-2038) 2datetime 1 Insert System time now () 2 No default time and update properties, 3 equals no time limit, 4 allows Is empty so this looks at the need definition
5 collection, enumeration, bitmap type type try not to choose
6 for the int type, as far as possible to standardize the definition of int bigint tinyint for refinement, can be used small do not use large, and the use of unsigned
7 Table primary key selection (1 auto-numeric primary key (recommended) 2 varchar type (do not choose as primary key unless required by business)), avoid federated primary keys, and can be constrained with a unique index
3 Index
1 indexing as much as possible when building a table
2 try to replace multiple single-row indexes with multiple-column indexes
3 Maximum length of the index
1 The sum of the maximum length of all index keys cannot exceed 1000 and is not the sum of the actual data length, but the sum of the length of the index key field definition.
2 because the index has a length limit, you have to limit the length of the character type.
The number of indexes in 41 tables is not too many, otherwise it will affect the efficiency of DML operation
5 The naming specification of the index should be logical, preferably unified as Idx_
6 Creating an Index Select columns to select columns with higher sensitivity

Three summary:

The specification for creating tables is referenced in the inception audit system and some of the usual experiences. Please correct me where there are deficiencies and errors.

MySQL 44th article ~ talk about the most basic specifications