How to get the original insert statement from the database

Question

Possible duplicate:
Get instructions for an existing row in MySQL

Let's say we have a table called users:

CREATE TABLE IF NOT EXISTS users( UID int(11) PRIMARY KEY NOT NULL auto_increment, fname varchar(100) default NULL, lname varchar(100) default NULL, username varchar(20) default NULL UNIQUE, password blob )ENGINE=InnoDB DEFAULT CHARSET=utf8 

Suppose a few rows were filled in a table. I know there is a query that returns table creation -> SHOW CREATE TABLE users But I'm looking to recreate individual insertion instructions to save them in a log ... I know this sounds weird, but I create a custom CMS where everything is logged , and, I hope, when updated / deleted rows can be rolled back at a time ... so I will need to recreate the exact insertion request from the table data (I searched the answer all over the Internet and cannot find it ...)

Is there a query that will return “automatically” a query that entered this particular string using the primary key?

I am looking for a query that would do this:

 SHOW INSERT FROM users WHERE PRIMARY_KEY=2 

Return:

 INSERT INTO users (UID,fname,lname,username,password) VALUES (2,'somename','somelastname','someusername','someAESencryptedPassword') 

The reason why I think such a query would exist / would be possible is that when backing up the database with php myadmin (cpanel) and you open the file, you can actually view each insert to recreate the table with all lines a this point in time ...

Answer 1

There is no such “command” (the data is stored, but not the actual SQL that inserted it), and it does not make sense to do what you ask. Do you realize that your “log” will be about 20 times larger (at least) than the actual table and data? And he won’t be able to get the INSERT instruction without a lot of work in tracking it (see comments below).

Learn transactional SQL, use logging and server transactions, and back up your data regularly, just like you, and stop trying to reinvent the wheel. :-)

Answer 2

There is no such command to retrieve the original insert statement. However, you can always redo the insert statement depending on the existing structure and table data.

The following link may help you if this has already been set:

Get instructions for an existing row in MySQL

Another possible option: mysqldump with php to export data as SQL statements.