如何限制SHOW TABLES查询 [英] How to limit SHOW TABLES query

问题描述

我有以下查询:

SHOW TABLES LIKE '$prefix%'

尽管我需要分页显示结果，但它确实可以达到我想要的效果.我试过了:

It works exactly how I want it to, though I need pagination of the results. I tried:

SHOW TABLES LIKE '$prefix%' ORDER BY Comment ASC LIMIT 0, 6

我需要它返回所有带有特定前缀的表，并按其注释对它们进行排序.我想通过LIMIT进行分页，每页6个结果.

I need it to return all the tables with a certain prefix and order them by their comment. I want to have pagination via the LIMIT with 6 results per page.

我显然做错了什么.如何做到这一点?

I'm clearly doing something very wrong. How can this be accomplished?

我确实查看了

I did look at this. It didn't work for me.

推荐答案

以上操作不能直接通过MySQL语法完成. MySQL在某些SHOW语句上不支持LIMIT子句.这就是其中之一. MySQL参考文档.

The above cannot be done via MySQL Syntax directly. MySQL does not support the LIMIT clause on certain SHOW statements. This is one of them. MySQL Reference Doc.

如果您的MySQL用户可以访问INFORMATION_SCHEMA数据库，则下面的内容将起作用.

The below will work if your MySQL user has access to the INFORMATION_SCHEMA database.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_TO SEARCH_HERE' AND TABLE_NAME LIKE "table_here%"  LIMIT 0,5;

这篇关于如何限制SHOW TABLES查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！