MySQL User-defined Variables

By /
User Defined Variables In Mysql

Variables are a very important part of any programming language. Although MySQL is not a programming language, variables are still its vital domain. In this tutorial, we will learn about user-defined variables which are also known as session variables in MySQL.

This is going to be a detailed guide on MySQL user-defined variables where we will learn what the user-defined variable is and how to declare and use them with some examples. So, without wasting any time, let’s get started!

Also read: Variables in MySQL Stored Procedures – Easy Implementation

Introduction to MySQL Variables

If you know any programing language, you must know that there are multiple types of variables available which are meant to act differently. Similarly, MySQL provides us with different types of variables that you can use for your queries, modifying server configuration, and writing complex business logic.

There are three types of variables available in MySQL as below-

  1. User-Defined variables or Session Variables
  2. Local Variables
  3. System variables

In this tutorial, we will learn about user-defined variables.

What is a User-Defined Variable?

In MySQL, user-defined variables are also known as session variables because these variables work as long as the session is active. As soon as the session ends, these variables no longer work.

User-defined variables can be declared and initialized using either the SET or SELECT statement. Remember that, values are assigned to the user-defined variables at the time of declaration and don’t need the DECLARE statement to declare them. We will understand it using the example below.

Following are the important points you must know about user-defined / session variables.

  • User-defined variables start with the @ sign and must be used with the same sign throughout the session.
  • User-defined variables can not be declared using the DECLARE statement. You can directly assign values to them while declaring.
  • User-defined variables can not be seen by other MySQL users.
  • User-defined variables don’t need datatype to be specified while assigning value.
  • User-defined variables are case-insensitive.
  • User-defined variables can have a maximum length of 64 characters.

How to Declare and Use User-Defined Variables?

As mentioned earlier, user-defined variables can be declared and initialized using the SET and SELECT statements. Let’s see with examples below-

Declare a user-defined variable using the SET statement-

SET @website = "mysqlcode.com";
SET @website1 := "mysqlcode.com1";Code language: SQL (Structured Query Language) (sql)
Declare Variables Using SET
Declare Variables Using SET

Using the SET statement, both = and := can be used to assign value to the variable.

Declare a user-defined variable using the SELECT statement-

Note that, you must use the := sign to assign the value to the variable when the SELECT statement is used.

SELECT @name := "Saitama";Code language: SQL (Structured Query Language) (sql)
Declare Variables Using SELECT
Declare Variables Using SELECT

The above statement will modify the value of the variable “@name” from “Saitama” to “jenos”.

How to Display User-defined Variables?

You can display user-defined variables using the SELECT statement as shown below-

SELECT @website;
SELECT @name;Code language: SQL (Structured Query Language) (sql)
Display User Defined Variables
Display User-Defined Variables

As you can see in the image above, we get the values that we specified while declaration.

If you try to display the undeclared variable using the SELECT statement, you will get NULL in the result. Check the example below-

SELECT @undeclared;Code language: SQL (Structured Query Language) (sql)
Display Undeclared Variable
Display Undeclared Variable

The “undeclared” variable is not declared and initialized anywhere. So, it gives you NULL value when displayed.

How to Modify Value of User-Defined Variable

Once you declare and initialize the user-defined variable, you are free to modify its value throughout the session. To modify the value of the user-defined variable, the same syntax is used.

In the above example, we declared the variable @name and assigned the value “Saitama” to it. Let’s change it now.

SET @name="jenos"; Code language: SQL (Structured Query Language) (sql)
Modify Variable Value
Modify Variable Value
SELECT @name;Code language: SQL (Structured Query Language) (sql)
Modified Value Of Variable
Modified Value Of Variable

As you can see, the value of the variable “name” is changed.

Assigning Expression Value to User-Defined Variable

You can use any expression that evaluates the proper value and assign it to the user-defined variable. We will understand this using the example-

Example – Display the total number of rows in the table.

To solve the above example, we will use the below table definition and data-

CREATE TABLE demo(
name VARCHAR(50) 
);
INSERT INTO demo VALUES ("Naruto"), ("Steve"), ("Butler"), ("May");Code language: SQL (Structured Query Language) (sql)

Now, we will use the COUNT function to calculate the total number of rows and assign it to the user-defined variable.

SELECT @rows := COUNT(*) FROM demo;Code language: SQL (Structured Query Language) (sql)
Assign Expression Value To Variable
Assign Expression Value To Variable

As you can see, we received the correct output. The session variable “rows” is now initialized to the value 4. You can use it throughout the session or modify it as per your need.

Conclusion

In this tutorial, we learned about the user-defined variable which is also known as the session variable. We went through its declaration and initialization and saw some examples to understand how we can use user-defined variables in MySQL in different ways. I hope you found this tutorial helpful. Don’t forget to share it with your friends!