Bug #5199 (ENHANCEMENT) SHOW TABLES IN database NOT LIKE '...'

• Submitted: 25 Aug 2004 9:06
• Modified: 20 Dec 2005 10:27
• Reporter: Jon Watson
• Status: Closed
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 4.1.2
• OS: Windows (Windows & Linux / ALL)
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[25 Aug 2004 9:06] Jon Watson

Description:
It appears that MySQL does not support a negative inclusion for selecting certain tables over others. For example the following two SQL statements would make logical sense, but neither works in my version (4.1.2-alpha):

SHOW TABLES IN lockbase NOT LIKE '%sometext%'

SELECT * FROM (SHOW TABLES IN lockbase) AS my_table WHERE Tables_in_lockbase NOT LIKE '%sometext%'

It would be great if MySQL could support these statements.

How to repeat:
Create 4 tables with the following names:

ib_table1
ib_table2
table3
table4

Now execute the following SQL statements:

SHOW TABLES IN lockbase NOT LIKE 'ib_%'

SELECT * FROM (SHOW TABLES IN lockbase) AS my_table WHERE Tables_in_lockbase NOT LIKE 'ib_%'

Suggested fix:
This may turn out to be two separate issues. The first would be an update to the SHOW TABLES statement support to make it support "NOT LIKE". The second might be an enhancement to allow MySQL to support creating a "table alias" for the result set that is returned from the SHOW TABLES statement.

Unfortunately, I have no code-specific fix in mind. My C and C++ skills are very weak. So it would be best to leave that to the masters. :o)

[26 Aug 2004 11:32] Sergei Golubchik

This is already being implemented in 5.0 :)

[20 Dec 2005 10:27] Sergei Glukhov

Fixed in 5.0.3

[19 Aug 2009 3:22] Malcolm Ke ([e-mail])

In fact, even in mysql 5.0.45,
to query "SHOW TABLES NOT LIKE 'kw%'", it still raise error:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'not LIKE 'kw%'' at line 2