Mysql快速列出来所有列信息

需求描述

如何将MySQL数据库中指定表【tb_order】的所有字段都展示出来，以备注中的中文名为列名。

实现思路

最终展示效果，即拼接出可执行执行的SQL，如下图所示。

建表语句如下所示。

CREATE TABLE `tb_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `user_id` bigint(20) NOT NULL COMMENT '用户id:长度20位',
  `name` varchar(100) DEFAULT NULL COMMENT '商品名称',
  `pay_amount` bigint(20) NOT NULL COMMENT '商品价格:单位分',
  `order_status` varchar(8) DEFAULT NULL COMMENT '订单状态',
  `num` bigint(10) DEFAULT '0' COMMENT '商品数量',
  `data_info` varchar(255) DEFAULT NULL COMMENT '订单信息',
  `is_enable` varchar(10) DEFAULT NULL COMMENT '是否有效:1-有效',
  `created_user` varchar(20) DEFAULT NULL COMMENT '创建人',
  `created_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `indx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

实现思路，即主要解决以下几个问题

1、如何查表信息

可以从 数据库系统表information_schema的 COLUMNS 中取目标表的信息。

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

执行SQL查询结果如下所示。

2、如何取字段描述信息

列备注的字段描述中有【冒号: 】，如何只取冒号前的内容，可 使用SUBSTRING_INDEX函数。

格式：SUBSTRING_INDEX(str, delimiter, number)
str 目标字符串
delimiter 截取的分隔符
number 表示出现的位置
即 返回从字符串str的第number个出现的分隔符delimiter之前的子串

对于本需求，截取冒号前的字符串，可使用 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';

执行以上SQL，结果如下所示。

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

将列的信息在一行显示，可 使用GROUP_CONCAT函数

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';

执行以上SQL如下所示。

4、拼接最终结果

使用 CONCAT 函数，拼接想要关联展示的信息，比如 select 、as 等，即可将信息完全展示，最终SQL如下所示，使用时 替换其中的【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';

执行以上SQL，结果如下所示。

注：中文作为列的命名 可使用【`】，英文状态下 esc键 下一行的第一个键。
复制出来的拼接的结果，即本案例中想要展示的内容，如下所示。

SELECT
	id AS `订单id`,
	user_id AS `用户id`,
	NAME AS `商品名称`,
	pay_amount AS `商品价格`,
	order_status AS `订单状态`,
	num AS `商品数量`,
	data_info AS `订单信息`,
	is_enable AS `是否有效`,
	created_user AS `创建人`,
	created_time AS `创建时间`
FROM
	tb_order

直接执行即可，控制台信息如下所示。

以上即本案例的过程，可参考使用。