3 useful MySQL queries

I regularly have to create database update scripts i.e. scripts that create tables or add new columns to existing tables or populate new tables with base data. One requirement is that these scripts be re-runnable. For example, if a new table doesn’t exist it should be created, if it does exist then the update script should continue without throwing an error. For these situations I find the following simple queries very useful.

Wrap them in functions written in your language of choice and you have an easy way to check the current status of your MySQL database before taking action.

1. Check if a table exists in the current database schema

select table_name as found
from information_schema.tables
where table_schema = SCHEMA()
and table_name = '__table_name__'

2. Check if a column exists in a table

select column_name as found
from information_schema.columns
where table_schema = SCHEMA()
and table_name = '__table_name__'
and column_name = '__column_name__'

3. Check if a table contains any data

select 1
from __table_name__
limit 1

After running each of these queries, simply check if any rows were returned. A non-zero row count indicates the existence of the table, the column or the data as appropriate.

These three queries, and a couple of variations on them, can also be found on my Gist page.



One thought on “3 useful MySQL queries

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s