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 que开发者_Python百科ry runs with no errors but doesn't work as expected.

---

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

---

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;

---

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.

---

This worked for me!

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