MySQL通过SQL语句查询表结构

李广妮2024/8/8

MySQL通过SQL语句查询表结构

查询表的字段属性

要在 MySQL 中查询数据库表的字段名称、类型、注释、是否允许为空、以及主键情况，您可以使用 INFORMATION_SCHEMA.COLUMNS 和 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 表。下面是具体的 SQL 查询语句：

SELECT 
    TABLE_SCHEMA AS `Database`,
    TABLE_NAME AS `Table`,
    COLUMN_NAME AS `Column`,
    COLUMN_TYPE AS `Data Type`,
    IS_NULLABLE AS `Nullable`,
    COLUMN_KEY AS `Key`,
    COLUMN_COMMENT AS `Comment`
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = 'your_database_name'  -- 替换为你的数据库名称
    AND TABLE_NAME = 'your_table_name';  -- 替换为你要查询的表名称

这个查询将返回如下信息：


Database	数据库名称
Table	表名称
Column	字段名称
Data Type	字段的数据类型
Nullable	是否允许为空（YES 或 NO）
Key	字段是否为主键（PRI 表示主键）
Comment	字段的注释

示例

SELECT 
    TABLE_SCHEMA AS `Database`,
    TABLE_NAME AS `Table`,
    COLUMN_NAME AS `Column`,
    COLUMN_TYPE AS `Data Type`,
    IS_NULLABLE AS `Nullable`,
    COLUMN_KEY AS `Key`,
    COLUMN_COMMENT AS `Comment`
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = 'ruoyi-cloud-plus'  -- 替换为你的数据库名称
    AND TABLE_NAME = 'sys_user';  -- 替换为你要查询的表名称

查询表的主键信息

如果需要进一步查询主键的情况，可以使用 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 表来获取主键的信息：

SELECT 
    TABLE_SCHEMA AS `Database`,
    TABLE_NAME AS `Table`,
    COLUMN_NAME AS `Column`,
    REFERENCED_COLUMN_NAME AS `Referenced Column`,
    CONSTRAINT_NAME AS `Constraint Name`
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    TABLE_SCHEMA = 'your_database_name'
    AND TABLE_NAME = 'your_table_name'
    AND CONSTRAINT_NAME = 'PRIMARY';

这个查询将返回主键列的信息，包括表名、列名、约束名等。

示例

SELECT 
    TABLE_SCHEMA AS `Database`,
    TABLE_NAME AS `Table`,
    COLUMN_NAME AS `Column`,
    REFERENCED_COLUMN_NAME AS `Referenced Column`,
    CONSTRAINT_NAME AS `Constraint Name`
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    TABLE_SCHEMA = 'ruoyi-cloud-plus'
    AND TABLE_NAME = 'sys_user'
    AND CONSTRAINT_NAME = 'PRIMARY';