Mysql快速列出來所有列信息實(shí)現(xiàn)思路

更新時(shí)間：2025年01月23日 10:21:19 作者：夢(mèng)幻通靈

本文介紹了如何使用MySQL查詢系統(tǒng)表和內(nèi)置函數(shù),將指定表的所有字段信息（包括字段名和注釋）以指定格式展示出來,本文給大家分享實(shí)現(xiàn)思路,感興趣的朋友一起看看吧

需求描述

如何將MySQL數(shù)據(jù)庫(kù)中指定表【tb_order】的所有字段都展示出來，以備注中的中文名為列名。

實(shí)現(xiàn)思路

最終展示效果，即拼接出可執(zhí)行執(zhí)行的SQL，如下圖所示。

建表語句如下所示。

CREATE TABLE `tb_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '訂單id',
  `user_id` bigint(20) NOT NULL COMMENT '用戶id:長(zhǎng)度20位',
  `name` varchar(100) DEFAULT NULL COMMENT '商品名稱',
  `pay_amount` bigint(20) NOT NULL COMMENT '商品價(jià)格:單位分',
  `order_status` varchar(8) DEFAULT NULL COMMENT '訂單狀態(tài)',
  `num` bigint(10) DEFAULT '0' COMMENT '商品數(shù)量',
  `data_info` varchar(255) DEFAULT NULL COMMENT '訂單信息',
  `is_enable` varchar(10) DEFAULT NULL COMMENT '是否有效:1-有效',
  `created_user` varchar(20) DEFAULT NULL COMMENT '創(chuàng)建人',
  `created_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `indx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

實(shí)現(xiàn)思路，即主要解決以下幾個(gè)問題

1、如何查表信息

可以從數(shù)據(jù)庫(kù)系統(tǒng)表information_schema的 COLUMNS 中取目標(biāo)表的信息。

SELECT
	ORDINAL_POSITION,
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT
FROM
	information_schema. COLUMNS
WHERE
	TABLE_NAME = 'tb_order';

執(zhí)行SQL查詢結(jié)果如下所示。

2、如何取字段描述信息

列備注的字段描述中有【冒號(hào): 】，如何只取冒號(hào)前的內(nèi)容，可使用SUBSTRING_INDEX函數(shù)。

格式：SUBSTRING_INDEX(str, delimiter, number)
str 目標(biāo)字符串
delimiter 截取的分隔符
number 表示出現(xiàn)的位置
即返回從字符串str的第number個(gè)出現(xiàn)的分隔符delimiter之前的子串

對(duì)于本需求，截取冒號(hào)前的字符串，可使用 SUBSTRING_INDEX(column_comment, ‘:’, 1)。

SELECT
	ORDINAL_POSITION,
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT,
	SUBSTRING_INDEX(column_comment, ':', 1) as columnComment
FROM
	information_schema. COLUMNS
WHERE
	TABLE_NAME = 'tb_order';

執(zhí)行以上SQL，結(jié)果如下所示。

3、如何將列信息一行展示

將列的信息在一行顯示，可使用GROUP_CONCAT函數(shù)

SELECT
	ORDINAL_POSITION,
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT,
	GROUP_CONCAT(SUBSTRING_INDEX(column_comment, ':', 1)) as columnComment
FROM
	information_schema. COLUMNS
WHERE
	TABLE_NAME = 'tb_order';

執(zhí)行以上SQL如下所示。

4、拼接最終結(jié)果

使用 CONCAT 函數(shù)，拼接想要關(guān)聯(lián)展示的信息，比如 select 、as 等，即可將信息完全展示，最終SQL如下所示，使用時(shí) 替換其中的【tb_order】即可。

SELECT 
    CONCAT('select ',
            GROUP_CONCAT(CONCAT(COLUMN_NAME,
                        ' as `',
                        SUBSTRING_INDEX(column_comment, ':', 1)),
                '`'),
            ' from ',
            'tb_order') AS querySql
FROM
    information_schema.COLUMNS
WHERE
    TABLE_NAME = 'tb_order';

執(zhí)行以上SQL，結(jié)果如下所示。

注：中文作為列的命名可使用【`】，英文狀態(tài)下 esc鍵下一行的第一個(gè)鍵。
復(fù)制出來的拼接的結(jié)果，即本案例中想要展示的內(nèi)容，如下所示。

SELECT
	id AS `訂單id`,
	user_id AS `用戶id`,
	NAME AS `商品名稱`,
	pay_amount AS `商品價(jià)格`,
	order_status AS `訂單狀態(tài)`,
	num AS `商品數(shù)量`,
	data_info AS `訂單信息`,
	is_enable AS `是否有效`,
	created_user AS `創(chuàng)建人`,
	created_time AS `創(chuàng)建時(shí)間`
FROM
	tb_order

直接執(zhí)行即可，控制臺(tái)信息如下所示。