MySQL user-defined variable in WHERE clause

Question

I want to know if there is a way to use a user-defined variable in WHERE clause, as in this example:

SELECT id, location, @id := 10 FROM songs WHERE id = @id

This query runs with no errors but doesn't work as expected.

In the real scenario I want to define a variable to get LOCATE() offset of a substring in a field. I use it many times in the SELECT statement. The problem is that I want to show only results that have this offset greater than zero. I know that I can use another LOCATE() in WHERE clause, but why this variable doesn't work in the WHERE clause? — Paulo Freitas, Oct 21 '10 at 03:40

score 31 · Accepted Answer · edited Jul 28 '12 at 03:45

31

Not far from what Mike E. proposed, but one statement:

SELECT id, location FROM songs, ( SELECT @id := 10 ) AS var WHERE id = @id;

I used similar queries to emulate window functions in MySQL. E.g. Row sampling - just an example of using variables in the same statement

edited Jul 28 '12 at 03:45

Paulo Freitas

13,194
14
74
96

answered May 16 '11 at 18:31

Maxym

11,836
3
44
48

1

Subquery in the FROM clause... what a useful trick! Thank you! :) – Paulo Freitas Jul 28 '12 at 03:25
Is there any possible way to check if UDV is not null then fire this Query? case explained in [link](https://dba.stackexchange.com/questions/227972/mariadb-user-define-variable-throws-warning-if-it-is-empty/227975) – Smit Shah Jan 25 '19 at 04:13
if where clause is string, it doesn't work. – Dedi Ananto Oct 18 '22 at 09:46

steampowered · Answer 2 · 2012-08-19T11:32:45.807

14

From the MySQL manual page on User Defined Variables:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.

So you should separate the assignment from the select statement:

SET @id = 10;
SELECT id, location, @id FROM songs WHERE id = @id;

edited Aug 19 '12 at 11:32

answered Aug 17 '12 at 18:02

steampowered

11,809
12
78
98

score 5 · Answer 3 · answered Oct 21 '10 at 03:27

Sure, but I've never seen anyone try to set a variable and use it in the same statement like you are. Try:

SET @id := 10;
SELECT @id := 10 FROM songs WHERE id = @id;

or

SELECT @id := 10 FROM songs;
SELECT @id := 10 FROM songs WHERE id = @id;

I've used both, and they both seem to work for me.

score 2 · Answer 4 · answered Dec 01 '16 at 11:32

2

This worked for me!

SET @identifier = 7;
SELECT * FROM test where identifier = @identifier;

answered Dec 01 '16 at 11:32

Jonas

49
3

this works equally well in MariaDb too, but surprisingly its not really that well documented. this is surprising, considering how useful it is. – edwardsmarkf Jun 21 '22 at 02:41

score 0 · Answer 5 · answered Aug 23 '23 at 08:32

Well, what about this.

Just like @PauloFreitas I needed some user defined variables to improve the readability and reduce operations in some large queries. I ended up with queries like below, just created for demonstration purposes. I define the variables first, then assign them in a nifty IF() statement in the WHERE clause, as that is executed before the SELECT part but is based on the row data. Then I'm able to use them anywhere.

CREATE TABLE `strings` (
  `id` INT UNSIGNED,
  `string` VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

    INSERT INTO `strings` (`id`, `string`) VALUES
(1, "abcde"),
(2, "bcdef"),
(3, "cdefg"),
(4, "defgh"),
(5, "efghi");

-- Declare the variables:
SET @CDpos=-1;
SET @DEpos=-1;

First SELECT Query

Show usage of filling the variables with row data

SELECT
    `id`, 
    `string`,
    @CDpos AS `Position of "cd"`,
    @DEpos AS `Position of "de"`
FROM 
    `strings` 
WHERE 
    `id` = IF(
                (((@CDpos:=LOCATE("cd", `string`)) OR TRUE) 
                AND ((@DEpos:=LOCATE("de", `string`)) OR TRUE))
                , `id`
                , `id`
            );

id	string	Position of "cd"	Position of "de"
1	abcde	3	4
2	bcdef	2	3
3	cdefg	1	2
4	defgh	0	1
5	efghi	0	0

Second SELECT Query

Now also use the variables in the WHERE to filter rows

SELECT
    `id`, 
    `string`,
    @CDpos AS `Position of "cd"`,
    @DEpos AS `Position of "de"`
FROM 
    `strings` 
WHERE 
    `id` = IF(
                (((@CDpos:=LOCATE("cd", `string`)) OR TRUE) 
                AND ((@DEpos:=LOCATE("de", `string`)) OR TRUE))
                , `id`
                , `id`
            )
    AND @CDpos = (@DEpos - 1) -- always true if both strings are present
    AND @CDpos > 0;

id	string	Position of "cd"	Position of "de"
1	abcde	3	4
2	bcdef	2	3
3	cdefg	1	2

View on DB Fiddle

MySQL user-defined variable in WHERE clause

5 Answers5

Linked