MySQL local variables

Question

I am trying to define and initialize a MySQL variable for a query.

I have the following:

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

I am using MySQL in Netbeans and it tells me I have an error. What/where is my error?

How can I fix this?

Pleurodynia

Answer 1

MySQL has two 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", per the wording in your question, 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;

Hover

Answer 2

Try this:-

 select @countTotal := COUNT(*) from nGrams;

Stirrup

I think you probably intended to use the := assignment operator, rather than the = equality operator? – Hover

Answer 3

Function example:

DROP FUNCTION IF EXISTS test;

DELIMITER $$
CREATE FUNCTION test(in_number INT) RETURNS INT
    BEGIN
        DECLARE countTotal INT;
        SET countTotal = SELECT COUNT(*) FROM nGrams;
    RETURN countTotal + in_number;
END $$
DELIMITER ;

Stickup

You're making a function or procedure? – Stickup

A subquery must always appear within parentheses. – Hover

Answer 4

According to DECLARE Syntax, declare must be inside a begin...end block.

Connecticut

Answer 5

You can set local variables with DECLARE statement as shown below:

DELIMITER $$

CREATE FUNCTION my_func() RETURNS INT 
DETERMINISTIC
BEGIN
  DECLARE v1, v2 INT DEFAULT 2;       -- v1 and v2 are 2.
  DECLARE v3, v4 INT DEFAULT v1 + v2; -- v3 and v4 are 4.
  DECLARE result INT;                 -- result is NULL.
  SELECT v3 + v4 INTO result;         -- result is 8. 
  RETURN result;                      -- 8 is returned.
END$$ 

DELIMITER ;

*Memos:

• You can initialize local variables with DEFAULT.

• The initial values of the local variables without DEFAULT are NULL.

• DECLARE block can be set within the top of BEGIN ... END statement within the stored programs functions, procedures, events and triggers.

• Local variables are deleted after the execution of stored programs.

• Local variables are not influenced by transaction so their changed values cannot be rollbacked. *My question and the answers explain it in detail.

Then, 8 is returned as shown below:

mysql> SELECT my_func();
+-----------+
| my_func() |
+-----------+
|         8 |
+-----------+

Be careful, if DECLARE block is not set within the top of BEGIN ... END statement within stored programs as shown below:

DELIMITER $$

CREATE FUNCTION my_func() RETURNS INT 
DETERMINISTIC
BEGIN
  SELECT 2 + 3;                       -- Because of this
  DECLARE v1, v2 INT DEFAULT 2;       -- Not the top
  DECLARE v3, v4 INT DEFAULT v1 + v2; -- Not the top
  DECLARE result INT;                 -- Not the top
  SELECT v3 + v4 INTO result;
  RETURN result;
END$$ 

DELIMITER ;

Or:

DELIMITER $$

CREATE FUNCTION my_func() RETURNS INT 
DETERMINISTIC
BEGIN
  DECLARE v1, v2 INT DEFAULT 2;
  DECLARE v3, v4 INT DEFAULT v1 + v2;
  SELECT 2 + 3;       -- Because of this
  DECLARE result INT; -- Not the top
  SELECT v3 + v4 INTO result;
  RETURN result;
END$$ 

DELIMITER ;

Then, there is the error below:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...

Brosy

This is a rant about/manual on DECLARE rather than an answer to the specific question asked. There was no need to post it 11 years later. Also please consider not restyling other people's code according to your personal preference. – Jakejakes

@Jakejakes That is also your preference though. Be careful. – Brosy

That this does not answer the question is not my personal preference, it is a fact. This "answer" even specifically avoids mentioning the only one single thing that the OP got wrong - which is correctly mentioned in the accepted answer. This is a blog post that belongs to your personal blog. Posting it here is specifically against the rules, which are not my personal preference here. – Jakejakes

@Jakejakes You can also check other answers of this question with your preference. Go for it!! – Brosy