MySQL doesn't report index uniqueness?

Question

I'm trying to use the MySQL command show indexes from table_name to find out which indexes are unique. But it mistakenly reports that all indexes are non-unique:

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> 

Why does show indexes report the wrong thing in the non_unique column, and how do I get a true answer about which indexes are unique?

(This is MySQL 5.5.24, and the table is InnoDB.)

Answer 1

If you check MySQL Manuals It says :

 Non_unique

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

Check MySQL Manuals