information_schema里面根本不正确

Question

select *
from TABLE_CONSTRAINTS tc
where tc.TABLE_NAME='INV_ON_HAND';INV_ON_HAND 上面有4个索引,但是Mysql报告说就有一个primary

Answer 1

TABLE_CONSTRAINTS里只记录了约束，如UNIQUE、PRIMARY KEY或FOREIGN KEY
索引记录在STATISTICS中。

Answer 2

mysql> select *                                                                                 
    -> from information_schema.STATISTICS tc                                                    
    -> where tc.TABLE_NAME='t2';                                                                
+---------------+--------------+------------+------------+--------------+------------+----------
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_IN
+---------------+--------------+------------+------------+--------------+------------+----------
| NULL          | test         | t2         |          0 | test         | PRIMARY    |          
+---------------+--------------+------------+------------+--------------+------------+----------
1 row in set (0.08 sec)                                                                         
                                                                                                
mysql>