Difference and advantages of timestamp, datetime, int type in mysql, timestampdatetime
-
The difference between timestamp, datetime, and int types in mysql and int1. 4 bytes are occupied. after an index is created, the query speed is high. 3. conditional range search can use between4. the time function provided by mysql cannot be used. CONCLUSION: It is suitable for data tables that require a large number of time range queries. datetime1. 8 bytes are occupied. the value can be a null value and can be customized. The system does not automatically modify the value. 3. actual storage 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, you must manually specify the value of the datetime field to insert data successfully if the value is not allowed to be null. 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. Timestamp1. occupies 4 bytes. 2. null values are allowed, but cannot be customized. 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. the value is saved 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.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.
