MySQLでORDER BY句を使って列に連番（INT）を設定する方法

MySQLでは、ユーザー定義変数と ORDER BY 句を組み合わせることで、指定した順序に基づいて列へ連番（INT値）を更新・設定できます。この記事では、その具体的な構文と実行例をわかりやすく解説します。

基本構文

順序に基づいて列をINT値で更新するには、まず変数を0で初期化し、UPDATE文の中で1ずつ加算しながら代入していきます。

set @yourVariableName=0;
update yourTableName
set yourColumnName=(@yourVariableName:=@yourVariableName+1)
order by yourColumnName ASC;

サンプルテーブルの作成

それでは、実際に動きを確認するためのテーブルを作成しましょう。以下のクエリを実行します。

mysql> create table updateColumnDemo
   -> (
   -> Id int,
   -> OrderCountryName varchar(100),
   -> OrderAmount int
   -> );
Query OK, 0 rows affected (1.76 sec)

レコードの挿入

次に、INSERT文を使ってテーブルにデータを挿入します。

mysql> insert into updateColumnDemo(Id,OrderCountryName) values(10,'US');
Query OK, 1 row affected (0.46 sec)
mysql> insert into updateColumnDemo(Id,OrderCountryName) values(20,'UK');
Query OK, 1 row affected (0.98 sec)
mysql> insert into updateColumnDemo(Id,OrderCountryName) values(30,'AUS');
Query OK, 1 row affected (0.77 sec)
mysql> insert into updateColumnDemo(Id,OrderCountryName) values(40,'France');
Query OK, 1 row affected (1.58 sec)

挿入したデータの確認

SELECT文ですべてのレコードを表示してみましょう。

mysql> select *from updateColumnDemo;

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

+------+------------------+-------------+
| Id   | OrderCountryName | OrderAmount |
+------+------------------+-------------+
| 10   | US               | NULL        |
| 20   | UK               | NULL        |
| 30   | AUS              | NULL        |
| 40   | France           | NULL        |
+------+------------------+-------------+
4 rows in set (1.00 sec)

順序に基づいて列を更新するクエリ

ここからが本題です。変数を初期化し、ORDER BY 句と組み合わせてOrderAmount列に連番を設定します。

mysql> set @sequenceNumber=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update updateColumnDemo
   -> set OrderAmount=(@sequenceNumber:=@sequenceNumber+1)
   -> order by OrderAmount ASC;
Query OK, 4 rows affected (0.25 sec)
Rows matched: 4 Changed: 4 Warnings: 0

更新後のデータ確認

もう一度テーブルの内容を確認してみます。

mysql> select *from updateColumnDemo;

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

+------+------------------+-------------+
| Id   | OrderCountryName | OrderAmount |
+------+------------------+-------------+
| 10   | US               | 1           |
| 20   | UK               | 2           |
| 30   | AUS              | 3           |
| 40   | France           | 4           |
+------+------------------+-------------+
4 rows in set (0.00 sec)

まとめ

このように、ユーザー定義変数を (@variable:=@variable+1) の形式でUPDATE文内に組み込み、ORDER BY 句で並び順を指定することで、任意の順序に基づいた連番を列に簡単に設定できます。ランキングの採番や表示順の管理など、さまざまな場面で活用できる便利なテクニックなので、ぜひ覚えておきましょう。