Close Menu
TechBeamersTechBeamers
    TechBeamersTechBeamers
    • Python
    • Java
    • C
    • SQL
    • MySQL
    • Selenium
    • Testing
    • Agile
    • Linux
    • WebDev
    • Technology
    TechBeamersTechBeamers
    MySQL Tutorial

    How to Declare Variables in MySQL

    By Meenakshi AgarwalUpdated:May 22, 2023No Comments5 Mins Read
    Share
    Facebook Twitter LinkedIn Pinterest Email

    This tutorial describes how to declare a variable (like user-defined, local, and system vars) in MySQL. We’ll tell you the complete syntax and provide simple examples for clarity.

    MySQL puts up the below three ways:

    1. Declare a user-defined variable
    2. Declare a local variable
    3. Declare a system variable

    Let’s start with looking at all of them one by one.

    Declare Variable in MySQL

    There are primarily three types of variables in MySQL. And each has its specific way to provide a declaration.

    1. Declare a User-defined Variable

    In MySQL, we can use the SET statement to declare a variable and also for initialization. After setting the value, it is accessible from anywhere in the script.

    An user-defined variable always begins with the @ sign. See the syntax below:

    mysql> SET @my_var1 = expr1 [, @my_var2 = expr2] ...

    While initializing the variable, we can use either a “=” or “:=” sign for assignment. However, it is better not to mix instead go with one convention only.

    A user variable name is an alpha-numeric and can have characters like (., _, $). We can even have a hyphen (-) if we enclose the name in quotes, for example – @”my-var1″.

    An alternative way to declare variables is by using the SELECT statement.

    mysql> SELECT @my_var1, @my_var2, @my_var3;

    Since we’ve not assigned any value, so the variables will assume NULL values. After running the above statement, the output is:

    +----------+----------+----------+
    | @my_var1 | @my_var2 | @my_var3 |
    +----------+----------+----------+
    |   NULL   |   NULL   |   NULL   |
    +----------+----------+----------+

    We can write a user variable name independent of its case. It means in uppercase or lowercase or a combination.

    See also  MySQL Date and Date Functions

    But, the variables have a limited length and can’t go beyond 64 characters. Also, we can store the values of the following types:

    Data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value

    Example

    You can check out how we declare a variable using the SET statement. Also, we used the SELECT command to print the value.

    SET @web_site = 'TechBeamers.com';
    SELECT @web_site;
    
    SET @"web-site" = 'TechBeamers.com';
    SELECT @"web-site";

    After executing the above commands, the result is:

    1	MySQL Workbench
    TechBeamers.com
    TechBeamers.com

    Find out another example:

    create table Test(id integer, title varchar(100));
    insert into Test(id, title) values(1, "MySQL Workbench");
    select * from Test;
    -- Your code here!
    SET @countTotal = (SELECT COUNT(*) FROM Test);
    SELECT "@countTotal =", @countTotal;

    After running the above statements, you get this result:

    1	MySQL Workbench
    @countTotal =	1

    2. Local Variable Declaration

    We define local variables in a program like stored procedures. MySQL provides the DECLARE statement to specify such a variable.

    Also, we can combine it with the DEFAULT clause to assign some initial value. Otherwise, a local variable is NULL.

    You can apply the following syntax to set up a local variable:

    DECLARE my_var1 [, my_var2] ... type [DEFAULT value]

    We should declare them before the cursor as MySQL treats them like stored proc parameters. Moreover, local variables are also independent of the case. Also, they follow the same naming rules as the user-defined ones do.

    Note:- You can use DECLARE only inside a BEGIN … END block statement.

    After reading the above explanation, you can run the below example.

    Example

    In this example, you see a stored procedure which performs addition operation. It also makes use of four local variables.

    DELIMITER // ;
    Create Procedure MySQL_Test_Proc()
        BEGIN
            DECLARE L INT DEFAULT 99;
            DECLARE M INT;
            DECLARE N INT;
            DECLARE T INT;
            SET M = 75;
            SET N = 84;
            SET T = L + M + N;
            SELECT L, M, N, T;
        END //
    DELIMITER ; //
    CALL MySQL_Test_Proc();

    After running this example, the result comes as:

    1	MySQL Workbench
    99	75	84	258

    3. Declare System Variables

    Let’s now learn to declare a MySQL system variable.

    See also  MySQL ABS Function with Simple Examples

    The MySQL server provides a bunch of system variables and sets them to a default value. They are GLOBAL, SESSION, or MIX types.

    Global vars – These persist during the lifecycle of the server.

    Session vars – These remain active only for particular client sessions.

    Moreover, we can observe the current status of a running server. The SHOW VARIABLES command or SELECT @@var_name does the needful.

    Here is the example to fetch variables of the running MySQL instance.

    SHOW VARIABLES LIKE '%wait_timeout%';
    SELECT @@sort_buffer_size;

    After running these, the result comes as:

    1	MySQL Workbench
    innodb_lock_wait_timeout	50
    lock_wait_timeout	31536000
    wait_timeout	28800
    262144

    However, you can even set the system variables. See the examples given below:

    -- Syntax to Declare/Set a Global variable
    SET GLOBAL sort_buffer_size = 500000;
    SET @@global.sort_buffer_size = 500000;
    
    -- Syntax to Declare/Set a Session variable
    SET sort_buffer_size = 500000;
    SET SESSION sort_buffer_size = 500000;
    SET @@sort_buffer_size = 500000;
    SET @@local.sort_buffer_size = 5000;

    We hope that after reading this tutorial, you should feel comfortable in concepts like “Declare Variable in MySQL.” However, you may practice more with examples to gain confidence.

    Also, if you aspire to be a DBA or a test engineer, then you should read our step by step MySQL tutorial to learn SQL from depth.

    Previous ArticleMySQL Tutorial for SQL Commands
    Next Article MySQL FIND_IN_SET Function with Simple Examples
    Meenakshi Agarwal

    I'm Meenakshi Agarwal, founder of TechBeamers.com, with 10+ years of experience in Software development, testing, and automation. Proficient in Python, Java, Selenium, SQL, & C-Sharp, I create tutorials, quizzes, exercises and interview questions on diverse tech topics. Follow my tutorials for valuable insights!

    Add A Comment

    Leave A Reply Cancel Reply

    Latest Posts
    • Create a New User in MySQL with Password
    • Grant Privileges on a Database in MySQL
    • Install MySQL on Ubuntu and Debian OS
    • MySQL ABS Function with Simple Examples
    • MySQL Aggregate Functions
    • MySQL CONCAT to Concatenate Strings
    • MySQL CURRENT_TIMESTAMP() Function

    Subscribe to Updates

    Get the latest tutorials from TechBeamers.

    Loading
    • About
    • Contact
    • Disclaimer
    • Privacy Policy
    • Terms of Use
    © 2023 TechBeamers. All Rights Reserved.

    Type above and press Enter to search. Press Esc to cancel.