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

yoku0825 さんが、面白そうなブログを書いていました。
yoku0825.blogspot.com

　MySQLの auto_increment 列はキーカラムである必要がありますが、このキーは複合キーでも構わない。ただし複合キーの場合は、InnoDB ではキー指定のうちの1つめのカラムである必要があり、MyISAMなら2つめ以降のカラムでも良い、という違いがあるそうです。

mysql> create table tt1 (c1 integer auto_increment, c2 integer, c3 integer, key(c2, c1, c3)) engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> create table tt1 (c1 integer auto_increment, c2 integer, c3 integer, key(c2, c1, c3)) engine=myisam;                      
Query OK, 0 rows affected (0.01 sec)

mysql> create table tt2 (c1 integer auto_increment, c2 integer, c3 integer, key(c1, c2, c3)) engine=innodb;                      
Query OK, 0 rows affected (0.02 sec)

　MyISAMで作ったテーブルの中に、こういった「key 2カラム目以降auto_incre」があると、そのままではInnoDB化ができないために、どのテーブルで使っているのかを把握しておきたいという意図でしょうか（想像）。
テーマとして面白そうだったのと、ブログで紹介されていたクエリが直観的に「もっとシンプルになるはず」と感じたので、試してみました。

準備

　yoku0825さんのブログを見て、t1, t2, t3, t4 の4つのテーブルを作成します。
私は今回「test」スキーマの中に作りました。

テーブルを作ったあとは、とりあえず i_s をデフォルトスキーマにしちゃったほうがラクなので。

mysql> use information_schema;
Database changed

SQLあそび

とりあえず、キーの情報は STATISTICS テーブルにあるので見てみる。

SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.COLUMN_NAME, s.SEQ_IN_INDEX
  FROM STATISTICS s
 WHERE s.TABLE_SCHEMA='test'
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | SEQ_IN_INDEX |
+--------------+------------+-------------+--------------+
| test         | tripoint   | id          |            1 |
| test         | kanko      | c1          |            1 |
| test         | t1         | one         |            1 |
| test         | t2         | one         |            2 |
| test         | t2         | two         |            1 |
| test         | t4         | one         |            3 |
| test         | t4         | two         |            1 |
| test         | t4         | three       |            2 |
| test         | t4         | one         |            1 |
| test         | t3         | one         |            3 |
| test         | t3         | two         |            1 |
| test         | t3         | three       |            2 |
+--------------+------------+-------------+--------------+

とりあえず auto_increment の情報は、COLUMNS テーブルの EXTRA にあるので見てみる。

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, EXTRA
  FROM COLUMNS
 WHERE EXTRA LIKE '%AUTO_INCRE%';

+--------------+-----------------+--------------+----------------+
| TABLE_SCHEMA | TABLE_NAME      | COLUMN_NAME  | EXTRA          |
+--------------+-----------------+--------------+----------------+
| test2        | location_tweet  | id           | auto_increment |
| test2        | spot1           | id           | auto_increment |
| test2        | spot2           | id           | auto_increment |
| mysql        | component       | component_id | auto_increment |
| mysql        | time_zone       | Time_zone_id | auto_increment |
| test         | t1              | one          | auto_increment |
| test         | t2              | one          | auto_increment |
| test         | t4              | one          | auto_increment |
| test         | t3              | one          | auto_increment |
+--------------+-----------------+--------------+----------------+

　
　
とりあえずくっつけようか。
STATISTICS の右側に、auto_incrementである情報がついているくらいがちょうどよさげ。
結果は、見やすいようにテーブルごとに線を入れてみました。

SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.COLUMN_NAME, s.SEQ_IN_INDEX, c.EXTRA
  FROM STATISTICS s
   LEFT OUTER JOIN COLUMNS c ON (s.TABLE_SCHEMA=c.TABLE_SCHEMA AND s.TABLE_NAME=c.TABLE_NAME AND s.COLUMN_NAME=c.COLUMN_NAME)
 WHERE s.TABLE_SCHEMA='test'
 ORDER BY TABLE_NAME, COLUMN_NAME, SEQ_IN_INDEX
+--------------+------------+-------------+--------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | SEQ_IN_INDEX | EXTRA          |
+--------------+------------+-------------+--------------+----------------+
| test         | t1         | one         |            1 | auto_increment |
+--------------+------------+-------------+--------------+----------------+
| test         | t2         | one         |            2 | auto_increment |
| test         | t2         | two         |            1 |                |
+--------------+------------+-------------+--------------+----------------+
| test         | t3         | one         |            3 | auto_increment |
| test         | t3         | three       |            2 |                |
| test         | t3         | two         |            1 |                |
+--------------+------------+-------------+--------------+----------------+
| test         | t4         | one         |            1 | auto_increment |
| test         | t4         | one         |            3 | auto_increment |
| test         | t4         | three       |            2 |                |
| test         | t4         | two         |            1 |                |
+--------------+------------+-------------+--------------+----------------+

　
　
t1とt4は、SEQ_IN_INDEXが 1 のものに auto_increment がついているのでOK。
それ以外の t2 と t3 を出したい、というのが今回の課題。
　
　
ここまでくれば簡単ですね。
一番単純なのは、グルーピングすることかな。

SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.COLUMN_NAME, MIN(s.SEQ_IN_INDEX) sq
  FROM STATISTICS s
   LEFT OUTER JOIN COLUMNS c ON (s.TABLE_SCHEMA=c.TABLE_SCHEMA AND s.TABLE_NAME=c.TABLE_NAME AND s.COLUMN_NAME=c.COLUMN_NAME)
 WHERE s.TABLE_SCHEMA='test'
   AND c.EXTRA LIKE '%auto_incre%'
 GROUP BY s.TABLE_SCHEMA, s.TABLE_NAME, s.COLUMN_NAME
 HAVING sq>1
 
+--------------+------------+-------------+------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | sq   |
+--------------+------------+-------------+------+
| test         | t2         | one         |    2 |
| test         | t3         | one         |    3 |
+--------------+------------+-------------+------+

　
　
　WINDOW関数を使ってシャレてみたけど、あんまり賢いクエリの書き方のような気はしない。。上記 GROUP BY が最良かも。

SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.COLUMN_NAME, s.SEQ_IN_INDEX sq
  ,MIN(s.SEQ_IN_INDEX) OVER (PARTITION BY s.TABLE_SCHEMA, s.TABLE_NAME, s.COLUMN_NAME) x
 , CASE WHEN (MIN(s.SEQ_IN_INDEX) OVER (PARTITION BY s.TABLE_SCHEMA, s.TABLE_NAME, s.COLUMN_NAME))<>1 THEN "←こいつです！" ELSE "" END kit
  FROM STATISTICS s
   LEFT OUTER JOIN COLUMNS c ON (s.TABLE_SCHEMA=c.TABLE_SCHEMA AND s.TABLE_NAME=c.TABLE_NAME AND s.COLUMN_NAME=c.COLUMN_NAME)
 WHERE s.TABLE_SCHEMA='test'
   AND c.EXTRA LIKE '%auto_incre%'

+--------------+------------+-------------+----+------+----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | sq | x    | kit            |
+--------------+------------+-------------+----+------+----------------+
| test         | t1         | one         |  1 |    1 |                |
| test         | t2         | one         |  2 |    2 | ←こいつです！ |
| test         | t3         | one         |  3 |    3 | ←こいつです！ |
| test         | t4         | one         |  1 |    1 |                |
| test         | t4         | one         |  3 |    1 |                |
+--------------+------------+-------------+----+------+----------------+