MySQL查詢數(shù)據(jù)庫所有表名以及表結(jié)構(gòu)其注釋(小白專用)

更新時(shí)間：2024年08月01日 08:28:27 作者：zgscwxd

查詢數(shù)據(jù)庫所有表的表名、備注,其實(shí)也是比較常見的操作,這篇文章主要給大家介紹了關(guān)于MySQL查詢數(shù)據(jù)庫所有表名以及表結(jié)構(gòu)其注釋的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下

一、先了解下INFORMATION_SCHEMA

1、在MySQL中，把INFORMATION_SCHEMA看作是一個(gè)數(shù)據(jù)庫，確切說是信息數(shù)據(jù)庫。其中保存著關(guān)于MySQL服務(wù)器所維護(hù)的所有其他數(shù)據(jù)庫的信息。如數(shù)據(jù)庫名，數(shù)據(jù)庫的表，表欄的數(shù)據(jù)類型與訪問權(quán) 限等。在INFORMATION_SCHEMA中，有數(shù)個(gè)只讀表。它們實(shí)際上是視圖，而不是基本表，因此，你將無法看到與之相關(guān)的任何文件。

2、TABLES表：提供了關(guān)于數(shù)據(jù)庫中的表的信息(包括視圖)。詳細(xì)表述了某個(gè)表屬于哪個(gè)schema，表類型，表引擎，創(chuàng)建時(shí)間等信息。是show tables from schemaname的結(jié)果取之此表。

3、COLUMNS表：提供了表中的列信息。詳細(xì)表述了某張表的所有列以及每個(gè)列的信息。是show columns from schemaname.tablename的結(jié)果取之此表。

#查詢所有的數(shù)據(jù)庫名稱
SELECT SCHEMA_NAME AS Database FROM INFORMATION_SCHEMA.SCHEMATA;

#查詢指定數(shù)據(jù)庫下的所有表名（例如information_schema數(shù)據(jù)庫下的所有表名）

select table_name as name  from information_schema.TABLES where TABLE_SCHEMA='information_schema'

查看ftp數(shù)據(jù)庫內(nèi)以oemp開頭的所有的表名、表數(shù)據(jù)量、表備注、字段名稱、字段類型、默認(rèn)值、字段備注等；如果查整個(gè)數(shù)據(jù)庫就把ftp后全刪除。

            string sql = $@"SELECT TABLE_NAME as TableName, 
                     column_name AS DbColumnName,
                     CASE WHEN  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1)='' THEN COLUMN_TYPE ELSE  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1) END AS DataType,
                     CAST(SUBSTRING(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)+1,LOCATE(')',COLUMN_TYPE)-LOCATE('(',COLUMN_TYPE)-1) AS signed) AS Length,
                     column_default  AS  `DefaultValue`,
                     column_comment  AS  `ColumnComment`,
                     CASE WHEN COLUMN_KEY = 'PRI' THEN true ELSE false END AS `IsPrimaryKey`,
                     CASE WHEN EXTRA='auto_increment' THEN true ELSE false END as IsIdentity,
                     CASE WHEN is_nullable = 'YES' THEN true ELSE false END AS `IsNullable`
                     FROM Information_schema.columns where TABLE_NAME='{tableName}' and  TABLE_SCHEMA=(select database()) ORDER BY TABLE_NAME";

SELECT  
    T1.TABLE_COMMENT 表注釋,
    T1.TABLE_ROWS 表數(shù)據(jù)量,
    T2.TABLE_NAME 表名,
    T2.COLUMN_NAME 字段名,  
    T2.COLUMN_TYPE 數(shù)據(jù)類型,  
    T2.DATA_TYPE 字段類型,  
    T2.CHARACTER_MAXIMUM_LENGTH 長度,  
    T2.IS_NULLABLE 是否為空,  
    T2.COLUMN_DEFAULT 默認(rèn)值,  
    T2.COLUMN_COMMENT 字段備注   
FROM 
    INFORMATION_SCHEMA.TABLES T1
LEFT JOIN
    INFORMATION_SCHEMA.COLUMNS T2
ON
    T1.TABLE_NAME = T2.TABLE_NAME
WHERE  
    T1.TABLE_SCHEMA ='ftp'
AND 
    T1.TABLE_NAME LIKE 'oemp%'
ORDER BY 
    T1.TABLE_NAME;

二、如何獲取全部表名

基本的語句為

SELECT table_name FROM information_schema.tables

