共计 2629 个字符,预计需要花费 7 分钟才能阅读完成。
通常我们提到数据库表结构的时候,比较关心的几个属性:
字段名称、类型、长度、是否主键、是否自增、是否为空、默认值、备注。
那么,使用 SQL 语句获取这些属性,在各主流数据库下是什么样的呢?
MySQL 数据库
作为时下最流行的数据库,MySQL 的获取 SQL 是最简单的。
一般我们用下面一行语句就能搞定:
SHOW FULL COLUMNS FROM xxx;
当然,也可以用 MySQL 自带的数据库 information_schema 中的表,例如:columns 等查询更全的信息。
PostgreSQL 数据库
使用其两个最重要的 Schema(information_schema、pg_catalog)下表,进行关联查询,获取常用属性。
SELECT d.column_name AS "Field", d.udt_name AS "Type", COALESCE(d.character_maximum_length, d.numeric_precision, d.datetime_precision) AS "Length",
CASE WHEN t.conname IS NOT NULL THEN 'PRI' ELSE ''END AS"Key",
CASE WHEN s.extra IS NOT NULL THEN 'auto_increment' ELSE ''END"Extra",
d.is_nullable AS "Null", f.adsrc AS "Default", col_description(a.attrelid, a.attnum) AS "Comment"
FROM information_schema.columns d, pg_class c, pg_attribute a
LEFT JOIN pg_constraint t ON (a.attrelid = t.conrelid AND t.contype = 'p' AND a.attnum = t.conkey[1])
LEFT JOIN pg_attrdef f ON (a.attrelid = f.adrelid AND a.attnum = f.adnum)
LEFT JOIN (SELECT 'nextval(''' || c.relname || '''::regclass)' AS extra FROM pg_class c WHERE c.relkind = 'S') s ON f.adsrc = s.extra
WHERE a.attrelid = c.oid
AND a.attnum > 0
AND c.relname = d.table_name
AND d.column_name = a.attname
AND c.relname = 'xxx'
ORDER BY a.attnum;
Oracle 数据库
系统表:user_col_comments 能提供大部分信息,只有:备注 (Comment)、主键(Key) 需要关联其他表进行获取。
SELECT t.column_name AS "Field", t.data_type AS "Type", t.data_length AS "Length",
CASE WHEN k.column_name IS NOT NULL THEN 'PRI' ELSE ''END AS"Key",
CASE WHEN t.identity_column = 'YES' THEN 'auto_increment' ELSE ''END AS"Extra",
CASE WHEN t.nullable = 'N' THEN 'NO' ELSE 'YES' END AS "Null",
t.data_default AS "Default", c.comments AS "Comment"
FROM user_col_comments c, user_tab_cols t
LEFT JOIN (
SELECT u.table_name, c.column_name
FROM user_constraints u, user_cons_columns c
WHERE u.table_name = c.table_name
AND u.constraint_name = c.constraint_name
AND u.constraint_type = 'P') k ON t.table_name = k.table_name AND t.column_name = k.column_name
WHERE t.table_name = c.table_name
AND t.column_name = c.column_name
AND t.table_name = 'xxx'
ORDER BY t.column_id;
MsSQL 数据库
MsSQL 即 SQL Server 数据库。MsSQL 相对更加复杂一些,关联的系统表也最多。
SELECT a.name AS 'Field', b.name AS 'Type', COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 'Length',
CASE WHEN p.name IS NOT NULL THEN 'PRI' ELSE ''END'Key',
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN 'auto_increment' ELSE ''END AS'Extra',
CASE WHEN a.isnullable=1 THEN 'YES' ELSE 'NO' END AS 'Null',
e.text AS 'Default', g.[value] AS 'Comment'
FROM sysobjects d, syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON (a.id = G.major_id AND a.colid = g.minor_id)
LEFT JOIN (
SELECT s.name, k.id, k.colid FROM sysindexkeys k, sysindexes i, sysobjects s
WHERE k.indid = i.indid
AND s.name = i.name
AND s.xtype = 'PK'
) p ON (p.id = a.id AND p.colid = a.colid)
WHERE d.id = a.id
AND d.xtype = 'U'
AND d.name = 'xxx'
ORDER BY a.colorder;
注:以上均参考 MySQL 的属性名输出。xxx 为需要查询的表名。
diboot 简单高效的轻代码开发框架 (求 star)
正文完