スキップしてメイン コンテンツに移動

LIMIT and user variable in MysQL


Problem

I want to use this query in PROCEDURE.

SET @user_variable = FLOOR(RAND()*10);
INSERT INTO  Table_name (columns_name) VALUES (1) FROM Table_name ORDER BY RAND() LIMIT 1 OFFSET @user_variable;

This query want to substitute 1 for the @user_variable 'th line of columns_name, but this query does not work.
A cause is that LIMIT and OFFSET does not user variable.

Solution
SET @user_variable = FLOOR(RAND()*10);
PREPARE SET_STMT FROM 'INSERT INTO  Table_name (columns_name) VALUES (1) FROM Table_name ORDER BY RAND() LIMIT 1 OFFSET ?;';
 EXECUTE SET_STMT USING @user_variable;

Conclusion, if you want to use user variable with LIMIT or OFFSET, use PREPARE STATMENT.

Reference
http://techtipshoge.blogspot.com/2011/10/limit.html











Labels:

コメント

  1. Nanashi2018年8月29日 12:12

    I woud much more appreciate this post if there are more background explanations...

    返信削除
  2. t.taichi.takutori.0214@gmail.com2019年6月10日 17:19

    Sorry, I will work much more harder.

    返信削除

コメントを投稿

このブログの人気の投稿

ヘッセ行列

Introduction English ver 今日は、ヘッセ行列を用いたテイラー展開について書こうと思います。 これは最適化を勉強するにあたって、とても大事になってくるので自分でまとめて残しておくことにしました。とくに、機械学習では最適化を必ず行うため、このブログのタイトルにもマッチした内容だと思います。 . 概要 ヘッセ行列の定義 ベクトルを用いたテイラー展開 関数の最適性 ヘッセ行列の定義 仮定 f は次のような条件を満たす関数です。. f はn次元ベクトルから実数値を出力します。 このベクトルは次のように表せます。 \[x = [x_1,x_2,,,,x_n]\] \(\forall x_i , i \in {1,2,,,n}\), f は二回偏微分可能です。 定義 ヘッセ行列は \(\frac{\partial^2}{\partial x_i \partial x_j}を (i,j)要素に持ちます。\) よってヘッセ行列は次のように表せます。 \[ H(f) = \left( \begin{array}{cccc} \frac{\partial^ 2}{\partial x_1^2} & \frac{\partial^2 f}{\partial x_1 \partial x_2} & &\ldots \frac{\partial^2 f}{\partial x_1 \partial x_n} \\ \frac{\partial^ 2 f}{\partial x_1 \partial x_2} & \frac{\partial^ 2 f}{\partial x_2^ 2} & \ldots & \frac{\partial^2 f}{\partial x_2 \partial x_n} \\ \vdots & \vdots & \ddots & \vdots \\ \frac{\partial^ 2 f}{\partial x_n \partial x_2} & \frac{\partial^ 2 f}{\partial x_n \partial x_2} & \ldo...
コメントを投稿
続きを読む

Plane in two dimention

Introduction 日本語 ver Today, I prove this theorem. Plane in two dimention is expressed following. \[\{x|<x,v> = 0\}\] however, v is orthogonal vector for plane and not zero vector. Proof \[\forall k \in \{x|<x,v> = 0\},\] k is fulfill this form. \[<k,v> = 0\] Now, because k and v in two dimentinal space, each vector express following. \[k = (k_1,k_2)\] \[v = (v_1,v_2)\] Thus, \(<k,v>=k_1v_1 + k_2v_2=0\) Change this equation. \[k_2 = -\frac{v_1}{v_2} k_1\] This equation is plane that slope is \(-\frac{v_1}{v_2}\). Q.E.D
コメントを投稿
続きを読む

Rolle’s theorem

Introduction 日本語 ver This post is written Rolle’s theorem. The mean-value theorem is proved by Rolle’s theorem. I will write Mean-value theorem at a later. I introduce Maximum principle because proving Rolle’s theorem need Maximum principle. Maximum principle It is very easy. f is continuous function on bounded closed interval.\(\implies\)** f have max value.** Proof This proof is difficult. I write this proof in other posts. Maximum Principle Rolle’s theorem f is continuous function on [a,b] and differentiable function on (a,b). \[f(a) = f(b) \implies \exists ~~c ~~s.t~~ f'(c) = 0 , a<c<b\] Proof f(x) is constant function \[\forall c \in (a,b) , f'(c) = 0\] else when \(\exists t ~~s.t~~f(a) < f(t)\), \(\exists c ~~s.t~~ \max f(x) = f(c)\) by Maximum principle I proof \(f'(c)=0\) f is differentiable on \(x = c\) and \(f(c) >= f(c+h)\). Thus \[f'(c) = \lim_{h \rightarrow +0} \frac{f(c+h) - f(c)}{h} \leq 0\] \[f'(c) = \lim...
コメントを投稿
続きを読む