Jump to content


Photo

What's the difference between the export SQL dump type used in PhpMyAdmin?


  • Please log in to reply
9 replies to this topic

#1 joseph

joseph

    Advanced Member

  • Members
  • PipPipPip
  • 33 posts

Posted 16 May 2007 - 09:19 AM

In PhpMyAdmin we have these 3 export options to choose from: 1. Insert 2. Update and 3. Replace. Sound very easy to understand but how is it done exactly to the tables having an existing data?

For sure, I know the most common when we use INSERT Export type to an existing database, it will insert it all w/o errors that is if the tables being inserted to the new database does not have any identical table_names.

How about for the UPDATE and REPLACE? How will it react when we insert them to the new database if there are identical table_names?  ???



#2 hitman6003

hitman6003

    Advanced Member

  • Members
  • PipPipPip
  • 1,807 posts

Posted 16 May 2007 - 07:50 PM

Export a small table with each method and look at the differences....

http://dev.mysql.com.../mysqldump.html

#3 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,214 posts
  • LocationCheshire, UK

Posted 18 May 2007 - 05:45 AM

Basically

INSERT inserts new records
UPDATE updates existing records
REPLACE inserts if record is not there and updates if it is (based on primary key match)

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#4 davidannis

davidannis

    Freak Dr.

  • Members
  • PipPipPip
  • 592 posts
  • LocationOkemos, MI USA

Posted 12 February 2013 - 11:30 AM

Am I correct that there is no option that will delete a record if it no longer exists in the copy that is being imported?

#5 davidannis

davidannis

    Freak Dr.

  • Members
  • PipPipPip
  • 592 posts
  • LocationOkemos, MI USA

Posted 12 February 2013 - 02:25 PM

Let me explain what I am after a little better. I have a live database which I exported to my development machine. While it was there I added a few new tables, a few fields in existing tables, and deleted a bunch of really old records that nobody will ever need. I'd love to be able to export from the development machine, import to my test server, have the new tables added (easy), have the tables with new fields updated, and have the junk records deleted. I think that I need to actually write SQL myself to do the last two things though. Am I wrong?

#6 fenway

fenway

    MySQL Si-Fu / PHP Resident Alien

  • Moderators
  • 16,193 posts
  • LocationToronto, ON

Posted 12 February 2013 - 03:51 PM

There are tools that will do this -- mysqldump isn't one of them -- you're talking about diff-ing records and schemata.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 davidannis

davidannis

    Freak Dr.

  • Members
  • PipPipPip
  • 592 posts
  • LocationOkemos, MI USA

Posted 03 March 2013 - 07:56 PM

fenway, Can you suggest a good tool for me. It might save me a lot of time.



#8 teynon

teynon

    Advanced Member

  • Members
  • PipPipPip
  • 895 posts

Posted 03 March 2013 - 08:06 PM

Basically

INSERT inserts new records
UPDATE updates existing records
REPLACE inserts if record is not there and updates if it is (based on primary key match)

Just to clarify, REPLACE does not update the old record. REPLACE will delete the old record and insert a new one. Be careful using REPLACE INTO if you have other tables that are referencing it.


Support my Kickstarter Project!
http://www.kickstart...7618755/antroid

http://www.thomaseynon.com

Vulnerabilities: http://cwe.mitre.org...x.html#Guidance - MySQL.com hacked with SQL Injection - If it happened to them, it can happen to you.


#9 teynon

teynon

    Advanced Member

  • Members
  • PipPipPip
  • 895 posts

Posted 03 March 2013 - 08:11 PM

Am I correct that there is no option that will delete a record if it no longer exists in the copy that is being imported?

If you just want the imported records and only those imported records, PHPMyAdmin has the option of adding the "DROP TABLE" statement. This is essentially like restoring a backup. I would be very careful doing this on a production site though.


Support my Kickstarter Project!
http://www.kickstart...7618755/antroid

http://www.thomaseynon.com

Vulnerabilities: http://cwe.mitre.org...x.html#Guidance - MySQL.com hacked with SQL Injection - If it happened to them, it can happen to you.


#10 fenway

fenway

    MySQL Si-Fu / PHP Resident Alien

  • Moderators
  • 16,193 posts
  • LocationToronto, ON

Posted 09 March 2013 - 01:15 PM

The Percona Toolkit has something called pt-table-sync.


Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com