TechBeamersTechBeamers
  • Learn ProgrammingLearn Programming
    • Python Programming
      • Python Basic
      • Python OOP
      • Python Pandas
      • Python PIP
      • Python Advanced
      • Python Selenium
    • Python Examples
    • Selenium Tutorials
      • Selenium with Java
      • Selenium with Python
    • Software Testing Tutorials
    • Java Programming
      • Java Basic
      • Java Flow Control
      • Java OOP
    • C Programming
    • Linux Commands
    • MySQL Commands
    • Agile in Software
    • AngularJS Guides
    • Android Tutorials
  • Interview PrepInterview Prep
    • SQL Interview Questions
    • Testing Interview Q&A
    • Python Interview Q&A
    • Selenium Interview Q&A
    • C Sharp Interview Q&A
    • PHP Interview Questions
    • Java Interview Questions
    • Web Development Q&A
  • Self AssessmentSelf Assessment
    • Python Test
    • Java Online Test
    • Selenium Quiz
    • Testing Quiz
    • HTML CSS Quiz
    • Shell Script Test
    • C/C++ Coding Test
Search
  • Python Multiline String
  • Python Multiline Comment
  • Python Iterate String
  • Python Dictionary
  • Python Lists
  • Python List Contains
  • Page Object Model
  • TestNG Annotations
  • Python Function Quiz
  • Python String Quiz
  • Python OOP Test
  • Java Spring Test
  • Java Collection Quiz
  • JavaScript Skill Test
  • Selenium Skill Test
  • Selenium Python Quiz
  • Shell Scripting Test
  • Latest Python Q&A
  • CSharp Coding Q&A
  • SQL Query Question
  • Top Selenium Q&A
  • Top QA Questions
  • Latest Testing Q&A
  • REST API Questions
  • Linux Interview Q&A
  • Shell Script Questions
© 2024 TechBeamers. All Rights Reserved.
Reading: How to Declare Variables in MySQL
Font ResizerAa
TechBeamersTechBeamers
Font ResizerAa
  • Python
  • SQL
  • C
  • Java
  • Testing
  • Selenium
  • Agile Concepts Simplified
  • Linux
  • MySQL
  • Python Quizzes
  • Java Quiz
  • Testing Quiz
  • Shell Script Quiz
  • WebDev Interview
  • Python Basic
  • Python Examples
  • Python Advanced
  • Python OOP
  • Python Selenium
  • General Tech
Search
  • Programming Tutorials
    • Python Tutorial
    • Python Examples
    • Java Tutorial
    • C Tutorial
    • MySQL Tutorial
    • Selenium Tutorial
    • Testing Tutorial
  • Top Interview Q&A
    • SQL Interview
    • Web Dev Interview
  • Best Coding Quiz
    • Python Quizzes
    • Java Quiz
    • Testing Quiz
    • ShellScript Quiz
Follow US
© 2024 TechBeamers. All Rights Reserved.
MySQL Tutorial

How to Declare Variables in MySQL

Last updated: May 22, 2023 12:52 pm
By Meenakshi Agarwal
Share
6 Min Read
MySQL Declare Variable local, user-defined, system
MySQL Declare Variable local, user-defined, system
SHARE

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.

Contents
1. Declare a User-defined VariableExample2. Local Variable DeclarationExample3. Declare System Variables

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.

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.

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.

You Might Also Like

MySQL vs MongoDB Comparison

Concatenate Strings in an SQL Query With Examples

The Difference between UPSERT & Insert

SQL Programming Test in 2024

SQL Table Creation: The Missing Manual

Meenakshi Agarwal Avatar
By Meenakshi Agarwal
Follow:
Hi, I'm Meenakshi Agarwal. I have a Bachelor's degree in Computer Science and a Master's degree in Computer Applications. After spending over a decade in large MNCs, I gained extensive experience in programming, coding, software development, testing, and automation. Now, I share my knowledge through tutorials, quizzes, and interview questions on Python, Java, Selenium, SQL, and C# on my blog, TechBeamers.com.
Previous Article Java nested class or inner class with examples Nested (Inner) Class Concept in Java
Next Article MySQL FIND_IN_SET function with examples MySQL FIND_IN_SET Function with Simple Examples

Popular Tutorials

SQL Interview Questions List
50 SQL Practice Questions for Good Results in Interview
SQL Interview Nov 01, 2016
Demo Websites You Need to Practice Selenium
7 Sites to Practice Selenium for Free in 2024
Selenium Tutorial Feb 08, 2016
SQL Exercises with Sample Table and Demo Data
SQL Exercises – Complex Queries
SQL Interview May 10, 2020
Java Coding Questions for Software Testers
15 Java Coding Questions for Testers
Selenium Tutorial Jun 17, 2016
30 Quick Python Programming Questions On List, Tuple & Dictionary
30 Python Programming Questions On List, Tuple, and Dictionary
Python Basic Python Tutorials Oct 07, 2016
//
Our tutorials are written by real people who’ve put in the time to research and test thoroughly. Whether you’re a beginner or a pro, our tutorials will guide you through everything you need to learn a programming language.

Top Coding Tips

  • PYTHON TIPS
  • PANDAS TIPSNew
  • DATA ANALYSIS TIPS
  • SELENIUM TIPS
  • C CODING TIPS
  • GDB DEBUG TIPS
  • SQL TIPS & TRICKS

Top Tutorials

  • PYTHON TUTORIAL FOR BEGINNERS
  • SELENIUM WEBDRIVER TUTORIAL
  • SELENIUM PYTHON TUTORIAL
  • SELENIUM DEMO WEBSITESHot
  • TESTNG TUTORIALS FOR BEGINNERS
  • PYTHON MULTITHREADING TUTORIAL
  • JAVA MULTITHREADING TUTORIAL

Sign Up for Our Newsletter

Subscribe to our newsletter to get our newest articles instantly!

Loading
TechBeamersTechBeamers
Follow US
© 2024 TechBeamers. All Rights Reserved.
  • About
  • Contact
  • Disclaimer
  • Privacy Policy
  • Terms of Use
TechBeamers Newsletter - Subscribe for Latest Updates
Join Us!

Subscribe to our newsletter and never miss the latest tech tutorials, quizzes, and tips.

Loading
Zero spam, Unsubscribe at any time.
x
x