MySQL dump, output each table row on a new line whilst using --extended-insert

Question

I'm having an issue, where for ease of use, I'd like to be able to format a command line MySQL dump so that each row of a given table is on a new line when using the --extended-insert option.

Usually when using --extended-insert, every row of a given table is outputted on one line, and as far as I am aware there's no way to change this, other than post-processing the dump with perl or such like.

The format I'm looking for is:

-- 
-- Dumping data for table `ww_tbCountry`
-- 

INSERT INTO `ww_tbCountry` (`iCountryId_PK`, `vCountryName`, `vShortName`, `iSortFlag`, `fTax`, `vCountryCode`, `vSageTaxCode`) VALUES (22, 'Albania', 'AL', 1, 0.00, '8', 'T9'),
(33, 'Austria', 'AT', 1, 15.00, '40', 'T9'),
(40, 'Belarus', 'BY', 1, 0.00, '112', 'T9'),
(41, 'Belgium', 'BE', 1, 15.00, '56', 'T9'),
(51, 'Bulgaria', 'BG', 1, 15.00, '100', 'T9'

However, when I dump a database using Phpmyadmin, using --extended-insert, each row is dumped on a new line (as shown by the example above). I've gone through Phpmyadmin and can't find any documentation that would explain this.

Is anyone able to shed any light on this?

Solution 1

From the googling I've done, it appears that this isn't possible through mysqldump itself. You have to pipe the output to something else, like sed:

$ mysqldump --extended-insert ... database | sed 's$),($),\n($g'

That particular sed command will look for the string "),(" and put a newline after the comma.

Solution 2

Using a regex to split lines is not enough, you need a parser that will properly understand quotes and escaping characters.

I just wrote a parser since I couldn’t find one: http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html

Comments

[Christian]

I'd argue that this is never a good idea (unless it's a one-time thing). It can easily corrupt data that happened to contain those 3 characters.