MySQL IF文内に複数の代入式をいれたいがエラーがでる

Question

環境はMySQL8.0.21です。
以下のように、if文内で、2つの代入式を実行させたいのですが、エラーとなります。

@y:=@y+1; @x:=1

区切りのセミコロンをａｎｄにしたり全体をカッコにいれてみたりしましたが、うまくいきません。

全体の作業としては既存のplaceテーブルがあり、そのｚカラムの値が直前のレコードから変化している場合は、

@y:=@y+1; @x:=1
を実行、そうではない場合は以下を実行します。

@x:=@x+1

記載していませんが、ここで得られたSELECT文をもとに新テーブルを作成しようと思っています。
以下SQL文です。

set @x:=0;
set @y:=1;
set @z:=null;

select
  if(@z!=place.z, (@y:=@y+1; @x:=1), @x:=@x+1),
  @x,
  @y,
  @z:=place.z
from place;

よろしくお願いいたします。

Comments

[m.ts10806]

どういうデータに対してどういう結果を得たいのでしょうか。

[m.ts10806]

あとMySQLも5系と8では機能的に違ってきます。バージョンをマイナーバージョン含めて提示してください

[orori]

すみません、質問文を修正しました。
バージョンは8.0.21でした。よろしくお願いいたします。

[m.ts10806]

placeテーブルのテーブル定義とサンプルデータ(CREATE TABLE,INSERT)も提示された方がよいかと思います。こちらで検証できませんので。

[sazi]

上手くいかないとは、どのような結果になるのでしょうか？
エラーにはならないのですか？
→タイトルにエラーが出るってありますね。;で複数の実行できないというエラーだと思います。
そもそもifやcaseは結果を返すもので、代入式などを含め、複数を実行できるものでは無いと思いますが。

[orori]

ありがとうございます。上にありますように
@y:=@y+1; @x:=1　のように、複数の代入式だとシンタックスエラーがでますが、
@y:=@y+1だけとすると問題なく動くのです。

[orori]

今回は回答いただき解決してしまいましたが、次回から再現可能な形で質問を行います。ありがとうございました。

Answer 1

MySQLのSelectで変数が使用できるというのに毒されていますね。
変数の参照は良いでしょうけど、SQL内で変数の値を変更を行うのは実行順序などに作用されますから、お勧めされるものではありません。

こで得られたSELECT文をもとに新テーブルを作成しようと思っています。

ということなら、素直に情報を取得して、その後で判定するようにするのをお薦めします。

追記

やりたいことが、place.z毎の連番という事なら、MySQL8以降ならWindow関数が使用できますので、以下の様にできます。

select row_number() over(partition by z order by [何かの順序を決める項目]) as SEQ
from place

Comments

[orori]

Window関数というのがあるんですね。早速学んでつかってみて期待通りの値を得ることができました！ありがとうございました。
変数については、そうなのですね。いままでRailsのタスクで行っていたものをSQLでできるようにしたいと考えておりました。関数などまだ学んでいない部分もあるので頑張ってみます。