Physical design of MySQL database design

First, the storage engine

It is recommended to use InnoDB, which is also the default storage engine used by MySQL, supporting transactions

Second, the choice of attributes

Character selection:

1, char, fixed long, fast, there is the possibility of space waste, will handle the trailing space, the upper limit of 255 bytes. (Utf-8, a kanji 3 bytes English alphabet 1 bytes)

2, varchar, long, slow, there is no space waste, not processing trailing spaces, the upper limit of 65535 bytes, but there is a storage length of the actual 65532 bytes maximum available.

3, text, storage and growth data, slow, there is no space waste, do not handle the trailing space, the upper limit of 65535 bytes, will use extra space to hold the data length, it can be used all 65535 bytes, up to 20000 characters can be saved.

Time Selection:

int
1. Occupy 4 bytes
2. After indexing, query speed is fast
3. Conditional range Search can be used using between
4. You cannot use the time function provided by MySQL
conclusion: Suitable for data tables that require large time-range queries

datetime
1. Occupy 8 bytes 2. Allow null values, you can customize the value, the system does not automatically modify its value .

3. Actual format storage (Just stores what are you having stored and retrieves the same thing which you have stored.)

4. Time zone Independent (It has nothing to deal with the TIMEZONE and Conversion.)

5. You can use the now () variable to automatically insert the current time of the system when specifying the value of a DateTime field. Conclusion: DateTime types are appropriate for recording the original creation time of the data, because no matter how you change the value of the other fields in the record, the value of the DateTime field does not change unless you manually change it.  timestamp
1. Occupy 4 bytes2. Allow null values, but not custom values, so null is meaningless.
3. The timestamp value cannot be earlier than 1970 or later than 2037. This indicates that a date, such as ' 1968-01-01 ', is valid for a datetime or date value but is not valid for the timestamp value, and is converted to 0 if assigned to such an object.

4. Values are saved in UTC (It stores the number of milliseconds)

5. Time zone conversion, the current time zone is converted when stored, and then converted back to the current time zone.

6. The default value is Current_timestamp (), which is actually the current system time. 7. The database automatically modifies its value, so you do not need to specify the name of the timestamp field and the value of the timestamp field when inserting a record, you only need to add a timestamp field when you design the table, and the value of the field will automatically become the current system time when you insert it. 8. By default, when a record in a table is modified at any later time, the timestamp value of the corresponding record is automatically updated to the current system time. 9. If necessary, you can set the timestamp not to update automatically. This can be done by setting the default Current_timestamp. To Modify Automatic Updates:
' field_time ' timestamp not NULL DEFAULT current_timestamp on UPDATE
Modify does not update automatically
' field_time ' timestamp not NULL DEFAULT current_timestamp Conclusion: The timestamp type is suitable for recording the last modification time of the data, because the value of the timestamp field is automatically updated as soon as you change the value of the other fields in the record. (if necessary, you can set timestamp not update automatically)

Third, anti-normalization

For a simpler query:

After optimization:

Physical design of MySQL database design