在 MySQL 中获取表元数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/510410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting table metadata in MySQL
提问by VirtuosiMedia
I'm trying to find out how to get the following constraint information from a table in MySQL 5.0:
我试图找出如何从 MySQL 5.0 中的表中获取以下约束信息：
- primary key
- foreign keys and table references
- unique columns
- 首要的关键
- 外键和表引用
- 独特的列
What is the syntax of the query or queries to do so? I have a feeling I'm close with this, but there is no example.
查询的语法是什么？我有一种感觉我很接近这个，但没有例子。
采纳答案by fluffels
The SHOW COLUMNScommand will show you the primary key and unique columns for a table.
该SHOW COLUMNS命令将显示一个表的主键和唯一列。
As for foreign keys, you coulduse something like the SHOW CREATE TABLEcommand which will output the DDL statements needed to replicate the table.
至于外键，您可以使用类似SHOW CREATE TABLE命令的命令，该命令将输出复制表所需的 DDL 语句。
回答by Nelson Santos
For MySQL:
对于 MySQL：
1) get Table/Fields metadata
1) 获取表/字段元数据
SELECT table_schema, table_name, column_name, ordinal_position, data_type,
numeric_precision, column_type, column_default, is_nullable, column_comment
FROM information_schema.columns
WHERE (table_schema='schema_name' and table_name = 'table_name')
order by ordinal_position;
OR
或者
show fields from 'table_name'
2) get Foregn Keys referenced table
2) 获取外键引用表
SELECT `REFERENCED_TABLE_NAME`
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE
`TABLE_NAME` = 'table_name' AND
`COLUMN_NAME` = 'Column_Name'
3) get indexes (primary and foreign) for a table
3) 获取表的索引（主索引和外索引）
show keys from `table_name`
5) get All indexes and referreced table
5）获取所有索引和引用表
SELECT *
FROM `KEY_COLUMN_USAGE`
WHERE
`TABLE_NAME` = 'table_name' AND
`TABLE_SCHEMA` = 'schema_name'
OR
或者
SELECT *
FROM `REFERENTIAL_CONSTRAINTS`
WHERE
`TABLE_NAME` = 'table_name' AND
`CONSTRAINT_SCHEMA` = 'schema_name'
6) get STORED PROCEDURES
6) 获取存储过程
SELECT *
FROM `ROUTINES`
WHERE
`ROUTINE_SCHEMA` = 'schema_name'
7) get TRIGGERS
7）获得触发器
SELECT *
FROM `TRIGGERS`
WHERE
`TRIGGER_SCHEMA` = 'schema_name'
8) get EVENTS
8) 获取事件
SELECT *
FROM `EVENTS`
WHERE
`EVENT_SCHEMA` = 'schema_name'
9) get VIEWS
9）获得意见
SELECT *
FROM `VIEWS`
WHERE
`TABLE_NAME` = 'table_name' AND
`TABLE_SCHEMA` = 'schema_name'
回答by jx12345
Use
用
show fields from table_name
show keys from table_name
to get primary keys, foreign keys, unique, etc.
获取主键、外键、唯一键等。
to get the table referenced by a foreign key use:
要获取外键引用的表，请使用：
SELECT `REFERENCED_TABLE_NAME`
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE
`TABLE_NAME` = '[table_containing_foreign_key]' AND
`COLUMN_NAME` = '[foreign_key]'
substituting [table_containing_foreign_key]and [foreign_key]with your values
替换[table_containing_foreign_key]并[foreign_key]与你的价值观
回答by PKMeiyappan
use the following to get the same using Select Query:
使用以下使用 Select Query 获得相同的结果：
SELECT table_schema, table_name, column_name, ordinal_position, data_type, numeric_precision, column_type FROM information_schema.columns WHERE table_name = '[TABLE_NAME]';
回答by MarkR
You should try it and see. INFORMATION_SCHEMA is part of some standard and is supported in a (mostly) similar way in other databases; this standard should be documented - you can look for that doc.
你应该试试看。INFORMATION_SCHEMA 是某些标准的一部分，在其他数据库中以（大多数）类似的方式得到支持；这个标准应该被记录下来——你可以寻找那个文档。
But mainly the way would be to create a bunch of test tables, and then have a look at INFORMATION_SCHEMA to see what's there.
但主要的方法是创建一堆测试表，然后查看 INFORMATION_SCHEMA 看看那里有什么。
