[mysql] How to declare a variable in MySQL?

How to declare a variable in mysql, so that my second query can use it?

I would like to write something like:

SET start = 1;
SET finish = 10;

SELECT * FROM places WHERE place BETWEEN start AND finish;

For any person using @variable in concat_ws function to get concatenated values, don't forget to reinitialize it with empty value. Otherwise it can use old value for same session.

Set @Ids = '';

select 
  @Ids := concat_ws(',',@Ids,tbl.Id),
  tbl.Col1,
  ...
from mytable tbl;

Use set or select

SET @counter := 100;
SELECT @variable_name := value;

example :

SELECT @price := MAX(product.price)
FROM product 

Different types of variable:

• local variables (which are not prefixed by @) are strongly typed and scoped to the stored program block in which they are declared. Note that, as documented under DECLARE Syntax:

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

• User variables (which are prefixed by @) are loosely typed and scoped to the session. Note that they neither need nor can be declared—just use them directly.

Therefore, if you are defining a stored program and actually do want a "local variable", you will need to drop the @ character and ensure that your DECLARE statement is at the start of your program block. Otherwise, to use a "user variable", drop the DECLARE statement.

Furthermore, you will either need to surround your query in parentheses in order to execute it as a subquery:

SET @countTotal = (SELECT COUNT(*) FROM nGrams);

Or else, you could use SELECT ... INTO:

SELECT COUNT(*) INTO @countTotal FROM nGrams;

SET

SET @var_name = value 

OR

SET @var := value

both operators = and := are accepted

SELECT

SELECT col1, @var_name := col2 from tb_name WHERE "conditon";

if multiple record sets found only the last value in col2 is keep (override);

SELECT col1, col2 INTO @var_name, col3 FROM .....

in this case the result of select is not containing col2 values

Ex both methods used

-- TRIGGER_BEFORE_INSERT --- setting a column value from calculations

...
SELECT count(*) INTO @NR FROM a_table WHERE a_condition;
SET NEW.ord_col =  IFNULL( @NR, 0 ) + 1;
...

SET Value

 declare Regione int;   
 set Regione=(select  id from users
 where id=1) ;
 select Regione ;

• Declare: SET @a = 1;

• Usage: INSERT INTO `t` (`c`) VALUES (@a);