MyISAMで第2カラムのAUTO_INCREMENTを使ってるテーブルを洗い出すSQL

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属性を指定できる」というのがある。

• MySQL :: MySQL 8.0 Reference Manual :: 3.6.9 Using AUTO_INCREMENT
  
  言葉で言ってもわかりにくいんだけど、サンプルは昔々に↓に書いた。
• MySQLのInnoDBでもPRIMARY KEYの2カラム目以降にAUTO_INCREMENTを使いたい | GMOメディア エンジニアブログ
  
  
• この時はどうやら第2カラムのAUTO_INCREMENTの存在はわかっていたっぽいんだけれど、今やりたいのは「ゼロから第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
--------------
..

​