MYSQL Data export/import some tips

Recently contacted MySQL more, take this to record some knowledge points.

Tips:

1. When the data is exported to a. sql file, the single quote ' is escaped, and the final INSERT statement is \ '

2. When inserting non-repeating data (insert if not exist), you can use Insert ignore or replace

Insert IGNORE: Insertion repetition fails, but is ignored, continuing with the next one.

REPLACE: Insert repeat will be overwritten directly, no then new.

The above two will cause the self-increment ID discontinuity problem, insert failure will increment, replace is first delete after insert. So the self-increment ID will be discontinuous

ID Discontinuous reference: http://blog.csdn.net/liyong199012/article/details/21516817

Reference Https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql

18th October 2007

To Start:as of the latest MySQL, syntax presented in the title was not possible. But there was several very easy ways to accomplish what's expected using existing functionality.

There is 3 possible solutions:using insert IGNORE, REPLACE, or insert ... On DUPLICATE KEY UPDATE.

Imagine We have a table:

CREATE TABLE `transcripts` (`ensembl_transcript_id` varchar(20) NOT NULL,`transcript_chrom_start` int(10) unsigned NOT NULL,`transcript_chrom_end` int(10) unsigned NOT NULL,PRIMARY KEY (`ensembl_transcript_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now imagine the We have a automatic pipeline importing transcripts meta-data from Ensembl, and so due to various Reaso NS The pipeline might is broken at any step of execution. Thus, we need to ensure, things:1) repeated executions of the pipeline won't destroy our database, and 2) repeated Executions won't die due to ' duplicate primary key ' errors.

Method 1:using REPLACE

It ' s very simple:

REPLACE INTO `transcripts`SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,`transcript_chrom_start` = 12345,`transcript_chrom_end` = 12678;

If The record exists, it'll be overwritten; If it does not yet exist, it'll be created. However, using the This method isn ' t efficient for our case:we does not need to overwrite existing records, it's fine just to S Kip them.

Method 2:using INSERT IGNORE Also very simple:

INSERT IGNORE INTO `transcripts`SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,`transcript_chrom_start` = 12345,`transcript_chrom_end` = 12678;

Here, if the ' ensembl_transcript_id ' was already present in the database, it would be silently skipped (ignored). (To is more precise, here's a quote from MySQL reference manual: "If You use the IGNORE keyword, errors this occur while E Xecuting The INSERT statement is treated as warnings instead. For example, without IGNORE, a row this duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a DUP Licate-key error and the statement is aborted.) If the record doesn ' t yet exist, it'll be created.

This second method has several potential weaknesses, including non-abortion of the "query in case" any other problem occurs (See the manual). Thus It should be used if previously tested without the IGNORE keyword.

There is one more option:to use INSERT ... On DUPLICATE KEY UPDATE syntax, and in the update part just does nothing does some meaningless (empty) operation, like Calcula Ting 0+0 (Geoffray suggests doing the id=id assignment for the MySQL optimization engine to ignore this operation). Advantage of this method is the it only ignores duplicate key events, and still aborts on other errors.

As a final notice:this post is inspired by XAPRB. I ' d also advise to consult he other post on writing flexible SQL queries.

MYSQL Data export/import some tips