mysql查询/导出所有表的结构，类型，注释，是否主键

先上图

使用navicat将查出数据直接导出excel享用。

完整sql语句：

SELECT 
    c.TABLE_NAME as '表名',              -- 表名
    c.COLUMN_NAME as '列名',             -- 列名
    c.COLUMN_TYPE as '数据类型',             -- 列数据类型
    c.COLUMN_COMMENT as '注释',          -- 列注释
    c.ORDINAL_POSITION as '排序',        -- 列位置
    IF(k.COLUMN_NAME IS NOT NULL, '是', '否') AS '是否主键'  -- 是否为主键
FROM 
    INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN 
    (
        SELECT 
            TABLE_SCHEMA, 
            TABLE_NAME, 
            COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.STATISTICS 
        WHERE 
            INDEX_NAME = 'PRIMARY'  -- 只选择主键信息
        GROUP BY 
            TABLE_SCHEMA, 
            TABLE_NAME, 
            COLUMN_NAME
    ) k ON c.TABLE_SCHEMA = k.TABLE_SCHEMA AND c.TABLE_NAME = k.TABLE_NAME AND c.COLUMN_NAME = k.COLUMN_NAME
WHERE 
    c.TABLE_SCHEMA = 'database'  -- 替换为您的数据库名
ORDER BY 
    c.TABLE_SCHEMA, 
    c.TABLE_NAME, 
    c.ORDINAL_POSITION;

再提供一下简单版的单表查询

SELECT 
    c.COLUMN_NAME AS `列名`,
    c.COLUMN_TYPE AS `数据类型`,
    c.COLUMN_COMMENT AS `注释`,
    IF(s.INDEX_NAME = 'PRIMARY', '是', '否') AS `是否主键`
FROM
    INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN 
    INFORMATION_SCHEMA.STATISTICS s ON c.TABLE_SCHEMA = s.TABLE_SCHEMA 
                                    AND c.TABLE_NAME = s.TABLE_NAME 
                                    AND c.COLUMN_NAME = s.COLUMN_NAME
WHERE
    c.TABLE_SCHEMA = 'database' -- 替换为您的数据库名
    AND c.TABLE_NAME = 'table';  -- 替换为您的表名