Makin changes to many tables at once

This question came up in Freenode's #mysql today

help | how can i truncate all the tables in my database in one go ?

I have had this question a few times from #mysql and from customers. Truth is, there is no simple or official way to do this in MySQL - some third party utilities let you do this - such as phpMyAmin - but one trick I commonly employ is making use of some SQL queries and the INFORMATION_SCHEMA database to generate the commands I need for me.

Within the INFORMATION_SCHEMA database, there is a table called, oddly enoguh, 'TABLES'.. In 'TABLES' we find columns representing the database and table names - as well as some other interesting information such as the engine type. With the use of the mysql command line tool and 'CONCAT' we can exploit this to generate queries for us.

You can read more about INFORMATION_SCHEMA here:
http://dev.mysql.com/doc/refman/5.1/en/information-schema.html

For example, to solve solofight's problem, we can run the following query:

mysql> SELECT CONCAT("TRUNCATE TABLE `", TABLE_SCHEMA, "`.`", TABLE_NAME, "`;") AS cmd FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "test";

This gives us some output like this (note in this example I only have one table, the benefit here is it generates commands for all tables, even if there are hundreds of them

+-------------------------------+
| cmd |
+-------------------------------+
| TRUNCATE TABLE `test`.`test`; |
+-------------------------------+
1 row in set (0.00 sec)

However, in this form we need to do some editing.. in the past I have used 'vim' to do this, some simple column-based deletes (deleting the first few characters of every line) and some simple regexes solved it - but we can expand on this with the mysql command line utility to output it in a better form.

If we pass two options, "batch" and "skip-column-names" it removes the table formatting (-, |, etc) and removes the header line with column names

shell> mysql -u root --skip-column-names --skip-line-numbers -e 'SELECT CONCAT("TRUNCATE TABLE `", TABLE_SCHEMA, "`.`", TABLE_NAME, "`;") AS cmd FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "test"'

Then we get the following output, simple and plain. This can be pasted straight into mysql, or piped in (careful doing that, though! always good to check the output first) or saved to a file and sourced later.

TRUNCATE TABLE `test`.`test`;

You can imagine this has many uses - another case I have used personally is some stray tables had been created as MyISAM - and I wanted them all InnoDB. So I made a query to make that changes for me.

In this case, we need to select on ENGINE=MyISAM.. but we also want to excude two databases - mysql since MySQL will get very upset if you change it's own tables to InnoDB, and information_schema since it is simply not possible to change their type - they are virtual tables.

mellie:~ lathiat$ mysql -u root -B --skip-column-name -e 'SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA, "`.`", TABLE_NAME, "` ENGINE=InnoDB;") AS cmd FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != "mysql" AND TABLE_SCHEMA != "information_schema" AND ENGINE = "MyISAM"'
ALTER TABLE `blah`.`host` ENGINE=InnoDB;
ALTER TABLE `test`.`test` ENGINE=InnoDB;

So, next time you need to perform operations on a bunch of tables at once - consider this option!

Comments:

Hi!

With little effort, you can make life easier by doing

SET @@group_concat_max_len := @@max_allowed_packet;

SELECT GROUP_CONCAT(
'\\nTRUNCATE TABLE '
, '`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;\\n'
SEPARATOR ''
) AS cmd
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = schema()
;

(improvements: output can be cut/pasted directly because of the extra line numbers, using the SCHEMA() function to get tha tables of only the current database.)

Posted by Roland Bouman on October 29, 2009 at 02:16 PM WST #

To be friendly to the Windows crowd (and people running with ansi_quotes in their sql_mode), swap your quotes around:

mysql -e 'SELECT withSingleQuotes' fails on Windows
mysql -e "SELECT withDoubleQuotes" works everywhere

Posted by Scott Noyes on October 29, 2009 at 03:31 PM WST #

Hi,

Check out mk-parallel-dump, part of Maatkit. While it mainly works out dumps (hence its name), it can actually act as a platform for parallelizing any MySQL job, and in particular -- the truncation of tables.

Regards

Posted by Shlomi Noach on October 29, 2009 at 06:51 PM WST #

Hmmm.
On second read I seem to have misunderstood the purpose of the post. My previous comment is irrelevant.

Posted by Shlomi Noach on October 29, 2009 at 11:25 PM WST #

Baron pointed out in his own blog post a way to do it with a different maatkit tool.

He gave a good reason why, and I'm sure he just forgot to post here, but the reason he recommends \*not\* doing it with information_schema is because on a busy server, using INFORMATION_SCHEMA can lock up the server, and in some cases (usually bugs, sometimes memory-related). Which is true...but it seems odd that someone would want to truncate (or somehow change) \*all\* their tables on a very busy server.

Another way to do it: find the .frm files in all the database directories (except mysql of course), cut off the .frm extension, and call mysql -e from commandline...all with a simple shell script.

If you are using INFORMATION_SCHEMA, and you can for 95% of the environments out there that aren't "super OMG busy!", consider eliminating the ; from your CONCAT. Let it show up with the | borders as usual, change the delimiter to | and copy/paste. :)

Posted by Sheeri K. Cabral on October 30, 2009 at 12:39 AM WST #

I really believe that these social networks will have a huge impact on what we can accomplish as groups, it'll help us be very organized and communicate.

Posted by christian louboutin on November 13, 2009 at 07:02 AM WST #

I really believe that these social networks will have a huge impact on what we can accomplish as groups, it'll help us be very organized and communicate.

Posted by ed hardy clothing on November 17, 2009 at 12:58 AM WST #

While it mainly works out dumps (hence its name), it can actually act as a platform for parallelizing any MySQL jo

Posted by infant on November 24, 2009 at 03:12 AM WST #

Thanks for your useful info, I think it's a good topic.

Posted by air jordan shoes on December 04, 2009 at 02:24 AM WST #

Thank you very much!
Your article is very useful!
Keep up the good work, Your article is really great and I truly enjoyed reading it. Waiting for some more great articles like this from you in the coming days.

Posted by fake watches on December 20, 2009 at 05:36 AM WST #

We are Rolex watch experts selling only the finest, top level condition, and preowned men's and ladies' Rolex watches, used Rolex watches, unused Rolex watches and new Rolex watches on the market. Our top priority is to provide every customer with the highest quality Rolex watch at the lowest price available. Browse our wide selection of Rolexes for sale.

Posted by replica watches on January 04, 2010 at 06:06 AM WST #

Above these goods really good, so beautiful jewelry!replica watches Really to be commended! Unfortunately, I have no money, He He. Otherwise I really bought"Magnificent", "Gum gee yuk yip", "True gold fears no fiery," "golden palace in books," "very strong"replica watchThere is a noble, time-delayed in ancient times left to shine; There is a wealth, status and power load across time and space.fake rolex watches You have a good typing speed soon yo!

Posted by fake rolex watches on January 08, 2010 at 06:34 AM WST #

Thank you.Hot Shoes Cheap Jordan shoes michael jordan shoes and are on sale-Free shipping.

Posted by michael jordan shoes on January 09, 2010 at 03:45 AM WST #

perfect posting.
Really nice writing.
As always ur posts are very helpful.

<a hers=http://www.webroyalty.com>webroyalty</a>

Posted by Nick Matyas on January 15, 2010 at 12:26 AM WST #

If you want to immediately execute the statements rather than getting back a list of commands, you can pipe it to another mysql. Also, naming the returned column `#sql` can be a useful shortcut to the options you have specified:

mysql -uroot -e "SELECT concat('ALTER TABLE ',table_schema,'.',table_name, ' ENGINE = MyISAM;') `#sql` FROM information_schema.tables WHERE engine='innodb' AND table_schema = 'test';" | mysql -uroot

Posted by Todd Farmer on February 17, 2010 at 02:46 PM WST #

thanks you very much

Posted by oriental hunter on March 14, 2010 at 01:42 PM WST #

I still have a hero, and I just read a book that reminded me why I admire him so much. His name? Lester Bowles Pearson, prime minister of Canada from 1963 to 1968. Okay, I admit, the lamest hero name ever. But he sounds a lot more heroic when you call him by his nickname — Mike. Now that’s a hero’s name

Posted by aj1s on July 02, 2010 at 03:42 AM WST #

True gold fears no fiery," "golden palace in books," "very strong"replica watchThere is a noble

Posted by goodboyz280 on August 25, 2010 at 05:20 AM WST #

Another way to do it: find the .frm files in all the database directories (except mysql of course), cut off the .frm extension, and call mysql -e from commandline...all with a simple shell script.

Posted by jordans on August 25, 2010 at 05:23 AM WST #

Your comment was marked as spam and will

Posted by mygoodshoe on August 26, 2010 at 04:26 AM WST #

frm files in all the database directories (except mysql of course), cut off the .frm extension, and call mysql -e from commandline...all with a simple shell script.

Posted by Men Abercrombie Tees on September 06, 2010 at 10:32 AM WST #

I still have a hero, and I just read a book that reminded me why I admire him so much. His name? Lester Bowles Pearson, prime minister of Canada from 1963 to 1968. Okay, I admit, the lamest hero name ever. But he sounds a lot more heroic when you call him by his nickname — Mike. Now that’s a hero’s name http://www.areshop.com/Watches-Rolex.html
http://www.areshop.com/
tks

Posted by steve231 on January 12, 2011 at 03:43 AM WST #

Vous pouvez également curl luxuriante, des vagues et flip alors lisseur de cheveux vente est super pratique, car il a fait beaucoup, alors vous donner brillant lisse fers GHD dressage! Chaleur étonnamment vite, vite de nombreux professionnels d'autres top à ce moment-là, j'ai essayé toute unité la moins chère Ghd cheveux fers à défriser.

Posted by Lisseurs Cheveux on February 25, 2011 at 07:54 AM WST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

This blog is a set of tips, tricks and articles from Trent Lloyd - MySQL Support Engineer @ Sun from Perth, Australia working in the APAC team.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today