如何限制 SHOW TABLES 查询

How to limit SHOW TABLES query(如何限制 SHOW TABLES 查询)

问题描述

我有以下查询:

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 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！