但是這個(gè)并不符合業(yè)務(wù)需求，因?yàn)檫@會(huì)返回全部的表名，而業(yè)務(wù)中需要限定是哪個(gè)數(shù)據(jù)庫，并且，不同的業(yè)務(wù)可能會(huì)使用不同的表前綴，所以最好可以限定表前綴，并且需要展示表的注釋，不然大家也不清楚表是屬于哪個(gè)業(yè)務(wù)的。

所以，完整的SQL語句如下

SELECT
	TABLE_NAME,
	TABLE_COMMENT 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = 'TABLE_SCHEMA' 
	AND TABLE_NAME LIKE 'x_%' 
	AND TABLE_NAME NOT LIKE 'xx_exp%' 
ORDER BY
	TABLE_NAME

需要配置幾個(gè)參數(shù)，并且已經(jīng)按表名進(jìn)行排序，TABLE_COMMENT 為表注釋。

TABLE_SCHEMA 數(shù)據(jù)庫名稱
x_ 表前綴

運(yùn)行結(jié)果如下圖

1、查看Mysql 數(shù)據(jù)庫 "ori_data"下所有表的表名、表注釋及其數(shù)據(jù)量

SELECT 
TABLE_NAME 表名,TABLE_COMMENT 表注釋,TABLE_ROWS 數(shù)據(jù)量
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'ori_data' 
ORDER BY TABLE_NAME;

