Abstract MySQL Selects

Posted by on October 25, 2011 in Abstraction, Database, MySQL | 0 comments

Sometimes if you have a rapidly changing database, you have to do maintenance on tables, but you may not want to keep updating your script to account for new tables and/or columns you create. If this is the case, you have to make your code a little more abstract. MySQL has a few commands that help with this.

For instance, if you are replicating a set of databases to another server, and want to make sure the replication is up to date, you could use the following commands to help you out.

SHOW DATABASES

This will list out all the databases that are in your system. They will be returned in the “Database” column of the query results.

SHOW TABLES IN `database_name`

This will list out all the tables that are in the database you tell it. You could also run this command without the “in” section if you are already sitting inside a database. The table names will be returned in the “Tables_in_XXXXX” column where XXXXX is the name of the database.

SELECT * FROM `database_name`.`table_name` ORDER BY _rowid DESC LIMIT 1

In our example, we’re just going to compare 1 row, the last row. Normally you would need to know what the column name is of the row you want to order by, but MySQL has a handy sort feature that you can use. The _rowid will sort on the primary key, no matter what the key name is. The key must be an integer value though. This will not work on varchars. If working with an unsupported primary key data type, you’ll have to add logic into your code to check for the error that’s returned. Combined with the LIMIT 1, we’ll get the very last row of the table. If you want to get the column name of the primary key you can use the following command:

SHOW INDEX FROM `database_name`.`table_name`

This will list out some data about the indexes of the table. The name of the column will be in the “Column_name” column. The “Key_name” column will indicate the primary key by having the text “PRIMARY” in it.

Using these four commands, you can iterate through your entire database structure without hard coding any specific names of databases, tables, or columns. You can then make a change to the structure, add a database, or a table, and you won’t have to update your script at all.

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>