mysql: What is the right syntax for NOT LIKE?

Hi I am trying to show tables with names not like a pattern by mysql is throws an error:

SHOW TABLES  NOT LIKE  "tree%";

returns:

#1064 - You have an error in your SQL syntax; check the manual that 开发者_StackOverflow中文版corresponds to your MySQL server version for the right syntax to use near 'NOT LIKE "tree%"' at line 1

What is the right syntax?

Thanks Arman.

You could use the WHERE clause extension, as in:

SHOW TABLES WHERE `Tables_in_<DatabaseName>` NOT LIKE 'tree%';

This is supported on MySQL ≥5.0.

Reference:

• 12.4.5.39. SHOW TABLES Syntax.
• 20.28. Extensions to SHOW Statements.

LIKE and NOT LIKE are used with SELECT statements. I don't think this works with the SHOW TABLES command.

According to this feature request, this has been introduced in mySQL 5.0.3. However, people there disagree, and it doesn't work in my 5.1.41 installation, either.

I guess the answer is it's not possible.