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

exportMySQL

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?

Best Answer

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.

Related Topic