MySql 查询所有索引数据

命令如下：

SELECT     * 
 FROM     (
     SELECT
         CONCAT( 'ALTER TABLE morseapp-data.', i.TABLE_NAME, ' DROP INDEX ', i.INDEX_NAME, ' ;' ) dropIndexCmd 
     FROM         INFORMATION_SCHEMA.STATISTICS i 
     WHERE
         i.TABLE_SCHEMA = 'morseapp-data'  -- 库名
         AND i.TABLE_NAME IN ( 'radios', 'radio_users' )  -- 表名
         AND i.INDEX_NAME <> 'PRIMARY'  -- 主键索引
     ) a 
 GROUP BY -- 如果不用 GROUP BY 则，一个索引有多个字段组成的时候，会有多个记录
     a.dropIndexCmd;

附：索引操作命令

查询索引

查看整个库的
 SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = '库名';
或者使用查看表的索引
 (1)show index from tblname;
 (2)show keys from tblname;

创建索引

Alter Table 修改表的形式创建索引

(1)ALTER TABLE table_name ADD INDEX index_name (column_list)
(2)ALTER TABLE table_name ADD UNIQUE (column_list)
(3)ALTER TABLE table_name ADD PRIMARY KEY (column_list)

Create Index 创建索引

(1)CREATE INDEX index_name ON table_name (column_list)
(2)CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引

(1)DROP INDEX index_name ON talbe_name
(2)ALTER TABLE table_name DROP INDEX index_name
(3)ALTER TABLE table_name DROP PRIMARY KEY --不推荐