MySQL中dd::columns表结构转table过程及应用详解

在MySQL中，我们经常需要获取某张表的结构信息，这时可以使用SHOW COLUMNS FROM table_name命令来实现。但是，在某些场景下，我们需要将多张表的结构信息保存在一个单独的表中进行统计分析，因此需要将SHOW COLUMNS命令的结果转为表格式，这时就可以使用dd::columns表来完成。

dd::columns表介绍

dd::columns表是系统信息架构(Schema Information Schema)的一部分，它存储了当前数据库中所有表的列信息，包括列名、数据类型、主键、外键等。我们可以使用SELECT语句查询dd::columns表中的信息，也可以通过将某个表名作为参数传递给dd::columns表来获取该表的结构信息。

dd::columns表结构转table

为了将dd::columns表中的信息转为表格式，我们需要使用GROUP BY子句按照Table name列进行分组，并使用GROUP_CONCAT函数将每个组内的数据连接成一个字符串。最后，我们可以使用CREATE TABLE语句来创建一个新的表，并通过INSERT INTO语句将数据插入到这个新表中。下面是具体步骤：

使用以下命令查询dd::columns表，并按照Table name列进行排序：

SELECT * FROM information_schema.columns ORDER BY `Table name`;

使用以下命令将dd::columns表中的信息转为表格式，并保存到新表table_structure中：

```
CREATE TABLE table_structure (
table_name VARCHAR(100) NOT NULL,
columns TEXT NOT NULL,
PRIMARY KEY (table_name)
);

INSERT INTO table_structure (table_name, columns)
SELECT Table name, GROUP_CONCAT(CONCAT(Column name, ' ', Data type, IF(Is nullable = 'NO', ' NOT NULL', '')) SEPARATOR ',') AS 'columns'
FROM information_schema.columns
GROUP BY Table name;
```

dd::columns表应用示例

示例一：查询所有表的列数

有时我们需要统计每张表的列数，这时可以使用以下命令：

SELECT table_name, CHAR_LENGTH(columns) - CHAR_LENGTH(REPLACE(columns, ',', '')) + 1 AS num_columns
FROM table_structure;

该命令会返回table_structure表中所有表的列数信息。

示例二：查询所有表的占用空间大小

有时我们需要知道每张表占用的空间大小，这时可以使用以下命令：

SELECT `Table Name`, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE
FROM information_schema.tables
WHERE `Table Name` IN (SELECT table_name FROM table_structure);

该命令会返回所有table_structure表中的表的占用空间大小信息。