Analyzing the selection of storage time and date type in MySQL

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

int (corresponding to Integer or int in javaBean)

1. 4 bytes

2. Query speed is fast after building index

3. Conditional range searches can be performed using between

4. You cannot use the time function provided by mysql

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

datetime (Date type used in javaBean)

1. 8 bytes

2. Allow null values, customize values, and the system will not automatically modify their values.

3. Actual format storage (Just stores what you have 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 cannot set a default value, so you must manually specify the value of the datetime field to successfully insert data if null values are not allowed.

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: 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 datetime field will not change unless you change it manually.

timestamp (Date or Timestamp type used in javaBean)

1. 4 bytes

2. Null values are allowed, but you cannot customize values, so null values make no sense.

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

4. Values are saved in UTC format (it stores the number of E113EN)

5. Time zone conversion, which converts the current time zone when storing and then converts 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 don't need to specify the name of timestamp field and the value of timestamp field when inserting records. You only need to add 1 timestamp field when designing the table, and the value of this field will automatically change to the current system time after insertion.

8. When a record in the table is modified at any later time, the timestamp value of the corresponding record is automatically updated to the current system time.

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

Summarize

That's all about parsing the selection of storage time and date types in MySQL. Interested friends can refer to: mysql in statement subquery slow optimization skills example, MYSQL subquery and nested query optimization example analysis, MySQL optimization using connection (join) instead of subquery, etc., any questions can be left at any time, this site will reply to everyone in time. I hope it will be helpful to everyone.