Creation time of Innodb tables

CREATE_TIME column of "TABLES" table from INFORMATION_SCHEMA shows the same CREATE_TIME for all my InnoDB tables. It means all these tables were created between 2010-03-26 06:52:00 开发者_如何学Cand 2010-03-26 06:53:00 while actually they were created a few months ago.

Does the CREATE_TABLE field change automatically for InnoDB tables?

For InnoDB, the CREATE_TIME value in INFORMATION_SCHEMA.TABLES is based on the modified time of the table's FRM file. So this will most likely represent the last time you ran ALTER TABLE or OPTIMIZE TABLE.

The create_time and update_time fields of information_schema correspond to the creation/modification timestamps of the underlying storage for the table.

With MyISAM, each table has its own file, so the create/modify timestamp of that file is returned.

For InnoDB however, storage for all tables is inside a single file, ibdata, so there is only one create/modify timestamp. This one timestamp is returned for all InnoDB tables.

I used MySQL 5.7.17.

If you have MyISAM table it will show you correct created table date even if you have Optimized MyISAM table. (Show table status where name = ‘tablename’;)

if you have innodb tables it will show you wrong created date because once you optimized innodb table internally it will use alter table and hence it will show you created table date and time which will be post Optimization date.(Optimize table tablename;)

For this resion we need to use information_schema.tables to fetch table creation date.