MySQLでソート順に従って値をインクリメントする方法

MySQLでは、UPDATE文にユーザー定義変数を組み合わせることで、指定したカラムでソートした順序に従って値を段階的にインクリメント（増加）させることができます。この記事では、テーブル作成から更新までの手順をサンプルコード付きでわかりやすく解説します。

サンプルテーブルを作成する

まず、create tableコマンドでテーブルを作成しましょう。

mysql> create table DemoTable
(
    FirstName varchar(20),
    Position int
);
Query OK, 0 rows affected (0.71 sec)

テストデータを挿入する

続いて、insertコマンドを使ってレコードを3件挿入します。

mysql> insert into DemoTable values('Chris',100);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Robert',120);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('David',130);
Query OK, 1 row affected (0.16 sec)

挿入したレコードを確認する

select文でテーブル内の全レコードを表示してみましょう。

mysql> select *from DemoTable;

実行結果は以下の通りです。

+-----------+----------+
| FirstName | Position |
+-----------+----------+
| Chris     | 100      |
| Robert    | 120      |
| David     | 130      |
+-----------+----------+
3 rows in set (0.00 sec)

ソートしながら値をインクリメントするUPDATE文

ここからが本題です。まずユーザー定義変数@myPositionに初期値として100を設定し、その後ORDER BY句でPositionカラムを昇順にソートしながら、変数の値を10ずつ加算してPositionを更新します。

mysql> SET @myPosition := 100;
Query OK, 0 rows affected (0.00 sec)
mysql> update DemoTable set Position = @myPosition:=@myPosition+10 ORDER BY Position;
Query OK, 1 row affected (0.17 sec)
Rows matched: 3 Changed: 1 Warnings: 0

仕組みの解説

このクエリでは、ORDER BY Positionによって行がPositionの昇順で処理され、各行に対して@myPositionが10ずつ加算され、その結果がPositionに代入されます。具体的には以下のような流れになります。

• Chris（元の値100）→ 変数が110になり、Positionは110
• Robert（元の値120）→ 変数が120になり、Positionは120
• David（元の値130）→ 変数が130になり、Positionは130

このため実行メッセージは「Rows matched: 3 Changed: 1」となり、実際に値が変わったのはChrisの行だけです。既存の値に一律で10を足しているのではなく、ソート順に基づいて連番的な値を振り直しているという点に注意してください。初期値や加算幅を変更すれば、任意の開始位置・間隔で番号を振り直すことも可能です。

更新結果の確認

最後に、再度select文を実行して更新結果を確認しましょう。

mysql> select *from DemoTable;

実行結果は以下の通りです。

+-----------+----------+
| FirstName | Position |
+-----------+----------+
| Chris     | 110      |
| Robert    | 120      |
| David     | 130      |
+-----------+----------+
3 rows in set (0.00 sec)

このように、ChrisのPositionだけが100から110に更新されました。なお、式の中で変数へ代入するこの書き方はMySQL 8.0以降では非推奨となっているため、新規開発ではROW_NUMBER()などのウィンドウ関数を使う方法もあわせて検討するとよいでしょう。