MySQLで自動採番&更新 ~ユーザ変数使ってROW_NUMBERっぽく~

OracleやったりMySQLやったり、NoSQLやったり・・・
kintoneやったり・・・

本記事はこんな方におすすめ

・Oracle触った事ある人
・MySQL触ってる人
・最近kintoneやっててSQL忘れてきちゃった人

はじめに

MySQLで、ある特定の条件で抽出したレコードリストに対して、抽出した結果の行番号で更新(付与)したい。
つまり、データリストに対して自動採番を行い、データの更新も一気にやりたい。
となった時にUPDATE文1発でやれるから、楽チンだな。と考えていたのですが、実は頭の中はOracleのROW_NUMBER関数を使っていました。
ですが、今回はMySQL。。。ん？？？ないなROW_NUMBER関数。
OracleやったMySQLやったり他にもやったりしていると、どれで何ができたっけ？と、いろんな知識が入り乱れてきちゃいました。
という事で、今回はMySQLにて、ユーザ変数を利用したROW_NUMBERっぽいUPDATE文を書いたので執筆します。

SQL(2発)

SET @rownum=0;

UPDATE
   exapmle t1,
   (
       SELECT
           id,
           name,
           sex
           @rownum:=@rownum+1 as auto_row_num
       FROM
           exapmle t1
       ORDER BY sex,name
   ) t2
SET t1.auto_row_num = t2.auto_row_num
WHERE t1.id = t2.id;

SQL(1発)

UPDATE
   exapmle t1,
   (
       SELECT
           id,
           name,
           sex
           @rownum:=@rownum+1 as auto_row_num
       FROM
           (SELECT @rownum:=0) dummy,
           exapmle t1
       ORDER BY sex,name
   ) t2
SET t1.auto_row_num = t2.auto_row_num
WHERE t1.id = t2.id;

あら楽チン

解説

SQL(2発)
ユーザ変数を利用します。

1. SET @rownum=0;
　　→ ユーザ変数rownumの初期化
2. SELECT @rownum:=@rownum+1 as auto_row_num ・・・
　　→ ユーザ変数rownumに1インクリメント

SQL1発
上記でユーザ変数を利用する事で採番する事ができたので、さらに1初で完結させます。

1. FROM (SELECT @rownum:=0) dummy,・・・
　　→ 一発SELECGT文を発行した際にユーザ変数の定義と初期化

まとめ

今回、私は「あれ？確かこうやればできたはず・・・」と、イメージした方法がOracleでの方法であり、MySQLだとどうやるんだっけ？
と一瞬困惑しましたが、ベースとなる知識や経験があればすぐに代替策が見つかります。
もし、ベースとなるものが少なくてもこのNoteなんかが引っかかれば幸いです。

最後に

ここ数ヶ月はkintoneを触る事が多く、とても便利で使いやすくて楽しく開発してきましたが、時々SQL文だったら楽チンなのにな・・・
と思うこともありました。
それぞれユースケースによって使いやすさなどはありますが、今回のように特定の条件でデータリストを抽出して、その抽出結果順番を自動採番した結果としてそのままデータリストにSQL文で反映(更新)できるのは、とても楽チンでした！