0

If you run a SQL statement in MySQL like this:

SHOW TABLE STATUS LIKE 'TableName'

You will get a create_time column that contains the date/time the table was created.

Is there a way to modify or 'touch' that date/time via a SQL query?

This table is storing non-relational cached data,and sometimes I want to reuse the data even if it is old.

humbads
  • 3,252
  • 1
  • 27
  • 22
  • 2
    How is the table creation date stopping you from using the data? There is also a Update_time field, but that doesn't work for all engines. – Dijkgraaf Mar 30 '15 at 23:36
  • If you run an SQL statement in MySQL like this: `SELECT your_very_old_data FROM your_really_old_table WHERE your_condition` you will get the data. Notice how the creation date had no say in you retrieving the data. – N.B. Mar 31 '15 at 14:18
  • The caching logic is not in SQL, it is in external code. I don't want to have to change that logic each time I want to use the old data. – humbads Mar 31 '15 at 15:02

2 Answers2

0

It would be somewhat counter-intuitive if you could change the creation time of a table, no? Like @Dijkgraaf suggests, you might want to reconsider your requirements - this question sounds very much like an XY problem. You shouldn't need to update the creation time of a table in the first place. What about just storing whatever timestamp you need in a column alongside the cached data?

That said, you could simply create a new table and copy the data over (of course, this is O(n) on time and space) if you really need to have a newer creation time.

Community
  • 1
  • 1
dimo414
  • 47,227
  • 18
  • 148
  • 244
  • Unix has a 'touch' command to update the creation time of a file. Nothing counter-intuitive there because it is a common use-case to tell the system the object is new but unchanged. It would make MySQL more useful if it had this feature. – humbads Apr 06 '17 at 17:23
  • Fair point, but I don't think it really makes sense to conflate files and database tables. They're different in many fundamental ways. – dimo414 Apr 06 '17 at 17:28
0

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

From Usage Notes for the INFORMATION_SCHEMA Database

It looks like it is not possible to alter the meta-data of the table. I was hoping to avoid storing the 'create time' in a user table to save development time.

My caching logic refreshes the table data if it is older than 24 hours. Refreshing can take hours, and I don't want to have to change the caching logic every time I want to re-use old data. Linux has a 'touch' command to update file timestamps, so I was wondering if MySQL had something similar.

Below is my SQL script to recreate the table, which does update the create_time, but is slow:

-- MySQL SQL Script to recreate a table 
-- for the purpose of updating the create_time

-- Create copy of the table with its indexes
CREATE TABLE tempForTouch LIKE TableToTouch;

-- Disable the indexes
ALTER TABLE tempForTouch DISABLE KEYS;

-- For performance, lock the tables
LOCK TABLES TableToTouch WRITE, tempForTouch WRITE;

-- Copy the data to the new table
INSERT INTO tempForTouch SELECT * FROM TableToTouch;

-- Unlock the tables
UNLOCK TABLES;

-- Re-enable the indexes
ALTER TABLE tempForTouch ENABLE KEYS;

-- Drop the original table
DROP TABLE TableToTouch;

-- Rename the temporary table to the new table
RENAME TABLE tempForTouch TO TableToTouch;
humbads
  • 3,252
  • 1
  • 27
  • 22