mysql数据库查看表结构的好方法(亲测)

SELECT
    TABLE_NAME AS '表名',
    '' AS '列名',
    '' AS '类型',
    '' AS '含义'
FROM
    information_schema. COLUMNS
WHERE
    table_schema = 'colliery'
AND table_name = 'dim_bid_type'
LIMIT 1
UNION ALL
    SELECT
        '' AS '',
        COLUMN_NAME AS '',
        COLUMN_TYPE AS '',
        COLUMN_COMMENT AS ''
    FROM
        information_schema. COLUMNS
    WHERE
        table_schema = 'colliery'
    AND table_name = 'dim_bid_type';

____________________________________________________________

注意：其中colliery是我自己的库名，使用的时候应该替换成你自己的 ，dim_bid_type也是我自己的表名使用的时候也应该替换成你自己的

_________________________________________________

更简便的方式如下：

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE,
    DATA_TYPE,
    COLUMN_COMMENT
FROM
    information_schema. COLUMNS
WHERE
    table_schema = '库名'
AND table_name = '表名';

 

---------------------------------------------------------------------------2020 最新完善

SELECT
    information_schema. COLUMNS .TABLE_NAME AS '表名',
    '' AS '列名',
    '' AS '类型',
    information_schema. TABLES .TABLE_COMMENT AS '含义'
FROM
    information_schema. COLUMNS
LEFT JOIN information_schema. TABLES ON (
    information_schema. COLUMNS .TABLE_NAME = information_schema. TABLES .TABLE_NAME
)
WHERE
    information_schema. COLUMNS .table_schema = '库名'
AND information_schema. COLUMNS .table_name = '表名'
LIMIT 1
UNION ALL
    SELECT
        '' AS '',
        COLUMN_NAME AS '',
        COLUMN_TYPE AS '',
        COLUMN_COMMENT AS ''
    FROM
        information_schema. COLUMNS
    WHERE
        table_schema = '库名'
    AND table_name = '表名'; 

_____________________________________________________________

________________________________________2023年最新完善

SELECT
    information_schema. COLUMNS .TABLE_NAME AS '表名',
    '' AS '列名',
    '' AS '类型',
    information_schema.`TABLES`.TABLE_COMMENT AS '含义'
FROM
    information_schema. COLUMNS,
    information_schema.`TABLES`
WHERE
    information_schema. COLUMNS .table_schema = 'riskmanagement'
AND information_schema. COLUMNS .table_name = 't_city'
AND information_schema. COLUMNS .table_schema = information_schema.`TABLES`.TABLE_SCHEMA
AND information_schema. COLUMNS .TABLE_NAME = information_schema.`TABLES`.TABLE_NAME 
LIMIT 1
UNION ALL
    SELECT
        '' AS '',
        COLUMN_NAME AS '',
        COLUMN_TYPE AS '',
        COLUMN_COMMENT AS ''
    FROM
        information_schema. COLUMNS
    WHERE
        table_schema = 'riskmanagement'
    AND table_name = 't_city';