Retrieving timestamp of MySQL row creation from me

问题:

I have been maintaining a MySQL table of rows of data inserted from PHP code for about a year and a half. Really stupid, I know, I didn't include the timestamp for insertion of these rows.

Is there any possible way that I can retrieve the timestamp of creation of these rows through some metadata or some other way? (Of MySQL or PHPMyAdmin or some other possible ways?)

回答1:

Unfortunately, there's nothing you can do in this case. If MySQL had a secret timestamp field, the general size of tables would increase by 4 bytes per row.

回答2:

The only way you can get that timestamp, if it is saved somewhere on one of your servers. You have a web server, which you may keep archive of logs for. Or some other place where there is a timestamp of activity of PHP script making requests to the database.

Say you have web server logs and there is an entry for each or most of PHP script activity, then, potentially, you can parse that log, get the timestamp and map it to the rows in your database. As you can see it is quite labourious, but not utterly impossible.

As for MySQL (or any other database) normally they do not keep a big archive of past information. Main reason for that - it is updo developer or designer of the application to decide what information should be kept or not. Database keeps only data needed for all its parts to run healthy.

Just had an idea, that is you have transaction log archive (which I really doubt), then you can re-run them on a back up of a database and may be they (transaction logs) contain timestamp of a row being added or changed.

回答3:

Here is a way to do so : https://www.marcus-povey.co.uk/2013/03/11/automatic-create-and-modified-timestamps-in-mysql/

I hope this can help