CODEWITHSUNDEEP
mysqlwhere-clause
Paulo Freitas
Paulo Freitas

Reputation: 13649

MySQL user-defined variable in WHERE clause

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.

Upvotes: 26

Views: 50503

Answers (5)

Piemol
Piemol

Reputation: 906

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

Upvotes: 0

Jonas
Jonas

Reputation: 59

This worked for me!

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

Upvotes: 3

steampowered
steampowered

Reputation: 12062

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;

Upvotes: 14

Maxym
Maxym

Reputation: 11906

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

Upvotes: 32

Mike E.
Mike E.

Reputation: 461

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.

Upvotes: 5

Related Questions