CODEWITHSUNDEEP
mysql
rexford
rexford

Reputation: 5582

MySQLDump one INSERT statement for each data row

with the following statement:

mysqldump --complete-insert --lock-all-tables --no-create-db 
--no-create-info --extended-insert --password=XXX -u XXX 
--dump-date yyy > yyy_dataOnly.sql

I get INSERT statements like the following:

INSERT INTO `table` VALUES (1,'something'),(2,'anything'),(3,'everything');

What I need in my case is something like this:

INSERT INTO `table` VALUES (1,'something');
INSERT INTO `table` VALUES (2,'anything');
INSERT INTO `table` VALUES (3,'everything');

Is there a way to tell "mysqldump" to create a new INSERT statement for each row? Thanks for your help!

Upvotes: 214

Views: 162480

Answers (4)

yurenchen
yurenchen

Reputation: 2499

for people who don't want to --skip-extended-insert
// cause (each record has one insert sentence) performance loss when later execute/import sql.

but want keep the sql readability (avoid records in one long line) this may be work
// didn't find offical solution, just use sed to format the sql

NOTE: It's not safe if ),( in raw data.
TODO: handle ),( in row data

use sed


mysqldump ... | sed 's/),(/),\n  (/g'

// no newline before first item.


mysqldump ... | sed -e 's/),(/),\n  (/g' -e 's/VALUES (/VALUES\n  (/g'

// add newline before first item.

outputs

0. original:

INSERT INTO `role_permission` VALUES (1328,106,1),(1329,171,1),(1330,144,1),(1331,147,1),(1333,157,1),(1334,88,1);

1. become:

INSERT INTO `role_permission` VALUES (1328,106,1),
  (1329,171,1),
  (1330,144,1),
  (1331,147,1),
  (1333,157,1),
  (1334,88,1);

INSERT INTO `role_permission` VALUES
  (1328,106,1),
  (1329,171,1),
  (1330,144,1),
  (1331,147,1),
  (1333,157,1),
  (1334,88,1);


http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html

https://stackoverflow.com/q/15750535#answer-19961480
Solution came from here, I didn't do fully test

Upvotes: 0

israel berko
israel berko

Reputation: 637

In newer versions change was made to the flags:

from the documentation:

--extended-insert, -e

Write INSERT statements using multiple-row syntax that includes several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.

--opt

This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt.

--skip-extended-insert

Turn off extended-insert

Upvotes: 24

DevWL
DevWL

Reputation: 18870

For MariaDB you would use

mysqldump -u root -p'secret' --skip-extended-insert dbnamehere > y.sql

MaraiDB mysqldump Docs

Upvotes: 4

driis
driis

Reputation: 164341

Use:

mysqldump --extended-insert=FALSE

Be aware that multiple inserts will be slower than one big insert.

Upvotes: 342

Related Questions