How to Declare a Variable in MySQL

How to declare a variable in MySQL?

There are mainly three types of variables in MySQL:

User-defined variables (prefixed with @):
You can access any user-defined variable without declaring it or
initializing it. If you refer to a variable that has not been
initialized, it has a value of NULL and a type of string.
```
SELECT @var_any_var_name
```
You can initialize a variable using SET or SELECT statement:
```
SET @start = 1, @finish = 10;    
```
or
```
SELECT @start := 1, @finish := 10;

SELECT * FROM places WHERE place BETWEEN @start AND @finish;
```
User variables can be assigned a value from a limited set of data
types: integer, decimal, floating-point, binary or nonbinary string,
or NULL value.
User-defined variables are session-specific. That is, a user
variable defined by one client cannot be seen or used by other
clients.
They can be used in SELECT queries using Advanced MySQL user variable techniques.
Local Variables (no prefix) :
Local variables needs to be declared using DECLARE before
accessing it.
They can be used as local variables and the input parameters
inside a stored procedure:
```
DELIMITER //

CREATE PROCEDURE sp_test(var1 INT) 
BEGIN   
    DECLARE start  INT unsigned DEFAULT 1;  
    DECLARE finish INT unsigned DEFAULT 10;

    SELECT  var1, start, finish;

    SELECT * FROM places WHERE place BETWEEN start AND finish; 
END; //

DELIMITER ;

CALL sp_test(5);
```
If the DEFAULT clause is missing, the initial value is NULL.
The scope of a local variable is the BEGIN ... END block within
which it is declared.
Server System Variables (prefixed with @@):
The MySQL server maintains many system variables configured to a default value.
They can be of type GLOBAL, SESSION or BOTH.
Global variables affect the overall operation of the server whereas session variables affect its operation for individual client connections.
To see the current values used by a running server, use the SHOW VARIABLES statement or SELECT @@var_name.
```
SHOW VARIABLES LIKE '%wait_timeout%';

SELECT @@sort_buffer_size;
```
They can be set at server startup using options on the command line or in an option file.
Most of them can be changed dynamically while the server is running using SET GLOBAL or SET SESSION:
```
-- Syntax to Set value to a Global variable:
SET GLOBAL sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000;

-- Syntax to Set value to a Session variable:
SET sort_buffer_size=1000000;
SET SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@local.sort_buffer_size=10000;
```

how to declare variable in mysql and set its value to the count result

You have to use session variables

SELECT COUNT(*) FROM grades WHERE statId = 1 INTO @itExists;
SELECT @itExists AS cnt;

How to declare a variable in MySQL

SELECT col1 INTO @variable1 FROM ... LIMIT 1
SELECT col1, col2 INTO @variable1, @variable2 FROM ... LIMIT 1

The number of variables must be the same as the number of columns or
expressions in the select list. In addition, the query must returns
zero or one row.
If the query return no rows, MySQL issues a warning of no data and the
value of the variables remain unchanged.
In case the query returns multiple rows, MySQL issues an error. To
ensure that the query always returns maximum one row, you use the
LIMIT 1 clause to limit the result set to a single row.

Source
https://dev.mysql.com/doc/refman/5.7/en/select-into.html
https://www.mysqltutorial.org/mysql-select-into-variable/

MySql DECLARE variable syntax

You can use @ in MySQL too, to use a user-defined variable.

You can do it as following without using any standard procedure or functions.

set @d='2021-02-01';
select * from tbl_name where createdt> @d;

You don't need to use declare clause here.
You can refer here: Syntax reference

How to declare a variable in MySQL for a normal query?

You can declare a session variable in this way :

SET @myvarname := 'value';

or a local variable in this way :

DECLARE my_variable varchar(30)

also:

DECLARE my_variable varchar(30) DEFAULT 'value'

Declaring and using MySQL varchar variables

This works fine for me using MySQL 5.1.35:

DELIMITER $$

DROP PROCEDURE IF EXISTS `example`.`test` $$
CREATE PROCEDURE `example`.`test` ()
BEGIN

  DECLARE FOO varchar(7);
  DECLARE oldFOO varchar(7);
  SET FOO = '138';
  SET oldFOO = CONCAT('0', FOO);

  update mypermits
     set person = FOO
   where person = oldFOO;

END $$

DELIMITER ;

Table:

DROP TABLE IF EXISTS `example`.`mypermits`;
CREATE TABLE  `example`.`mypermits` (
  `person` varchar(7) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO mypermits VALUES ('0138');

CALL test()

How to declare a variable in SQL script?

You can only use declared variables in stored procedure. In an ordinary SQL script, use user variables, which are names beginning with @. You don't specify a type, they can hold any type of value.

SET @a = 4;