如何在 MySQL 中检查表字段上是否存在索引

如何检查 MySQL 中的表字段上是否存在索引？

我需要在谷歌上搜索很多次，所以我分享了我的问答。

116820 次浏览
小开
最佳答案

Use SHOW INDEX like so:

SHOW INDEX FROM [tablename]

Docs: https://dev.mysql.com/doc/refman/5.0/en/show-index.html

小开

要从 CLI 查看表的布局，您可以使用

desc mytable

或者

show table mytable
小开

试试:

SELECT * FROM information_schema.statistics
WHERE table_schema = [DATABASE NAME]
AND table_name = [TABLE NAME] AND column_name = [COLUMN NAME]

它将告诉您某个列上是否存在任何类型的索引，而不需要知道索引的名称。它还将在存储过程中工作(与显示索引相反)

小开 
SHOW KEYS FROM  tablename WHERE Key_name='unique key name'

将显示表中是否存在唯一键。

小开 
show index from table_name where Column_name='column_name';
小开

无法运行特定的 show index 查询，因为如果索引不存在，它将抛出错误。因此，如果希望避免任何 SQL 错误，必须将所有索引抓取到一个数组中并循环遍历它们。

我是这么做的。我从表中获取所有索引(在本例中是 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_r of $res in order to see what you can work with.

小开

您可以使用以下 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;

由于联接是针对 INNODB _ SYS _ * 的，所以匹配索引只来自 INNODB 表。

小开

使用以下语句:

SHOW INDEX FROM *your_table*

And then check the result for the fields: row["Table"], row["Key_name"]

确保您正确地写了“ Key _ name”

小开

If you need to check if a index for a column exists as a database function, you can use/adopt this code. 如果您想检查一个索引是否存在，而不管它在多列索引中的位置，那么只需删除 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 ;
小开

试着用这个:

SELECT TRUE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = "{DB_NAME}"
AND TABLE_NAME = "{DB_TABLE}"
AND COLUMN_NAME = "{DB_INDEXED_FIELD}";
小开

GK10还提出了以下建议:

Use the following statement: SHOW INDEX FROM your_table

And then check the result for the fields: row["Table"], Row [“ Key _ name”]

确保您正确地写了“ Key _ name”

我们可以利用这一点，将其转换为 PHP (或其他语言) ，包装在 sql 语句中，以查找索引列。基本上，您可以将 SHOW INDEX FROM‘ mytable’的结果放入 PHP 中，然后使用列‘ Column _ name’获取索引列。

Make your database connection string and do something like this:

$mysqli = mysqli_connect("localhost", "my_user", "my_password", "world");


$sql =  "SHOW INDEX FROM 'mydatabase.mytable' WHERE Key_name = 'PRIMARY';" ;
$result = mysqli_query($mysqli, $sql);


while ($row = $result->fetch_assoc()) {
echo $rowVerbatimsSet["Column_name"];
}

提交答案