Analyze the selection problem of storing time and date types in MySQL

In general applications, we use timestamp, datetime, int types to store time formats:

int (corresponding to Integer or int in javaBean)

1. Occupies 4 bytes

2. After indexing, query speed is fast

3. Condition range search can use between

4. Cannot use the time functions provided by MySQL

Conclusion: Suitable for data tables that require a large number of time range queries

datetime (Date type is used in javaBean)

1. Occupies 8 bytes

2. Allows empty values, can customize values, and the system will not automatically modify its value.

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

4. It has nothing to deal with the TIMEZONE and Conversion.

5. You cannot set a default value, so if null values ​​are not allowed, you must manually specify the value of the datetime field to successfully insert data.

6. You can use the now() variable to automatically insert the current time of the system when specifying the value of the datetime field.

Conclusion: The datetime type is suitable for recording the original creation time of data, because no matter how you change the values ​​of other fields in the record, the value of the datetime field will not change unless you change it manually.

timestamp (Date or Timestamp type in javaBean)

1. Occupies 4 bytes

2. Empty values ​​are allowed, but custom values ​​are not allowed, so empty values ​​have no meaning.

3. TIMESTAMP values ​​cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while valid for a DATETIME or DATE value, is not valid for a TIMESTAMP value and will be converted to 0 if assigned to such an object.

4. The value is saved in UTC format (it stores the number of milliseconds)

5. Time zone conversion: convert the current time zone when storing, and convert back to the current time zone when retrieving.

6. The default value is CURRENT_TIMESTAMP(), which is actually the current system time.

7. The database will automatically modify its value, so you do not need to specify the name of the timestamp field and the value of the timestamp field when inserting records. You only need to add a timestamp field when designing the table. After insertion, the value of the field will automatically become the current system time.

8. Whenever you modify a record in the table at any time in the future, the timestamp value of the corresponding record will be automatically updated to the current system time.

Conclusion: The timestamp type is suitable for recording the last modification time of data, because as long as you change the value of other fields in the record, the value of the timestamp field will be automatically updated.

Summarize

The above is all about analyzing the selection problem of storing time and date types in MySQL. Interested friends can refer to: MySQL in statement subquery efficiency optimization skills example, MYSQL subquery and nested query optimization example analysis, MySQL optimization using connection (join) instead of subquery, etc. If you have any questions, you can leave a message at any time and the editor will reply to you in time. I hope this helps you all.