- Published on
MySQLのUPDATEで並び順を一括更新する方法
- Authors
- Name
- MASAYA IWATA
- @iwaking
MySQL において、ソート用フィールドを持つテーブルを一括で並び替える方法です。
まずは結論から。
SET @sortnumber = 0;
UPDATE `table_name` SET `sort_id` = (@sortnumber := @sortnumber + 1)
ORDER BY `id` ASC;
たとえば下記のようなソート用のフィールドを持たないテーブルにおいて、region 内で並び替えをしたい場合などに有効ですね。
# prefectures テーブル
+----+--------+--------------+
| id | region | name |
+----+--------+--------------+
| 1 | 関東 | 茨城県 |
| 2 | 関東 | 栃木県 |
| 3 | 関東 | 群馬県 |
| 4 | 関東 | 埼玉県 |
| 5 | 関東 | 東京都 |
| 6 | 関東 | 千葉県 |
| 7 | 関東 | 神奈川県 |
| 8 | 関西 | 滋賀県 |
| 9 | 関西 | 京都府 |
| 10 | 関西 | 大阪府 |
| 11 | 関西 | 兵庫県 |
| 12 | 関西 | 奈良県 |
| 13 | 関西 | 和歌山県 |
+----+--------+--------------+
まずは sort_id フィールドを追加します。
ALTER TABLE `prefectures` ADD `sort_id` INT NOT NULL DEFAULT 0 AFTER `id`;
+----+-------------+--------+----------+
| id | sort_id | region | name |
+----+-------------+--------+----------+
| 1 | 0 | 関東 | 茨城県 |
| 2 | 0 | 関東 | 栃木県 |
| 3 | 0 | 関東 | 群馬県 |
| 4 | 0 | 関東 | 埼玉県 |
| 5 | 0 | 関東 | 東京都 |
| 6 | 0 | 関東 | 千葉県 |
| 7 | 0 | 関東 | 神奈川県 |
| 8 | 0 | 関西 | 滋賀県 |
| 9 | 0 | 関西 | 京都府 |
| 10 | 0 | 関西 | 大阪府 |
| 11 | 0 | 関西 | 兵庫県 |
| 12 | 0 | 関西 | 奈良県 |
| 13 | 0 | 関西 | 和歌山県 |
+----+-------------+--------+---------+
ここで sort_id を SQL で一括更新します。
SET @sortnumber = 0;
UPDATE `prefectures` SET `sort_id` = (@sortnumber := @sortnumber + 10)
WHERE `region` = "関東"
ORDER BY `region` ASC, `id` ASC;
こうなります。この後、手動で数字を変えるなりして並び替えれば良いでしょう。 細かいテクニックですが手動で変更しやすくするためにソートの数字は 10 毎などにすることはよくありますね。
+----+---------+--------+--------------+
| id | sort_id | region | name |
+----+---------+--------+--------------+
| 1 | 10 | 関東 | 茨城県 |
| 2 | 20 | 関東 | 栃木県 |
| 3 | 30 | 関東 | 群馬県 |
| 4 | 40 | 関東 | 埼玉県 |
| 5 | 50 | 関東 | 東京都 |
| 6 | 60 | 関東 | 千葉県 |
| 7 | 70 | 関東 | 神奈川県 |
| 8 | 0 | 関西 | 滋賀県 |
| 9 | 0 | 関西 | 京都府 |
| 10 | 0 | 関西 | 大阪府 |
| 11 | 0 | 関西 | 兵庫県 |
| 12 | 0 | 関西 | 奈良県 |
| 13 | 0 | 関西 | 和歌山県 |
+----+---------+--------+--------------+