Get Insert Statement for existing row in MySQL
Lets 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
Lets assume there's a few rows filled up in the table. I know there is a query that returns the creation of the table -> SHOW CREATE TABLE users But I'm looking to recreate individual insert statements to save them in a log...I know this sounds weird, but I am making a custom CMS where everything is logged, and hopefully where updated/deleted rows could rollback at point in time...therefore I would need to recreate exact insertion query from table data (I have searched all over the web for an answer and can't find it...)
Is there a query that would return "automatically" the query that inserted that specific row by primary key?
I am looking for a query that would do this:
SHOW INSERT FROM users WHERE PRIMARY_KEY=2
INSERT INTO users (UID,fname,lname,username,password) VALUES (2,'somename','somelastname','someusername','someAESencryptedPassword')
The reason I'm thinking such a query would exist/be possible is because when you backup a database with php myadmin (cpanel) and you open the file, you can actually view each insert to recreate the table with all rows a that point in time...
There is no such "command" (the data is stored, but not the actual SQL that inserted it), and it wouldn't make sense to do what you're asking. You do realize your "log" would be about 20 times larger (at least) than the actual table and data itself? And it's not going to able to retrieve the INSERT statement without a lot of work to track it down (see the comments below) anyway.
Study transactional SQL, make use of server logging and transactions, and back up the data regularly like you're supposed to and quit trying to reinvent the wheel. :-)