MySQL Server Forum - mysqldump, entire table in one insert, but multiple lines, how?

Author

mysqldump, entire table in one insert, but multiple lines, how?

HomerCritic

2005-08-05, 3:23 am

I'm aware of the recent mysqldump change, in that it now by default
enables some optimizations.
One of those optimizations is to use single insert statements, instead
of separate insert statements, each on their own lines.
I'm also aware of the --skip-opt / -e / --skip-extended-insert

But what I'd like is a combination of having a single insert statement,
but with each record in its own line, like so:

INSERT INTO `article` (`id`, `title`, `text`) VALUES
(1, 'test', 'this is a test'),
(2, 'test2', 'also a test');

--skip-opt or --skip-extended-insert isn't it, because that'll give me:

INSERT INTO `article` (`id`, `title`, `text`) VALUES (1, 'test', 'this
is a test');
INSERT INTO `article` (`id`, `title`, `text`) VALUES (2, 'test2', 'also
a test');

If this can't be done with mysqldump, does anyone know of an
alternative command-line runnable utility? Thanks in advance!

Bill Karwin

2005-08-05, 3:23 am

HomerCritic wrote:
> But what I'd like is a combination of having a single insert statement,
> but with each record in its own line, like so:
>
> INSERT INTO `article` (`id`, `title`, `text`) VALUES
> (1, 'test', 'this is a test'),
> (2, 'test2', 'also a test');

I also found this was useful, so I piped the output of mysqldump to a
perl command to insert the line breaks. E.g.:

mysqldump <options> | perl -pe 's/\),\(/),\n(/og'

Regards,
Bill K.

HomerCritic

2005-08-06, 3:23 am

Bill Karwin wrote:
> HomerCritic wrote:
>
> I also found this was useful, so I piped the output of mysqldump to a
> perl command to insert the line breaks. E.g.:
>
> mysqldump <options> | perl -pe 's/\),\(/),\n(/og'
>

Thanks for the feedback. I was hoping that mysqldump could do this
built in, but piping to a script is ok I guess. Your script works,
except if one of the strings contains a ),( it would break it.
Is is possible to enhance the script to detect if it's inside a string?

-Mike-

Sponsored Links