Baron Schwartz's Website
Archives Talks

Making Changes to Many Tables at Once

Published Oct 29, 2009 by Baron Schwartz in Databases, Open Source at https://www.xaprb.com/blog/2009/10/29/making-changes-to-many-tables-at-once/

As an alternative to another recent blog post that answered the question “how can I truncate all the tables in my database,” I thought I’d show another way to do it, which does not use the INFORMATION_SCHEMA.

$ wget http://www.maatkit.org/get/mk-find
$ perl mk-find --exec 'TRUNCATE TABLE %D.%N'

The other example is how to alter MyISAM tables to be InnoDB. That one’s easy, too. Let’s alter all MyISAM tables in the ‘test’ database:

$ wget http://www.maatkit.org/get/mk-find
$ perl mk-find test --engine MyISAM --exec 'ALTER TABLE %D.%N ENGINE=InnoDB'

If you want to print out the commands instead of executing them, you can just use --printf instead of --exec.

Why would you do it this way instead of through the INFORMATION_SCHEMA database? I don’t think this can be said too often: querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous. So whenever I mention it, I mention the dangers of using it. I use it too sometimes, but only when I know the server I’m working on.

Baron Schwartz

I’m the founder and CTO of VividCortex, author of several books, and creator of various open-source software. I write about topics such as technology, entrepreneurship, and fitness. More about me.

Story logo

© 2019 Baron Schwartz