How MySQL stores data in milliseconds

A considerable number of new MySQL users have encountered the same problem: storage and display in milliseconds. Because MySQL only provides DATETIME, TIMESTAMP, TIME, DATE, and year time types, and the minimum unit of DATETIME and TIMESTAMP is second, there is no function for storing milliseconds. However, MySQL can recognize the millisecond part of the time. In addition, we have multiple ways to obtain the millisecond part, such as the function: microsecond.

Here is a simple example to store the parts before and after seconds.
For applications that use the time field as the primary key, we can create the following table for conversion:

mysql> create table mysql_microsecond ( log_time_prefix timestamp not null default 0, log_time_suffix mediumint not null default 0) engine innnodb;Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> alter table mysql_microsecond add primary key (log_time_prefix, log_time_suffix);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0

mysql> set @a = convert(concat(now(),'.222009'),datetime);Query OK, 0 rows affected (0.00 sec)

mysql> insert into mysql_microsecond select date_format(@a,'%Y-%m-%d %H-%i-%s'),date_format(@a,'%f');Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from mysql_microsecond;+---------------------+-----------------+| log_time_prefix   | log_time_suffix |+---------------------+-----------------+| 2009-08-11 17:47:02 |     222009 |+---------------------+-----------------+1 row in set (0.00 sec)

You can use VARCHAR to store all the time fields or store a HASH to ensure performance!
There are many methods, depending on how your application is used properly.