7

Possible Duplicate:
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

Returns:

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...

Community
  • 1
  • 1
ParadoxWs
  • 213
  • 2
  • 7
  • PhpMyAdmin simply exports the database manually to SQL format. I believe there is nothing near to the command you are looking for. However, you may find good scripts to generate the insert statements from the table structure and data just as PhpMyAdmin. – Mateus Schneiders Oct 16 '12 at 01:38
  • I'm fairly sure there is no such command but it wouldn't take much to write it in php! – Erik Oct 16 '12 at 01:42
  • Do you require this to return the INSERT statements in the same order that they were originally done? Or is any valid collection of INSERT statements good enough? If the latter, `mysqldump` should do what you want. – Barmar Oct 16 '12 at 03:36

2 Answers2

2

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. :-)

Ken White
  • 123,280
  • 14
  • 225
  • 444
  • @Adrian: What about them? If you use them properly (transactions, backups, etc) they're useful (as **binary logs**). What part of "don't reinvent the wheel" was unclear? There's no need to `mysqlbinlog` anything if you're doing things properly in the first place. Should I downvote your answer because you're recommending using something that should be for emergency recovery inappropriately? – Ken White Oct 16 '12 at 02:31
  • My point was against `There is no such "command" ` when there is. I guess the OP is looking for an audit log - when there is not one - this would work – Adrian Cornish Oct 16 '12 at 02:33
  • My point is that the "command" you're recommending is designed for emergency recovery, not for daily transactional logging, and it doesn't do what the question asked (return the "insert statement used to create a certain record originally"). It's OK, though. It's your decision where you spend your rep downvoting. :-) No problem. – Ken White Oct 16 '12 at 02:36
  • How does it not show the insert applied to the DB - am I being stupid about something? – Adrian Cornish Oct 16 '12 at 02:37
  • No. You're missing part of the question. "Returns the **original insert statement**" is what the question asked. Being able to produce a **new** insert statement isn't the same thing - the question asked about the **original** insert statement. You know, the one run somewhere around a year ago by user "Jeff", before the row was modified by multiple updates by 6 other users. Yeah, that one. Produce that for me quickly, will you? Using any means you'd like. – Ken White Oct 16 '12 at 02:40
  • I am totally missing something here - the binlog records each insert statement as it happens right? So the statement can be found. Finding it I agree is not easy - but it is there (assuming you keep all the binlogs) – Adrian Cornish Oct 16 '12 at 02:42
  • Yeah, you are. :-) The data is not readily available (as the question seems to want), but technically I guess you're right. It's there, just not useful without a lot of work, and that's not wuat the question asks about doing. But it's OK. This is turning into a chat, and that's not what SO is designed to be used for. Like I said, no problem with the downvote. – Ken White Oct 16 '12 at 02:45
  • Then we agree +1 again. Hey I have to wade through 100,000,000 of stock quotes a day to find the `bad` one :-) easy no - possible yes. We are back on the same DB page ;-) – Adrian Cornish Oct 16 '12 at 02:53
  • except SO will not let me upvote – Adrian Cornish Oct 16 '12 at 02:54
  • @Adrian: :-) Don't worry about it. (Once the time expires, you can't reverse a downvote unless the answer is edited by someone.) – Ken White Oct 16 '12 at 03:00
  • SO changed their policy - unless you edit I cannot upvote - meaningless process compliant task upcoming? – Adrian Cornish Oct 16 '12 at 03:20
  • @Adrian: I added a pointer to these comments to my original post to make sure our discussion was seen when evaluating answers. :-) – Ken White Oct 16 '12 at 11:01
0

There is no such command to retrieve the original insert statement. However, you can always remake the insert statement based on the existent table structure and data.

The following link may help you, where this has already been asked:

Get Insert Statement for existing row in MySQL

Another possible option is using mysqldump with php to export the data as SQL statements.

Community
  • 1
  • 1
Mateus Schneiders
  • 4,853
  • 3
  • 20
  • 40
  • You can always convert binary data to a base64 string. The insert statement would contain the base64 string and the script to convert back to binary. – Mateus Schneiders Oct 16 '12 at 15:22