【MySQL】変数を使ったIF文のトリガー

Question

特定の株価を随時更新するMySQLを組みます。

テーブルは

in_ver
time VARCHAR(30),price_s INT(10)

price_ver
time VARCHAR(30),price_s INT(10)

となっています。

【株価の取得とMySQLへのインサートについて】

まずは、市況を拾ってきて、その時間(timestamp)とその時点の価格をin_verテーブルのtimeカラムとprice_sカラムとにインサートします。
timeカラムについては、現在のUNIX_TIMESTAMP(例　1524357960)がVARCHARとして挿入されます。(INTで挿入は不可)
in_verに挿入された timeとprice_s は下記のトリガーを使いprice_verにコピーされます。

【下記のトリガーの役割】
in_verテーブルには時折、現在のUNIX_TIMESTAMP(例　1524357960)ではなく数時間前のTIMESTAMPと価格が挿入されることがあるため、下記のトリガーは「最新価格だけを挿入することを目的とし、それら間違った価格を排除する」役目を負います。

まずは　@newtime=(SELECT cast(NEW.time as unsigned));　をセットして、VARCHARのタイムスタンプをINT形式に修正します。

次に@time60=(SELECT UNIX_TIMESTAMP()-60);をセットし、現在のタイムスタンプの60秒前の時間を取得します。

その上で
IF (@newtime >@time60)　で仕分けし、数時間前の価格を排除した上で、トリガーをスタートさせます。

DELIMITER //
CREATE TRIGGER tri_ver
AFTER INSERT ON in_ver
FOR EACH ROW BEGIN 

SET @newtime=(SELECT cast(NEW.time as unsigned));
SET @time60=(SELECT UNIX_TIMESTAMP()-60);

IF (@newtime >@time60)
THEN
INSERT INTO price_ver SET  

price_ver.time=NEW.time,price_ver.price_s=NEW.price_s;


END IF;
  END;
//
DELIMITER ;

結果
コピーされません。(トリガーがうまく作動していません)

in_verテーブルにはちゃんと挿入されているため、トリガーがエラーになったわけではなく、トリガーの記載方法に問題があるものだと思います。(IF文の書き方、またはSET方法が正しくない)

どこに間違いがございますでしょうか?

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

Answer 1

この方法では解決できそうになかったので、ロードするファイルの先頭に1カラム加え、そこをVARCHAR としたところ、timeはINTにすることができました。

この方法で対応します。

ありがとうございました。

Answer 2

個々のSQLのSELECT文は文法は正しいでしょうか。その前提で

SELECT UNIX_TIMESTAMP()-60

のUNIX＿TIMESTAMP()関数ですが、型はINT型ですか？　

こちらによると

If you want to subtract UNIX_TIMESTAMP() columns, you might want to cast the result to signed integers. See Section 12.10, “Cast Functions and Operators”.
UNIX_TIMESTAMP() カラムを減算する場合は、結果を符号付きの整数にキャストします

とあぶので。SIGNED [INTEGER]ですね。

SET @newtime=(SELECT cast(NEW.time as unsigned));

as unsignedもおかしいと思います。SIGNED [INTEGER]

Comments

[tajix_japan]

ありがとうございます。

SET @newtime=(SELECT cast(NEW.time as signed));

を試しましたがうまくいきませんでした。

NEW.timeが式の中に入っているのが間違いと思い

SET @newtime0=NEW.time;
SET @newtime=(SELECT cast(@newtime0 as signed));
SET @time60=(SELECT UNIX_TIMESTAMP()-60);
IF (@newtime > @time60)

としてみましたが駄目でした。

IF (1 < @time60)

とすれば動きますので、やはり問題は
NEW.time が変数の中に入っているところが原因のようです。

ご教示いただいたことをトライしましたがまだ解決できていません。
ありがとうございました。