MySQL Database Specifications and principles

1. database table name naming specification
        The use of 26 letters (case-sensitive) and 0-9 of the natural number (often do not need) with an underscore ' _ ' composition;        The name is concise and clear, and multiple words are separated by the underscore ' _ ';        For example: User_login, User_profile, User_detail, User_role, User_role_relation, User_role_right            , User_role_right_ The Relation        table prefix ' User_ ' can effectively show the tables of the same relationship together;

        The use of 26 letters (case-sensitive) and 0-9 of the natural number (often do not need) with an underscore ' _ ' composition;        The name is concise and clear, and multiple words are separated by the underscore ' _ ';        For example: User_login table field user_id, user_name, Pass_word, Eamil, TickIT, status, Mobile, add_time;        There must be a self-increment primary key in each table, and the Add_time (default system time) table has the        same name as the associated field names between tables;

3. Use as few storage space as possible to save data of one field;

        For example, if you can use int instead of varchar, char, you can use varchar (16) to avoid using varchar ("n");        The IP address preferably uses the int type;        Fixed-length types are best used with char, for example: postal code;        Do not use Smallint,int if you can use tinyint;        It is best to give each field a default value, preferably not null;

4. Field class principle

        Use a good value type (save space with the appropriate field type);        Convert characters into numbers (the best conversions that can be transformed, as well as saving space and improving query performance);        Avoid using null fields (Null fields are difficult to query for optimizations, NULL field indexes require extra space, invalid composite index for null fields);        Less text type (use varchar instead of the text field as much as possible);

MySQL data type	Meaning
Date	3 bytes, date, format: 2014-09-18
Time	3 bytes, time, format: 08:42:30
Datetime	8 bytes, date time, format: 2014-09-18 08:42:30
Timestamp	4 bytes, automatically stores the time the record was modified
Year	1 bytes, year

Integral type

MySQL data type	Meaning (Signed)
tinyint	1 bytes, range ( -128~127)
smallint	2 bytes, range ( -32768~32767)
Mediumint	3 bytes, range ( -8388608~8388607)
Int	4 bytes, range ( -2147483648~2147483647)
bigint	8 bytes, Range (18 +-9.22*10)

String data type

MySQL data type	Meaning
CHAR (n)	Fixed length, up to 255 characters
VARCHAR (n)	Variable length, up to 65,535 characters
Tinytext	Variable length, up to 255 characters
Text	Variable length, up to 65,535 characters
Mediumtext	Variable length, up to 2 of 24 square-1 characters
Longtext	Variable length, up to 2 of 32 square-1 characters

MySQL Database Specifications and principles