以下是几种获取MySQL表结构信息的常用SQL语句:
1. DESCRIBE / DESC 语句(最简单)
DESCRIBE 表名;
DESC 表名;
2. SHOW COLUMNS 语句
SHOW COLUMNS FROM 表名;
SHOW COLUMNS FROM 表名 FROM 数据库名;
SHOW COLUMNS FROM 表名 IN 数据库名;
SHOW FULL COLUMNS FROM 表名;
3. SHOW CREATE TABLE 语句
SHOW CREATE TABLE 表名;
-- 获取最全面的字段信息
SELECT
COLUMN_NAME AS
COLUMN_TYPE AS
IS_NULLABLE AS
COLUMN_DEFAULT AS
COLUMN_COMMENT AS
EXTRA AS
COLUMN_KEY AS
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA =
AND TABLE_NAME =
ORDER BY ORDINAL_POSITION;
5. 查询更详细的信息
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE,
IS_NULLABLE,
COLUMN_DEFAULT,
COLUMN_COMMENT,
COLLATION_NAME,
COLUMN_TYPE,
EXTRA,
COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名'
AND TABLE_NAME = '你的表名'
ORDER BY ORDINAL_POSITION;
6. 使用特定条件查询
SHOW COLUMNS FROM 表名 LIKE '字段前缀%';
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名'
AND TABLE_NAME = '你的表名'
AND COLUMN_COMMENT LIKE '%关键词%';
7. 简洁的汇总查询
SELECT
COLUMN_NAME '字段',
COLUMN_TYPE '类型',
IS_NULLABLE '可空',
COLUMN_KEY '键',
COLUMN_DEFAULT '默认值',
COLUMN_COMMENT '注释'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = '你的表名';
实用建议:
- 对于快速查看,使用
DESC 表名;
- 对于详细信息,使用
SHOW FULL COLUMNS FROM 表名;
- 对于编程或导出需求,使用
INFORMATION_SCHEMA 查询
- 如果需要建表语句,使用
SHOW CREATE TABLE 表名;
示例:
DESC employees;
SHOW FULL COLUMNS FROM employees;
SELECT
COLUMN_NAME,
COLUMN_TYPE,
IS_NULLABLE,
COLUMN_DEFAULT,
COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'employees'
ORDER BY ORDINAL_POSITION;
选择哪种方法取决于你需要的信息详细程度和使用场景。