Bookmark and Share

Bulk alter schema in MySQL.

Posted: Monday, April 20th, 2009 at 4:47 pmUpdated: Tuesday, May 12th, 2009 at 10:02 am

There are times when you want to alter schema for all tables in the database. For example, let’s say in your project you have multiple databases with multiple tables on each of them. Suppose that you have columns within that table defined as DECIMAL(11,6). What it means is that the column will have 11 digits and 6 of those digits are decimal points. Therefore, your maximum value is just under 100K.

Suppose your company is doing well and you’re now dealing with numbers greater than 100K. Obviously you need to do alter statements to increase the decimal size, perhaps to DECIMAL(22,6). One way to do this is to go over each table manually and find the columns that needs to be changed. This method may work fine for a small set of tables. If you have a lot of tables and multiple databases, updating it manually may be tedious and there’s a good chance you miss something.

There has to be another way to do this, preferably programmatically or automatically. Hopefully that’s what you get by the end of reading this article.

Introducing MySQL INFORMATION_SCHEMA

INFORMATION_SCHEMA is a database within MySQL that’s available starting from MySQL 5.0. As the name implies, it basically contains information / meta data of the schema within MySQL. Here’s one example of using it:

Previously, you have to use SHOW DATABASES or SHOW TABLES to get listing of databases or tables in a MySQL server. Now let’s say you have hundreds of tables within a database and you want to display tables with names like %history% for example. Using SHOW TABLES, there was no way for you to add a conditional clause (at least pre 5.0.3).

Using INFORMATION_SCHEMA, you can now simply write a SELECT statement and all arsenals of WHERE clauses and groupings to query the schema structure. For example, to get tables with names like %history%, you can simply issue a statement like below:

SELECT
	*
FROM
	INFORMATION_SCHEMA.TABLES
WHERE
	TABLE_NAME LIKE '%history%'
;

Using INFORMATION_SCHEMA to build ALTER statements

Now that we know about INFORMATION_SCHEMA and that we can use it to query data that we want, the idea is to come up with a query that will generate SQL statement, run it, and execute the resulting generated SQL statements.

Going back to our example above, the basic SQL statement that we need to generate is like the following:

ALTER TABLE
	[db_name].[table_name]
CHANGE
	[column_name] DECIMAL(22,6) [column_attributes]
;

So let’s get started. Below is the query that will actually generate the ALTER SQL ready to run:

SELECT
	CONCAT(
		'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,
		GROUP_CONCAT(
			CONCAT(
				'\n\tCHANGE ',
				COLUMN_NAME,
				' DECIMAL(22, 6) ',
				IF(IS_NULLABLE = 'NO', 'NOT ', ''),
				'NULL default \'',
				COLUMN_DEFAULT,
				'\''
			)
		), '\n;'
	) AS sql_string
FROM
	information_schema.COLUMNS
WHERE
	DATA_TYPE = 'decimal'
	AND NUMERIC_PRECISION = 11
	AND NUMERIC_SCALE = 6
GROUP BY
	TABLE_SCHEMA, TABLE_NAME
;

Explaining the SQL query

First, I’d like to explain the use of GROUP BY on the query above. There are good chances that in one table there are more than 1 column that we need to change. If we don’t use GROUP BY, we’ll end up with 2 or more completely independent ALTER statement; one for each column. It is something that I definitely want to avoid as MySQL may have to create temporary table first before doing the alter.

Other than that, I think the SQL statement above is pretty self explanatory. I used MySQL’s CONCAT function to join the strings. Also don’t forget to add column attributes to it. What I wanted was to preserve the column attributes (such as nullable and default values). Therefore, I can be sure that the only change was from DECIMAL(11,6) to DECIMAL(22,6)

There you have it … As a last word, even though the title says MySQL and I’ve used it against MySQL database, the same technique would also be applicable to other databases, as long as they have INFORMATION_SCHEMA equivalent. According to MySQL documentation, their implementation of INFORMATION_SCHEMA is following ANSI/ISO SQL:2003 standard Part 11 Schemata. There’s a good change that the SQL statement above will work verbatim on other databases that also implements ANSI/ISO SQL:2003 standard Part 11 Schemata.

I hope you enjoyed this article. Please leave comments / suggestions / questions if you have. I’m looking forward to improving my solution with your comments / suggestions / questions.

One Response to “Bulk alter schema in MySQL.”

  1. Gus Says:

    This is somthing I am trying to do. I would like to alter schema from time to time to add columns to a table or update column type or resize column size on multiple databases.

    I guest you can use the default property to set a value and than alter and remove the default statement on the query.

Leave a Reply