MySQL 匹配表名与显示表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4432062/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
matching tables name with show tables
提问by Katie
I'm trying to get all tables name from a database.
我正在尝试从数据库中获取所有表名。
In my DB I have tables like:
在我的数据库中，我有如下表：
_table1,
_table2,
table3,
table4,
table5_xrefs
but I want to get only the tables without _ at the beginning and without _xref at the end. So in this example I need only table3 and table4.
但我只想获得开头没有 _ 且结尾没有 _xref 的表格。所以在这个例子中，我只需要 table3 和 table4。
I'm using show tablesto show all tables name and after I'm using PHP to match the correct table name. I was wondering if I could do the same using only a MySQL query.
我show tables用来显示所有表名，然后我使用 PHP 来匹配正确的表名。我想知道是否可以只使用 MySQL 查询来做同样的事情。
回答by dev-null-dweller
It's possible but you have to know that column name returned from SHOW TABLESquery is concatenation of string tables_in_and your database name. So it would look like this, for database test:
这是可能的，但您必须知道从SHOW TABLES查询返回的列名称是字符串tables_in_和数据库名称的串联。所以它看起来像这样，对于数据库test：
SHOW TABLES
WHERE tables_in_test NOT LIKE '\_%'
AND tables_in_test NOT LIKE '%\_xrefs'
But I would prefer to use information_schemadatabase to get this info:
但我更喜欢使用information_schema数据库来获取此信息：
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = SCHEMA() /* = 'test'*/
AND TABLE_NAME NOT LIKE '\_%'
AND TABLE_NAME NOT LIKE '%\_xrefs'
回答by rik
You can use LIKE or WHEREin SHOW TABLES queries.
您可以在 SHOW TABLES 查询中使用 LIKE 或 WHERE。
