I ran into an interesting problem trying to select table names that use prefixes. It is common to use a prefix on table names as a way to group different sets of tables. At work we have two applications using the same database; one of them uses a prefix of 's_'.
In MySQL you can get a list of table names that match a pattern by using
SHOW TABLES LIKE 'foo%';
However, I wanted to get a list of tables that didn't match a pattern. To do this I had to use the information_schema table. The query ended up looking something like:
SELECT table_name FROM tables WHERE table_schema = 'foo' AND table_name NOT LIKE 's_%' AND table_name NOT LIKE 'data_%';
At first glance this seemed ok, but on closer inspection I found it wasn't getting the table named 'session'.
I had forgotten that '_' is a special character when it comes to pattern matching. This page describes how '%' matches an arbitrary number of characters (including zero characters) and '_' matches any single character, so table 'session' was being matched.
The simple solution was to escape the underscore character, so it then becomes:
SELECT table_name FROM tables WHERE table_schema = 'foo' AND table_name NOT LIKE 's\_%' AND table_name NOT LIKE 'data\_%';
The other option would have been to use the REGEXP and NOT REGEXP operators instead of standard SQL pattern matching.