Differences and advantages of timestamp, datetime, and int types in mysql _ MySQL

Difference and advantages of timestamp, datetime, int type in mysql bitsCN.com

Differences and advantages of timestamp, datetime, and int types in mysql

Int

1. occupies 4 bytes

2. fast query after an index is created

3. you can use between for conditional range search.

4. the time functions provided by mysql cannot be used.

Conclusion: it is suitable for data tables that require a large number of time range queries.

Datetime

1. 8 bytes occupied

2. the value can be null and can be customized. The system will not automatically modify the value.

3. storage in actual format (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. the default value cannot be set. Therefore, if null values are not allowed, you must manually specify the value of the datetime field to insert data.

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

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

Timestamp

1. occupies 4 bytes

2. null values are allowed, but custom values are not allowed. Therefore, null values are meaningless.

3. the TIMESTAMP value cannot be earlier than 1970 or later than 2037. This indicates a DATE, for example, '2017-01-01 '. although it is valid for DATETIME or DATE values, it is invalid for TIMESTAMP values, if such an object is assigned, it is converted to 0.

4. Save the value in UTC format (it stores the number of milliseconds)

5. time zone conversion. during storage, the current time zone is converted, and then retrieved 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. Therefore, you do not need to specify the name of the timestamp field and the value of the timestamp field when inserting the record. you only need to add a timestamp field when designing the table, after the field is inserted, the value of this field automatically changes to the current system time.

8. when you modify the 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 the value of the timestamp field is automatically updated as long as you change the values of other fields in the record.

BitsCN.com