MySQLでオートインクリメントを使用せずにグループ毎に連番を設定する方法
2025/06/19
MySQLでオートインクリメントを使用せずにグループ毎で連番を割り振りたかった。具体的には掲示板を作成する案件がありスレッド毎にレスナンバーを表示させたかった。以下にSQL文とPHPから実行させる方法をメモ。
目次
MySQL8系以降は別の方法が便利(2025/06/19追記)
MySQLのバージョンが8系以降の場合は以下の方法を用いる方が楽で便利。
SQL文
phpmyadminやadminerからは以下SQL文でgroup_idごとに連番(no)が割り振られる。
SET @no:=0; SET @group_id:=null; SELECT *, IF(@group_id <> group_id, @no:=1, @no:=@no+1) AS no, @group_id := group_id FROM test_table ORDER BY group_id DESC;
PHPからSQL文を発行する場合は以下になる。
$sql = 'SET @no:=0;'; $row = $mysqli->query($sql); $sql = 'SET @group_id:=null;'; $row = $mysqli->query($sql); $sql = ' SELECT *, IF(@group_id <> group_id, @no:=1, @no:=@no+1) AS no, @group_id := group_id FROM test_table ORDER BY group_id DESC ;
ページングにも対応させたい
掲示板だと数十件ほどの書き込みごとに「次のページへ」等のページングが必要になるが、先述のソースだとページが切り替わるごとに上からレスナンバーが1~とリセットされてしまう。
そのような場合は以下のように変更することでページが切り替わってもレスナンバーが正常に表示されるようになる。
$page = 1; //ページ数 $limit = 20; //1ページの最大表示数 $offset = $page * $limit; $sql = 'SET @no:=' . $offset . ';';
アットマークとかコロンとかは何?
今回作成したSQL文は参考サイトを元にしたが、アットマークとかコロンなど平素では使用しない記述が多数見受けられた。
@(アットマーク)
変数を利用する際に使用する。PHPでいう$マークみたいなものっぽい。
:=(コロン・イコール)
イコールと同じ、もしくは代入の際に使用する。例えば以下だとnoという変数に0を代入するという意味になる。
$sql = 'SET @no:=0;';
IF(@group_id <> group_id, @no:=1, @no:=@no+1) AS noの部分
IF~からASまでの部分は参考演算子とのこと。group_idが等しくない場合はnoは1、同じ場合は+1とする。つまり同じグループであればインクリメントしていくといったもの。その値をnoカラムとして受け取られる(AS no部分)。
参考サイト
関連記事
-
-
MySQLに保存したデータをJSON形式で取得する方法
MySQLに保存したデータをJSON形式で利用したいということがたまにある(Ja ...
-
-
MySQLで全国の市区町村のデータを入手し、抽出する方法
都道府県と市区町村が連携したセレクトメニューを作成したかった。具体的には大阪府を ...
-
-
MySQLでテーブル構造とデータをコピーする方法
MySQLで既にあるテーブルとデータに対して実験を行う必要があった。本番テーブル ...
-
-
さくらインターネットでCronからmysqldumpすると0バイトのファイルが生成される
さくらインターネットのレンタルサーバでmysqldumpした結果をファイルとして ...
-
-
MySQLで特定の値を加算しつつNULL値の場合は0にする方法
いくつかのコードに対して投票を行い、その合計値をスコアとして出したい。ただ、投票 ...