勉強したことのメモ

webプログラマが勉強したことのメモ。

MySQLでグループ毎に連番を設定する方法

   

MySQLでオートインクリメントを使用せずにグループ毎で連番を割り振りたかった。具体的には掲示板を作成する案件がありスレッド毎にレスナンバーを表示させたかった。以下に方法をメモ。

 

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部分)。

 

参考サイト

https://qiita.com/toyottoyo/items/813338f4756dee41e49b

https://style.potepan.com/articles/15271.html

 - MySQL, データベース

  関連記事

プリペアドステートメント

個人的に得た内容として、SQL文だけ先にDBに送って、 変数は後で送る。だからS ...

MySQLで1対多のデータ結合と条件検索

MySQLで飲食店舗テーブルと休業日テーブルがあり、1店舗に対して複数行の休業日 ...

MySQLでデータがあれば更新、無ければ挿入

DBにデータがあれば更新、無ければ挿入(新規作成)を 行いたい際に使えるものがあ ...

MySQL/Apacheの再起動

忘れないようにメモ。 ■MySQL service mysqld restart ...

MySQLでグループ化したものを条件で絞る(HAVING)

正規化したテーブルがあってその中には idとtagのカラムがある。 でtagの方 ...

MySQLでWhere句をつけてdumpする

MySQLであるテーブルからWhere句をつけて、絞った結果をダンプさせたかった ...

エックスサーバーでMySQLのサイズに注意

エックスサーバーで自動更新のログをMySQLに保存するようなシステムを動かしてい ...

WordPressでデータベース接続確立エラー

WordPressで「データベース接続確立エラー」というエラー表示がされた。大抵 ...

MySQLで全角半角を区別せずに検索する方法(collate utf8_unicode_ci)

あるシステムでMySQLの検索時に「WHERE test_column coll ...

LEFT JOIN

なんとなくで覚えていたMySQLのLEFT JOINを 最近使うことがあり、上手 ...