CODEWITHSUNDEEP
mysql
Ben Coffin
Ben Coffin

Reputation: 607

SHOW TABLE STATUS WHERE Rows > 0

In MySQL 5.7, this query runs fine:

SHOW TABLE STATUS WHERE Rows > 0

In MySQL 8.022, it produces the following fault:

#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 'Rows > 0' at line 1

It seems there's no issue with querying on other status columns:

SHOW TABLE STATUS WHERE Data_length > 0 
SHOW TABLE STATUS WHERE Avg_row_length > 0

... and so on.

Is this a known issue with this version of MySQL?

Upvotes: 1

Views: 362

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-R says;

ROWS (R); became reserved in 8.0.2

Since Rows now is a reserved keyword, it needs to be quoted to use in that way;

SHOW TABLE STATUS WHERE `Rows` > 0

Upvotes: 5

Related Questions