Thursday Oct 29, 2009

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!

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
« October 2009
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
30
31
       
Today