SELECT* FROM OPENQUERY (MYSQLTEST ,'
SELECT
	TABLE_NAME as 表名
	 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = ''msldbalitest'' 
	AND TABLE_NAME LIKE ''tp_%'' 
	AND TABLE_NAME NOT LIKE ''cms_exp%'' 
    ORDER BY TABLE_NAME desc

')

2. 查詢數(shù)據(jù)庫 ‘ori_data' 下表 ‘a(chǎn)ccumulation' 所有字段注釋

SELECT 
COLUMN_NAME 字段名,column_comment 字段注釋 
FROM INFORMATION_SCHEMA.Columns 
WHERE table_name='accumulation' AND table_schema='ori_data'

select COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT from information_schema.COLUMNS where table_name = '表名' and table_schema = '數(shù)據(jù)庫名稱';

SELECT* FROM OPENQUERY (MYSQLTEST ,'
SELECT 
COLUMN_NAME as 字段名,DATA_TYPE,column_comment as 字段注釋 
FROM INFORMATION_SCHEMA.Columns 
WHERE table_name=''cms_goods'' AND table_schema=''msldbalitest''

')

3. 查詢數(shù)據(jù)庫 "ori_data" 下所有表的表名、表注釋以及對(duì)應(yīng)表字段注釋

SELECT 
a.TABLE_NAME 表名,a.TABLE_COMMENT 表注釋,b.COLUMN_NAME 表字段,b.COLUMN_TYPE 字段類型,b.COLUMN_COMMENT 字段注釋
FROM information_schema.TABLES a,INFORMATION_SCHEMA.Columns b 
WHERE b.TABLE_NAME=a.TABLE_NAME AND a.TABLE_SCHEMA='ori_data'

SELECT* FROM OPENQUERY (MYSQLTEST ,'
SELECT 
a.TABLE_NAME as 表名,a.TABLE_COMMENT as 表注釋,b.COLUMN_NAME as 表字段,b.COLUMN_TYPE as 字段類型,b.COLUMN_COMMENT as 字段注釋
FROM information_schema.TABLES a,INFORMATION_SCHEMA.Columns b 
WHERE b.TABLE_NAME=a.TABLE_NAME AND a.TABLE_SCHEMA=''msldbalitest''

')

information_schema數(shù)據(jù)庫是MySQL數(shù)據(jù)庫自帶的數(shù)據(jù)庫，里面存放的MySQL數(shù)據(jù)庫所有的信息，包括數(shù)據(jù)表、數(shù)據(jù)注釋、數(shù)據(jù)表的索引、數(shù)據(jù)庫的權(quán)限等等。

Mysql數(shù)據(jù)庫如何獲取某數(shù)據(jù)庫所有表名稱（不包含表結(jié)構(gòu)），Sql如下：

SELECT 
	table_name 
FROM 
	information_schema.tables 
WHERE table_schema = 'xxx' AND table_type = 'base table'

information_schema：Mysql自帶的數(shù)據(jù)庫，存放各類數(shù)據(jù)庫相關(guān)信息的信息數(shù)據(jù)庫，表多為視圖
information_schema.tables：該數(shù)據(jù)庫下的tables表
table_schema：tables表下的一個(gè)字段，數(shù)據(jù)庫名稱
table_type：tables表下的一個(gè)字段，表類型，base table為基礎(chǔ)表，注：有空格
table_name：tables表下的一個(gè)字段，數(shù)據(jù)表名稱

查看指定表的字段及注釋

SELECT* FROM OPENQUERY (MYSQLTEST ,'
select
	a.ordinal_position,
	a.COLUMN_name,
	a.COLUMN_type,
	a.COLumn_comment,
	a.is_nullable,
	a.column_key
from
	information_schema.COLUMNS a
where
	TABLE_schema = ''msldbalitest''
	and TABLE_name = ''cms_admin_menu''

')

查看數(shù)據(jù)所有表名及注釋

SELECT* FROM OPENQUERY (MYSQLTEST ,'
select
	t.TABLE_NAME,
	t.TABLE_COMMENT
from
	information_schema.tables t
where
	t.TABLE_TYPE = ''BASE TABLE''
	and TABLE_schema = ''msldbalitest''
')

在mysql中，information_schema這個(gè)數(shù)據(jù)庫中保存了mysql服務(wù)器所有數(shù)據(jù)庫的信息。

包括數(shù)據(jù)庫名，數(shù)據(jù)庫的表，表字段的數(shù)據(jù)類型等。

簡而言之，若想知道m(xù)ysql中有哪些庫，哪些表，表里面有哪些字段以及他們的注釋，都可以從information_schema中獲取

COLUMNS表

information_schema庫中的COLUMNS表，存放MySQL所有表的字段詳細(xì)信息。

常用列

TABLE_SCHEMA：數(shù)據(jù)庫名
TABLE_NAME：數(shù)據(jù)表名
COLUMN_NAME：數(shù)據(jù)列名
DATA_TYPE：數(shù)據(jù)類型，如：varchar
COLUMN_TYPE：數(shù)據(jù)列類型（含數(shù)據(jù)長度），如：varchar(32)
COLUMN_COMMENT：數(shù)據(jù)列注釋/說明

            string sql = $@"SELECT TABLE_NAME as TableName, 
                     column_name AS DbColumnName,
                     CASE WHEN  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1)='' THEN COLUMN_TYPE ELSE  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1) END AS DataType,
                     CAST(SUBSTRING(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)+1,LOCATE(')',COLUMN_TYPE)-LOCATE('(',COLUMN_TYPE)-1) AS signed) AS Length,
                     column_default  AS  `DefaultValue`,
                     column_comment  AS  `ColumnComment`,
                     CASE WHEN COLUMN_KEY = 'PRI' THEN true ELSE false END AS `IsPrimaryKey`,
                     CASE WHEN EXTRA='auto_increment' THEN true ELSE false END as IsIdentity,
                     CASE WHEN is_nullable = 'YES' THEN true ELSE false END AS `IsNullable`
                     FROM Information_schema.columns where TABLE_NAME='{tableName}' and  TABLE_SCHEMA=(select database()) ORDER BY TABLE_NAME";

使用MySQL創(chuàng)建的表，無論是表注釋、索引，還是字段的類型等等，都會(huì)存到MySQL自帶的庫表中，可以通過SQL查出來想要的表、字段信息。

了解information_schema庫，可以在工作中起到意想不到的效果

-- database_name替換為庫名，查出庫中所有表的TABLE_NAME表名、TABLE_COMMENT表注釋
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='database_name';

TABLES表

information_schema庫中的TABLES表，存放MySQL所有表的表信息。

常用列

TABLE_SCHEMA：數(shù)據(jù)庫名
TABLE_NAME：數(shù)據(jù)表名
TABLE_COMMENT：數(shù)據(jù)表注釋/說明

查詢某個(gè)表的所有字段

select column_name,data_type,column_comment,column_key,extra,character_maximum_length,is_nullable,column_default
from information_schema.columns 
where table_schema = 'seata' and table_name = 'users' ;

組裝表的所有列

select GROUP_CONCAT("t.",column_name) total
from information_schema.columns 
where table_schema = 'seata' and table_name = 'users' and column_name not in ('id');

總結(jié)

到此這篇關(guān)于MySQL查詢數(shù)據(jù)庫所有表名以及表結(jié)構(gòu)其注釋的文章就介紹到這了,更多相關(guān)MySQL查詢所有表名及表結(jié)構(gòu)注釋內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: