TL;DR
SELECT table_schema, table_name, column_name, seq_in_index FROM information_schema.statistics WHERE (table_schema, table_name, column_name) IN (SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE extra LIKE '%auto_increment%') AND (table_schema, table_name, column_name) NOT IN (SELECT DISTINCT table_schema, table_name, column_name FROM information_schema.statistics WHERE seq_in_index = 1);- 無理にSQLでどうにかしなくても、
mysqldump --no-data で引っこ抜いて sed 's/MyISAM/InnoDB/' してテキトーなところにリストアしてみればいいと思うよ
MyISAMストレージエンジンとInnoDBストレージエンジンの非互換(というか、他のストレージエンジンの中でもMyISAMだけに特有な機能)な点に「MyISAMは複合キーの2つ目以降のカラムにAUTO_INCREMENT属性を指定できる」というのがある。
サンプルは↓の4つ。
mysql56> SHOW CREATE TABLE t1\G -- 第1カラムのAUTO_INCREMENT。InnoDBでも有効。
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`one` int(11) NOT NULL AUTO_INCREMENT,
`two` int(11) DEFAULT NULL,
PRIMARY KEY (`one`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql56> SHOW CREATE TABLE t2\G -- 第2カラムのAUTO_INCREMENT。InnoDBでは使えない
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`one` int(11) NOT NULL AUTO_INCREMENT,
`two` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`two`,`one`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql56> SHOW CREATE TABLE t3\G -- 第3カラムのAUTO_INCREMENT。これも使えない。
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`one` int(11) NOT NULL AUTO_INCREMENT,
`two` int(11) NOT NULL DEFAULT '0',
`three` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`two`,`three`,`one`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql56> SHOW CREATE TABLE t4\G -- 第3カラムのAUTO_INCREMENTだけど第1カラムのAUTO_INCREMENTでもあるのでInnoDBでも有効
*************************** 1. row ***************************
Table: t4
Create Table: CREATE TABLE `t4` (
`one` int(11) NOT NULL AUTO_INCREMENT,
`two` int(11) NOT NULL DEFAULT '0',
`three` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`two`,`three`,`one`),
KEY `one` (`one`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
このうちt2とt3だけが引っ掛けられれば正解。
まずはAUTO_INCREMENT属性を持ったカラムを引っこ抜くか。
mysql56> SELECT table_schema, table_name, column_name, extra FROM information_schema.columns WHERE extra LIKE '%auto_increment%';
+--------------+------------+--------------+----------------+
| table_schema | table_name | column_name | extra |
+--------------+------------+--------------+----------------+
| d1 | t1 | one | auto_increment |
| d1 | t2 | one | auto_increment |
| d1 | t3 | one | auto_increment |
| d1 | t4 | one | auto_increment |
| mysql | time_zone | Time_zone_id | auto_increment |
+--------------+------------+--------------+----------------+
5 rows in set (0.02 sec)
ここから「第1カラムにインデックスを持っているものを除外」すればいいはず。
「インデックスの第1カラムに指定されているカラム」は seq_in_index = 1 で表現できる。
mysql56> SELECT table_schema, table_name, column_name, index_name, seq_in_index FROM information_schema.statistics WHERE table_schema = 'd1';
+--------------+------------+-------------+------------+--------------+
| table_schema | table_name | column_name | index_name | seq_in_index |
+--------------+------------+-------------+------------+--------------+
| d1 | t1 | one | PRIMARY | 1 |
| d1 | t2 | two | PRIMARY | 1 |
| d1 | t2 | one | PRIMARY | 2 |
| d1 | t3 | two | PRIMARY | 1 |
| d1 | t3 | three | PRIMARY | 2 |
| d1 | t3 | one | PRIMARY | 3 |
| d1 | t4 | two | PRIMARY | 1 |
| d1 | t4 | three | PRIMARY | 2 |
| d1 | t4 | one | PRIMARY | 3 |
| d1 | t4 | one | one | 1 |
+--------------+------------+-------------+------------+--------------+
10 rows in set (0.00 sec)
mysql56> SELECT DISTINCT table_schema, table_name, column_name FROM information_schema.statistics WHERE table_schema = 'd1' AND seq_in_index = 1;
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| d1 | t1 | one |
| d1 | t2 | two |
| d1 | t3 | two |
| d1 | t4 | two |
| d1 | t4 | one |
+--------------+------------+-------------+
5 rows in set (0.00 sec)
という訳でサブクエリーを使ってこんな感じになるかしらん。
SELECT
table_schema,
table_name,
column_name
FROM
information_schema.statistics
WHERE
(table_schema, table_name, column_name) IN
(SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE extra LIKE '%auto_increment%') /* ←AUTO_INCREMENTなカラムの一覧サブクエリ */ AND
(table_schema, table_name, column_name) NOT IN
(SELECT DISTINCT table_schema, table_name, column_name
FROM information_schema.statistics
WHERE seq_in_index = 1) /* ←第1カラムなインデックス一覧サブクエリ */;
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| d1 | t2 | one |
| d1 | t3 | one |
+--------------+------------+-------------+
2 rows in set (0.03 sec)
まあこんなことしなくても、 mysqldump --no-data でテーブル定義だけ引っこ抜いて、MyISAMをInnoDBに書き換えてやればエラーになるんでわかるんですけどね。
$ mysqldump56 --set-gtid-purged=OFF --no-data d1 > tables.sql
$ sed -i 's/MyISAM/InnoDB/' tables.sql
$ mysql56 -v -f d1 < tables.sql
..
--------------
CREATE TABLE `t1` (
`one` int(11) NOT NULL AUTO_INCREMENT,
`two` int(11) DEFAULT NULL,
PRIMARY KEY (`one`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
--------------
..
--------------
CREATE TABLE `t2` (
`one` int(11) NOT NULL AUTO_INCREMENT,
`two` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`two`,`one`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
--------------
ERROR 1075 (42000) at line 39: Incorrect table definition; there can be only one auto column and it must be defined as a key
..
--------------
CREATE TABLE `t3` (
`one` int(11) NOT NULL AUTO_INCREMENT,
`two` int(11) NOT NULL DEFAULT '0',
`three` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`two`,`three`,`one`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
--------------
ERROR 1075 (42000) at line 53: Incorrect table definition; there can be only one auto column and it must be defined as a key
..
--------------
CREATE TABLE `t4` (
`one` int(11) NOT NULL AUTO_INCREMENT,
`two` int(11) NOT NULL DEFAULT '0',
`three` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`two`,`three`,`one`),
KEY `one` (`one`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
--------------
..
