查询索引名和字段名相同的表及查找重复索引

查看statistics结构:
information_schema 02:51:12>desc statistics;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

查询用例：
select table_schema,table_name,column_name,index_name from information_schema.statistics where 
TABLE_SCHEMA not in ('mysql','performance_schema','information_schema','common_schema') and table_name='g_a_task'

+---------------+-------------+---------------+------------------------+
| table_schema  | table_name  | column_name   | index_name             |
+---------------+-------------+---------------+------------------------+
| m_g_ver | g_a_task | _id           | PRIMARY                |
| m_g_ver | g_a_task | id            | id                     |
| m_g_ver | g_a_task | task_no       | idx_gsat_task_no       |
| m_g_ver | g_a_task | upper_task_no | idx_gsat_upper_task_no |
+---------------+-------------+---------------+------------------------+

查看columns结构:
desc columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |
| TABLE_NAME               | varchar(64)         | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |
| DATA_TYPE                | varchar(64)         | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |
| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |
| EXTRA                    | varchar(27)         | NO   |     |         |       |
| PRIVILEGES               | varchar(80)         | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |
+--------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.00 sec)

查询用例：
select table_name, COLUMN_NAME from information_schema.columns where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema','common_schema')
and COLUMN_NAME='vamp_no';
+-----------------------+-------------+
| table_name            | COLUMN_NAME |
+-----------------------+-------------+
| go_task               | vamp_no     |
| g_t_y | vamp_no     |
| g_a_task           | vamp_no     |
| gs_ship_task          | vamp_no     |
+-----------------------+-------------+

查询索引名和字段名相同的表和字段：
方法一：
select table_schema,table_name,column_name,index_name from information_schema.statistics i where 
i.TABLE_SCHEMA not in ('mysql','performance_schema','information_schema','common_schema') and i.index_name=i.column_name;
方法二：
select c.TABLE_SCHEMA ,c.table_name, c.COLUMN_NAME,i.index_name from information_schema.statistics c ,information_schema.statistics  i
 where c.TABLE_SCHEMA not in ('mysql','performance_schema','information_schema','common_schema')
and c.COLUMN_NAME=i.index_name and c.table_name=i.table_name and c.COLUMN_NAME=i.column_name;
方法三：
select c.TABLE_SCHEMA ,c.table_name, c.COLUMN_NAME,i.index_name from information_schema.columns c ,information_schema.statistics  i
 where c.TABLE_SCHEMA not in ('mysql','performance_schema','information_schema','common_schema')
and c.COLUMN_NAME=i.index_name and c.table_name=i.table_name and c.COLUMN_NAME=i.column_name

验证上面三种方法是否正确：

下面字段名vamp_no和索引名相同
show create table m_g_ver.g_t_y\G
PRIMARY KEY (`_id`),
UNIQUE KEY `id` (`id`), ----字段和索引名相同
KEY `idx_gotr_task_no` (`task_no`),
KEY `vamp_no` (`vamp_no`)  ----字段和索引名相同

方法一：
select a.* from (
select table_schema,table_name,column_name,index_name from information_schema.statistics i where 
i.TABLE_SCHEMA not in ('mysql','performance_schema','information_schema','common_schema') and i.index_name=i.column_name)a
where a.table_name='g_t_y';
+---------------+-----------------------+-------------+------------+
| table_schema  | table_name            | column_name | index_name |
+---------------+-----------------------+-------------+------------+
| m_g_ver | g_t_y | id          | id         |
| m_g_ver | g_t_y | vamp_no     | vamp_no    |
+---------------+-----------------------+-------------+------------+

方法二：
select a.* from (
select c.TABLE_SCHEMA ,c.table_name, c.COLUMN_NAME,i.index_name from information_schema.statistics c ,information_schema.statistics  i
 where c.TABLE_SCHEMA not in ('mysql','performance_schema','information_schema','common_schema')
and c.COLUMN_NAME=i.index_name and c.table_name=i.table_name and c.COLUMN_NAME=i.column_name)a
where a.table_name='g_t_y';
+---------------+-----------------------+-------------+------------+
| table_schema  | table_name            | column_name | index_name |
+---------------+-----------------------+-------------+------------+
| m_g_ver | g_t_y | id          | id         |
| m_g_ver | g_t_y | vamp_no     | vamp_no    |
+---------------+-----------------------+-------------+------------+

方法三：
select a.* from (
select c.TABLE_SCHEMA ,c.table_name, c.COLUMN_NAME,i.index_name from information_schema.columns c ,information_schema.statistics  i
 where c.TABLE_SCHEMA not in ('mysql','performance_schema','information_schema','common_schema')
and c.COLUMN_NAME=i.index_name and c.table_name=i.table_name and c.COLUMN_NAME=i.column_name)a
where a.table_name='g_t_y';
+---------------+-----------------------+-------------+------------+
| TABLE_SCHEMA  | table_name            | COLUMN_NAME | index_name |
+---------------+-----------------------+-------------+------------+
| m_g_ver | g_t_y | id          | id         |
| m_g_ver | g_t_y | vamp_no     | vamp_no    |
+---------------+-----------------------+-------------+------------+

查找重复索引：
SELECT table_name,column_name,COUNT(1) ct
FROM information_schema.`STATISTICS`
WHERE seq_in_index=1
AND table_schema='m_g_ver'
GROUP BY table_name,column_name
HAVING ct >=2;
+----------------+-------------+----+
| table_name     | column_name | ct |
+----------------+-------------+----+
| g_s_so | bking_no  |  2 |
+----------------+-------------+----+
1 row in set (0.03 sec)

 UNIQUE KEY `unique_booking_no` (`bking_no`) USING BTREE,
  KEY `idx_gsss_booking_no` (`bking_no`)