我正在尝试使用 MySQL 命令 show indexes from table_name 来找出哪些索引是唯一的。但它错误地报告所有索引都是非唯一的:

mysql> desc books;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id               | int(11)      | NO   | PRI | NULL    |       |
| name             | varchar(255) | YES  |     | NULL    |       |
| author_id        | int(11)      | YES  |     | NULL    |       |
| coauthor_id      | int(11)      | YES  |     | NULL    |       |
| publisher_id     | int(11)      | YES  |     | NULL    |       |
| isbn             | varchar(255) | YES  |     | NULL    |       |
| publication_year | int(11)      | YES  |     | NULL    |       |
| shelf_id         | int(11)      | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> show indexes from books;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| books |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> create unique index books_isbn on books (isbn);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from books;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| books |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| books |          0 | books_isbn |            1 | isbn        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> insert into books (id, name, isbn) values (0, 'foo', 'bar');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into books (id, name, isbn) values (1, 'foo2', 'bar');
ERROR 1062 (23000): Duplicate entry 'bar' for key 'books_isbn'
mysql> 

为什么 show indexes 在 non_unique 列中报告错误的内容，我如何才能得到关于哪些索引是唯一的真实答案？

(这是MySQL 5.5.24，表是InnoDB。)

最佳答案

如果你查看 MySQL 手册 它说:

 Non_unique

0 if the index cannot contain duplicates, 1 if it can. 

检查 MySQL Manuals

关于MySQL 不报告索引唯一性？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/12697177/