Omit auto-increment values using mysqldump
Endel Dreyer
I've just found a tip for omiting auto-increment column values using
mysqldump:
mysqldump -u root -pYourPasword database table_name --compact --no-
create-info | sed -r "s/\([0-9]*/\(NULL/g"
More details on my blog:
http://bugfixer.endel.me/2011/06/25/tip---omit-auto-increment-values-using-mysqldump/
Hope it be useful for someone else.
Luuk
This only works if your auto-increment column is the first column in
your table
--
Luuk
Jerry Stuckle
> Hey there,
>
> I've just found a tip for omiting auto-increment column values using
> mysqldump:
> mysqldump -u root -pYourPasword database table_name --compact --no-
> create-info | sed -r "s/\([0-9]*/\(NULL/g"
>
> More details on my blog:
>
> Hope it be useful for someone else.
And a very dangerous thing to do - especially if you have foreign keys
depending on the auto-increment column's value, which is quite common in
relational databases. If you ever have to restore the database, you'll
end up with rows in one table mismatched with rows in another table.
But then we all know your real reason for posting this "hint".
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
onedbguru
> Hey there,
>
> I've just found a tip for omiting auto-increment column values using
> mysqldump:
> mysqldump -u root -pYourPasword database table_name --compact --no-
> create-info | sed -r "s/\([0-9]*/\(NULL/g"
>
>
> Hope it be useful for someone else.
Title should be: "How to hose your database".
As has been stated, this is very dangerous thing to do or even
suggest. Most script-kiddies that read these posts do not know how
dangerous it could be. One would be very foolish to follow this advice.
J.O. Aho
> Hey there,
>
> I've just found a tip for omiting auto-increment column values using
> mysqldump:
> mysqldump -u root -pYourPasword database table_name --compact --no-
> create-info | sed -r "s/\([0-9]*/\(NULL/g"
>
> Hope it be useful for someone else.
This will not be helpful for anyone at all, the regexp is flawed and even
if the obvious flaw was fixed, you would replace all ( and the following
numbers if any with (NULL.
As others mentioned you may ruin dependencies between tables with this if
it had worked as you thought, and renumbering primary keys where they may
be foreign key dependencies can lead to that you will not have all the data
in the database.
Before posting, please check what you post really does work, there will be
people who may think it works and ruins their database... but maybe that
was your intention.
--
//Aho