mysql 查询数据库中的表名_mysql如何查表名-优快云博客

本文链接：https://blog.youkuaiyun.com/Olivia_Vang/article/details/103172800

本文介绍了如何使用MySQL查询包含特定关键词的表名，包括展示含有关键词的表和查询所有表的方法，如`show tables`命令，并提及可以按数据库名筛选。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询一个数据库中含有某关键词的表名

搜索一个数据库中包含一些关键字，词的表。

SELECT 
    TABLE_NAME 
FROM
    information_schema. TABLES
WHERE
    table_schema = '数据库名'
    AND TABLE_NAME LIKE '%name%';

例

mysql> select table_name from information_schema.tables 
    -> where table_schema = 'sakila'
    -> and table_name like "%film%";
+----------------------------+
| TABLE_NAME                 |
+----------------------------+
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| nicer_but_slower_film_list |
| sales_by_film_category     |
+----------------------------+
7 rows in set (0.00 sec)

查询数据库中所有的表

show tables；

-- use 数据库名
USE sakila;
SHOW TABLES;

同上面，where条件只查数据库名。还可以同时搜索多个数据库中的表。

SELECT 
    TABLE_NAME 
FROM
    information_schema. TABLES
WHERE
    table_schema = '数据库名';

例

mysql> select table_name from information_schema.tables where table_schema = 'sakila';
+----------------------------+
| TABLE_NAME                 |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

mysql> select table_name from information_schema.tables where table_schema = 'sakila' or table_schema = 'test';
+----------------------------+
| TABLE_NAME                 |
+----------------------------+
| actor                      |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| film                       |
| film_actor                 |
| film_category              |
| film_text                  |
| inventory                  |
| language                   |
| payment                    |
| rental                     |
| staff                      |
| store                      |
| customer_list              |
| film_list                  |
| nicer_but_slower_film_list |
| staff_list                 |
| sales_by_store             |
| sales_by_film_category     |
| actor_info                 |
| employee                   |
| test_alter                 |
+----------------------------+
25 rows in set (0.01 sec)

以上。