如何检查 MySQL 中的表字段上是否存在索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/127156/
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
How do I check if an index exists on a table field in MySQL?
提问by Sean
I've needed to Google this a couple times, so I'm sharing my Q/A.
我需要谷歌几次，所以我要分享我的问答。
回答by Sean
Use SHOW INDEXlike so:
SHOW INDEX像这样使用：
SHOW INDEX FROM [tablename]
Docs: https://dev.mysql.com/doc/refman/5.0/en/show-index.html
回答by Stéphan Champagne
Try:
尝试：
SELECT * FROM information_schema.statistics
WHERE table_schema = [DATABASE NAME]
AND table_name = [TABLE NAME] AND column_name = [COLUMN NAME]
It will tell you if there is an index of any kind on a certain column without the need to know the name given to the index. It will also work in a stored procedure (as opposed to show index)
它会告诉您某个列上是否有任何类型的索引，而无需知道为索引指定的名称。它也可以在存储过程中工作（而不是显示索引）
回答by pulock
SHOW KEYS FROM tablename WHERE Key_name='unique key name'
you can find if there exists an unique key in the table
您可以查找表中是否存在唯一键
回答by Somil
show index from table_name where Column_name='column_name';
回答by GK10
Use the following statement: SHOW INDEX FROM your_table
使用以下语句：SHOW INDEX FROM your_table
And then check the result for the fields: row["Table"], row["Key_name"]
然后检查字段的结果：row["Table"], row["Key_name"]
Make sure you write "Key_name" correctly
确保正确写入“Key_name”
回答by J.J.
to just look at a tables layout from the cli. you would do
只需查看 cli 中的表格布局。你会做
desc mytable
描述我的表
or
或者
show table mytable
显示表 mytable
回答by Hubbe73
If you need the functionality if a index for a column exists (here at first place in sequence) as a database function you can use/adopt this code. If you want to check if an index exists at all regardless of the position in a multi-column-index, then just delete the part "AND SEQ_IN_INDEX = 1".
如果您需要该功能，如果列的索引存在（此处按顺序排在第一位）作为数据库函数，您可以使用/采用此代码。如果你想检查一个索引是否存在而不管多列索引中的位置，那么只需删除“AND SEQ_IN_INDEX = 1”部分。
DELIMITER $$
CREATE FUNCTION `fct_check_if_index_for_column_exists_at_first_place`(
`IN_SCHEMA` VARCHAR(255),
`IN_TABLE` VARCHAR(255),
`IN_COLUMN` VARCHAR(255)
)
RETURNS tinyint(4)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Check if index exists at first place in sequence for a given column in a given table in a given schema. Returns -1 if schema does not exist. Returns -2 if table does not exist. Returns -3 if column does not exist. If index exists in first place it returns 1, otherwise 0.'
BEGIN
-- Check if index exists at first place in sequence for a given column in a given table in a given schema.
-- Returns -1 if schema does not exist.
-- Returns -2 if table does not exist.
-- Returns -3 if column does not exist.
-- If the index exists in first place it returns 1, otherwise 0.
-- Example call: SELECT fct_check_if_index_for_column_exists_at_first_place('schema_name', 'table_name', 'index_name');
-- check if schema exists
SELECT
COUNT(*) INTO @COUNT_EXISTS
FROM
INFORMATION_SCHEMA.SCHEMATA
WHERE
SCHEMA_NAME = IN_SCHEMA
;
IF @COUNT_EXISTS = 0 THEN
RETURN -1;
END IF;
-- check if table exists
SELECT
COUNT(*) INTO @COUNT_EXISTS
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = IN_SCHEMA
AND TABLE_NAME = IN_TABLE
;
IF @COUNT_EXISTS = 0 THEN
RETURN -2;
END IF;
-- check if column exists
SELECT
COUNT(*) INTO @COUNT_EXISTS
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = IN_SCHEMA
AND TABLE_NAME = IN_TABLE
AND COLUMN_NAME = IN_COLUMN
;
IF @COUNT_EXISTS = 0 THEN
RETURN -3;
END IF;
-- check if index exists at first place in sequence
SELECT
COUNT(*) INTO @COUNT_EXISTS
FROM
information_schema.statistics
WHERE
TABLE_SCHEMA = IN_SCHEMA
AND TABLE_NAME = IN_TABLE AND COLUMN_NAME = IN_COLUMN
AND SEQ_IN_INDEX = 1;
IF @COUNT_EXISTS > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END$$
DELIMITER ;
回答by De Paradox
Try use this:
尝试使用这个：
SELECT TRUE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = "{DB_NAME}"
AND TABLE_NAME = "{DB_TABLE}"
AND COLUMN_NAME = "{DB_INDEXED_FIELD}";
回答by Dian Yudha Negara
you can use the following SQL statement to check the given column on table was indexed or not
您可以使用以下 SQL 语句来检查表上的给定列是否已编入索引
select a.table_schema, a.table_name, a.column_name, index_name
from information_schema.columns a
join information_schema.tables b on a.table_schema = b.table_schema and
a.table_name = b.table_name and
b.table_type = 'BASE TABLE'
left join (
select concat(x.name, '/', y.name) full_path_schema, y.name index_name
FROM information_schema.INNODB_SYS_TABLES as x
JOIN information_schema.INNODB_SYS_INDEXES as y on x.TABLE_ID = y.TABLE_ID
WHERE x.name = 'your_schema'
and y.name = 'your_column') d on concat(a.table_schema, '/', a.table_name, '/', a.column_name) = d.full_path_schema
where a.table_schema = 'your_schema'
and a.column_name = 'your_column'
order by a.table_schema, a.table_name;
since the joins are against INNODB_SYS_*, so the match indexes only came from INNODB tables only
因为连接是针对 INNODB_SYS_*，所以匹配索引仅来自 INNODB 表
回答by kjdion84
You can't run a specific show index query because it will throw an error if an index does not exist. Therefore, you have to grab all indexes into an array and loop through them if you want to avoid any SQL errors.
您无法运行特定的 show index 查询，因为如果索引不存在，它将引发错误。因此，如果您想避免任何 SQL 错误，您必须将所有索引抓取到一个数组中并循环遍历它们。
Heres how I do it. I grab all of the indexes from the table (in this case, leads) and then, in a foreach loop, check if the column name (in this case, province) exists or not.
继承人我如何做到这一点。我从表中获取所有索引（在本例中为leads），然后在 foreach 循环中检查列名（在本例中为province）是否存在。
$this->name = 'province';
$stm = $this->db->prepare('show index from `leads`');
$stm->execute();
$res = $stm->fetchAll();
$index_exists = false;
foreach ($res as $r) {
if ($r['Column_name'] == $this->name) {
$index_exists = true;
}
}
This way you can really narrow down the index attributes. Do a print_rof $resin order to see what you can work with.
通过这种方式，您可以真正缩小索引属性的范围。做print_r的$res，为了看看有什么可以一起工作。
