Mysql "like" returning unexpected results

Question

I am creating a MySQL Query to get the size of all tables which contain a specified string only.

I am using the code below:

SELECT
TABLE_NAME AS `Table`,
(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024)) AS `Size (MB)`
FROM information_schema.TABLES
WHERE TABLE_SCHEMA ='Servername' AND
(TABLE_NAME LIKE '%log_%' OR TABLE_NAME LIKE '%_log%')
GROUP BY  TABLE_SCHEMA
ORDER BY TABLE_SCHEMA;

Expected Output
--------------------
Table     Size (MB)
--------------------
login_log 10.0005
log_login 00.8800

Resulting Output
--------------------
Table     Size (MB)
--------------------
login_log 10.0005
login     05.000
log_login 00.8800

In the above result, login table is not required, but MySQL is returning it. How can I avoid this to get only those tables containing the specified string?

Answer 1

Your underscore _ also acts as a wildcard, indicating any character.

You could fix it by escaping your underscore in your SQL statement, using a backslash character \:

(TABLE_NAME LIKE '%log\_%' OR TABLE_NAME LIKE '%\_log%')

Answer 2

You could use something similar to the query below:

SELECT
TABLE_NAME AS `Table`,
(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024)) AS `Size (MB)`
FROM information_schema.TABLES
WHERE TABLE_SCHEMA ='Servername' AND
(TABLE_NAME LIKE '%\_log' OR TABLE_NAME LIKE 'log\_%')
GROUP BY  TABLE_SCHEMA
ORDER BY TABLE_SCHEMA;

The output would be the following:

Table     Size (MB)
--------------------
login_log 10.0005
log_login 00.8800