Search

Changes in Old datetime datatype in MySQL 8.0


What are Temporal datatypes

Temporal Datatypes are basically the Date Time data types of MySQL like TIME, DATETIME and TIMESTAMP. They are used for different purposes in MySQL database tables for audit record of the rows inserted into the tables and storing the Date values along with the records.


What is the different between Old and New

Temporal types are updated in newer version of MySQL 5.6 and onward. There are fractional second precision added in seconds of temporal types with up to microseconds along with fractional second precision. Storage of old temporal types are not space efficient. Old temporal types of MySQL Version 5.5 are shown below.



Old temporal types are created in MySQL 5.5 and older versions. Any new values created with temporal types after MySQL 5.6.4 will automatically get the new version of it and fractional second precision. As MySQL is now version 8 is going on so support for old version is not there. Now updating from older version of MySQL to latest version is not possible with old temporal types.


How to Upgrade

Now question is how to upgrade the old types to newer one when switching data from older version to the newer version. ALTER, REPAIR TABLE command. For some cases mysql_upgrade will do the job and upgrade the data to version 5.7. There is one more way to avoid the temporal types by using the -avoid-temporal-upgrade in the configuration file.


Alter Command


Tools and Techniques

But this ALTER command is suitable for small tables where table size is already small for big tables it will take much more time as compaction of table is a long time taking process. To avoid blocking of table for a long period of time. Tools like gh-ost can be used for this job.

Or we can create one more similar table and copy the data in that table.


Summary

This change is not necessary for people who are upgrading their system by restoring the data done by mysqldump. By migrating the data from old temporal types to new one we can save space and get more accuracy.



11 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa