Reputation: 5582
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
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
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.
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);
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
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