variable assignment in mysql

How to Declare and Use Variables in MySQL

• MySQL Howtos
• How to Declare and Use Variables in …

User-Defined Variables in MySQL

Initialize user-defined variables, use variables as fields in a select statement, declare local variables in mysql, declare system variables in mysql.

In this tutorial article, we will explain how to declare variables within the SQL code for MySQL databases.

On SQL scripts, you can use variables to store values during the execution of a sequence of commands and use them instead of literals.

MySQL recognizes different types of variables. The first type is the user-defined variables, identified by an @ symbol used as a prefix. In MySQL, you can access user-defined variables without declaring or initializing them previously. If you do so, a NULL value is assigned to the variable when initialized. For example, if you use SELECT with a variable without giving a value to it, as in this case:

MySQL returns a NULL value.

To initialize a user-defined variable, you need to use a SET or SELECT statement. You can initialize many variables at once, separating each assignment statement with a comma, like this:

Once you assign a value to a variable, it will have a type according to the given value. In the previous examples, @FirstVar and @SecondVar are of type int .

The lifespan of a user-defined variable lasts as long as the session is active, and it is invisible to other sessions. Once the session closes, the variable disappears.

There are 5 data types you can assign to a user-defined variable:

• string (binary or nonbinary)
• floating-point
• NULL , which can be associated with any type.

To assign a value to a variable, you can use either symbol = or := . The two following statements have the same effect:

Variables can be part of the field lists of a SELECT statement. You can mix variables and field names when you specify fields in a select, as in this example:

Local variables don’t need the @ prefix in their names, but they must be declared before they can be used. To declare a local variable, you can use the DECLARE statement or use it as a parameter within a STORED PROCEDURE declaration.

When you declare a local variable, optionally, a default value can be assigned to it. If you don’t assign any default value, the variable is initialized with a NULL value.

Each variable lives within a scope, delimited by the BEGIN ... END block that contains its declaration.

The following example illustrates two different ways to use local variables: as a procedure parameter and as a variable internal to the procedure:

In the previous example, the variable itemcount is used as a parameter to pass a value to the procedure. That variable is later used in the SELECT statement to multiply the ListPrice field obtained from the table. The local variable factor is used to store a decimal value used to multiply the resulting price.

There is a third type of variable called system variables used to store values that affect individual client connections ( SESSION variables) or affect the entire server operation ( GLOBAL variables).

System variables are usually set at server startup. To do so, you can use the command line or include the SET statement in an option file. But their values can be modified within an SQL script.

System variables can be identified using a double @ sign as a prefix or using the words GLOBAL or SESSION in the SET statement. Another way to differentiate GLOBAL and SESSION system variables is to use a second prefix: global or session . Here are a few examples of how you can assign values to system variables:

To see the system variables in use within a session or in the server, you can use the SHOW VARIABLES statement. You can add a comparison operator to filter this list if you want to get the value of some specific variables. For example:

• MySQL Basics
• MySQL - Home
• MySQL - Introduction
• MySQL - Features
• MySQL - Versions
• MySQL - Variables
• MySQL - Installation
• MySQL - Administration
• MySQL - PHP Syntax
• MySQL - Node.js Syntax
• MySQL - Java Syntax
• MySQL - Python Syntax
• MySQL - Connection
• MySQL - Workbench
• MySQL Databases
• MySQL - Create Database
• MySQL - Drop Database
• MySQL - Select Database
• MySQL - Show Database
• MySQL - Copy Database
• MySQL - Database Export
• MySQL - Database Import
• MySQL - Database Info
• MySQL Users
• MySQL - Create Users
• MySQL - Drop Users
• MySQL - Show Users
• MySQL - Change Password
• MySQL - Grant Privileges
• MySQL - Show Privileges
• MySQL - Revoke Privileges
• MySQL - Lock User Account
• MySQL - Unlock User Account
• MySQL Tables
• MySQL - Create Tables
• MySQL - Show Tables
• MySQL - Alter Tables
• MySQL - Rename Tables
• MySQL - Clone Tables
• MySQL - Truncate Tables
• MySQL - Temporary Tables
• MySQL - Repair Tables
• MySQL - Describe Tables
• MySQL - Add/Delete Columns
• MySQL - Show Columns
• MySQL - Rename Columns
• MySQL - Table Locking
• MySQL - Drop Tables
• MySQL - Derived Tables
• MySQL Queries
• MySQL - Queries
• MySQL - Constraints
• MySQL - Insert Query
• MySQL - Select Query
• MySQL - Update Query
• MySQL - Delete Query
• MySQL - Replace Query
• MySQL - Insert Ignore
• MySQL - Insert on Duplicate Key Update
• MySQL - Insert Into Select
• MySQL Views
• MySQL - Create Views
• MySQL - Update Views
• MySQL - Drop Views
• MySQL - Rename Views
• MySQL Indexes
• MySQL - Indexes
• MySQL - Create Index
• MySQL - Drop Index
• MySQL - Show Indexes
• MySQL - Unique Index
• MySQL - Clustered Index
• MySQL - Non-Clustered Index
• MySQL Operators and Clauses
• MySQL - Where Clause
• MySQL - Limit Clause
• MySQL - Distinct Clause
• MySQL - Order By Clause
• MySQL - Group By Clause
• MySQL - Having Clause
• MySQL - AND Operator
• MySQL - OR Operator
• MySQL - Like Operator
• MySQL - IN Operator
• MySQL - ANY Operator
• MySQL - EXISTS Operator
• MySQL - NOT Operator
• MySQL - NOT EQUAL Operator
• MySQL - IS NULL Operator
• MySQL - IS NOT NULL Operator
• MySQL - Between Operator
• MySQL - UNION Operator
• MySQL - UNION vs UNION ALL
• MySQL - MINUS Operator
• MySQL - INTERSECT Operator
• MySQL - INTERVAL Operator
• MySQL Joins
• MySQL - Using Joins
• MySQL - Inner Join
• MySQL - Left Join
• MySQL - Right Join
• MySQL - Cross Join
• MySQL - Full Join
• MySQL - Self Join
• MySQL - Delete Join
• MySQL - Update Join
• MySQL - Union vs Join
• MySQL - Unique Key
• MySQL - Primary Key
• MySQL - Foreign Key
• MySQL - Composite Key
• MySQL - Alternate Key
• MySQL Triggers
• MySQL - Triggers
• MySQL - Create Trigger
• MySQL - Show Trigger
• MySQL - Drop Trigger
• MySQL - Before Insert Trigger
• MySQL - After Insert Trigger
• MySQL - Before Update Trigger
• MySQL - After Update Trigger
• MySQL - Before Delete Trigger
• MySQL - After Delete Trigger
• MySQL Data Types
• MySQL - Data Types
• MySQL - VARCHAR
• MySQL - BOOLEAN
• MySQL - ENUM
• MySQL - DECIMAL
• MySQL - INT
• MySQL - FLOAT
• MySQL - BIT
• MySQL - TINYINT
• MySQL - BLOB
• MySQL - SET
• MySQL Regular Expressions
• MySQL - Regular Expressions
• MySQL - RLIKE Operator
• MySQL - NOT LIKE Operator
• MySQL - NOT REGEXP Operator
• MySQL - regexp_instr() Function
• MySQL - regexp_like() Function
• MySQL - regexp_replace() Function
• MySQL - regexp_substr() Function
• MySQL Fulltext Search
• MySQL - Fulltext Search
• MySQL - Natural Language Fulltext Search
• MySQL - Boolean Fulltext Search
• MySQL - Query Expansion Fulltext Search
• MySQL - ngram Fulltext Parser
• MySQL Functions & Operators
• MySQL - Date and Time Functions
• MySQL - Arithmetic Operators
• MySQL - Numeric Functions
• MySQL - String Functions
• MySQL - Aggregate Functions
• MySQL Misc Concepts
• MySQL - NULL Values
• MySQL - Transactions
• MySQL - Using Sequences
• MySQL - Handling Duplicates
• MySQL - SQL Injection
• MySQL - SubQuery
• MySQL - Comments
• MySQL - Check Constraints
• MySQL - Storage Engines
• MySQL - Export Table into CSV File
• MySQL - Import CSV File into Database
• MySQL - UUID
• MySQL - Common Table Expressions
• MySQL - On Delete Cascade
• MySQL - Upsert
• MySQL - Horizontal Partitioning
• MySQL - Vertical Partitioning
• MySQL - Cursor
• MySQL - Stored Functions
• MySQL - Signal
• MySQL - Resignal
• MySQL - Character Set
• MySQL - Collation
• MySQL - Wildcards
• MySQL - Alias
• MySQL - ROLLUP
• MySQL - Today Date
• MySQL - Literals
• MySQL - Stored Procedure
• MySQL - Explain
• MySQL - JSON
• MySQL - Standard Deviation
• MySQL - Find Duplicate Records
• MySQL - Delete Duplicate Records
• MySQL - Select Random Records
• MySQL - Show Processlist
• MySQL - Change Column Type
• MySQL - Reset Auto-Increment
• MySQL - Coalesce() Function
• MySQL Useful Resources
• MySQL - Useful Functions
• MySQL - Statements Reference
• MySQL - Quick Guide
• MySQL - Useful Resources
• MySQL - Discussion
• Selected Reading
• UPSC IAS Exams Notes
• Developer's Best Practices
• Questions and Answers
• Effective Resume Writing
• HR Interview Questions
• Computer Glossary

MySQL - SET Syntax for Variable Assignment

Mysql set statement, setting values to user defined variables, setting values to local variables, setting values to system variables.

The SET statement in MySQL is used to assign values for variables. Using this you can set values to, user-defined variables, variables in procedures and, system variables.

Following is the syntax of the SET statement in MySQL−

You can create variables with in the current MySQL session and use them in the queries. While defining a user d-fined variable you need to place '@' before the variable name.

You can retrieve the values of these variables using the SELECT statement as −

The above query produces the following output −

You can define variables with in stored routines and set values to them using the SET statement.

Following is an example of declaring local variables −

You can call the above procedure as follows −

Following is the output of the above query −

MySQL system variables holds global or session level values, these variables are used to configure various operations. You can set values to these variables dynamically using the SET statement

Let us verify whether loading local data is enabled, if not you can observe the local_infile variable value as −

The above mysql query generates the following output −

Following query enables local_infile option as −

If you verify the value of the variable local_infile again, you can observe the changed value as −

Following is the output of the above mysql query −

Advertisement

• Oracle / PLSQL
• Web Development
• Color Picker
• Programming
• Techie Humor

MySQL Basics

• AND & OR
• COMPARISON OPERATORS
• DELETE LIMIT
• IS NOT NULL
• SELECT LIMIT

MySQL Advanced

• Alter Table
• AUTO_INCREMENT
• Change Password
• Comments in SQL
• Create Table
• Create Table As
• Create User
• Declare Variables
• Find Users Logged In
• Grant/Revoke Privileges
• Primary Key
• Rename User
• Show Grants
• Unique Constraints

MySQL Cursors

• Close Cursor
• Cursor NOT FOUND
• Declare Cursor
• Fetch Cursor
• Open Cursor

MySQL Loops/Conditionals

• IF-THEN-ELSE
• REPEAT Loop

MySQL Triggers

• After Delete Trigger
• After Insert Trigger
• After Update Trigger
• Before Delete Trigger
• Before Insert Trigger
• Before Update Trigger
• Drop Trigger

String Functions

• CHAR_LENGTH
• CHARACTER_LENGTH
• FIND_IN_SET
• SUBSTRING_INDEX

Numeric/Math Functions

Date/time functions.

• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• DATE_FORMAT
• LOCALTIMESTAMP
• MICROSECOND
• PERIOD_DIFF
• SEC_TO_TIME
• STR_TO_DATE
• TIME_FORMAT
• TIME_TO_SEC

Advanced Functions

• CONNECTION_ID
• CURRENT_USER
• LAST_INSERT_ID
• SESSION_USER
• SYSTEM_USER

Encryption Functions

• OLD_PASSWORD

MySQL: Declaring Variables

This MySQL tutorial explains how to declare variables in MySQL with syntax and examples.

What is a variable in MySQL?

In MySQL, a variable allows a programmer to store data temporarily during the execution of code.

The syntax to declare a variable in MySQL is:

Parameters or Arguments

Example - declaring a variable.

Below is an example of how to declare a variable in MySQL called vSite .

This example would declare a variable called vSite as a VARCHAR(40) data type.

You can then later set or change the value of the vSite variable, as follows:

This SET statement would set the vSite variable to a value of 'TechOnTheNet.com'.

Example - Declaring a variable with an initial value (not a constant)

Below is an example of how to declare a variable in MySQL and give it an initial value. This is different from a constant in that the variable's value can be changed later.

This would declare a variable called vSite as a VARCHAR(40) data type and assign an initial value of 'TechOnTheNet.com'.

You could later change the value of the vSite variable, as follows:

This SET statement would change the vSite variable from a value of 'TechOnTheNet.com' to a value of 'CheckYourMath.com'.

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy .

Copyright © 2003-2024 TechOnTheNet.com. All rights reserved.

How to Define and Use Variables in MySQL 8

Introduction.

MySQL, the widely-used open-source relational database management system, offers the flexibility to use variables. Variables in MySQL can take various forms, each with its own scope and use case; they can be user-defined variables, local variables, or system variables. User-defined variables can be set at a session level. Local variables exist within stored programs and are procedural variables. System variables configure MySQL server operation and affect its behavior globally or per session.

In this tutorial, we’ll explore how to define and use these different types of variables in MySQL 8, delving into user-defined variables, system variables, and declaring local variables in stored procedures.

User-Defined Variables

User-defined variables can be created as needed during a session, and must be prefixed with @ . Here’s how to set and use them:

The output will display ‘Foo’, showing the contents of the variable.

You can perform operations on user-defined variables and use them in more complex queries:

This will output 30 .

User-defined variables maintain their value within the session and can be used in subsequent queries:

This will output ‘John Doe’.

System Variables

System variables hold the MySQL system’s configuration settings. They can be session variables, visible and applying only to the current session, or global variables, affecting the operation of the server for all sessions. When modifying a global system variable, remember that it may not affect running sessions, but it will apply to any connections made after the change.

To view system variables, use:

To filter the results, a LIKE clause can be used:

To set a session variable:

And to set a global system variable:

It’s also possible to use the @@ prefix to denotate system variables:

Which will return 1 or 0 indicating whether autocommit is enabled or not.

Local Variables in Stored Procedures

In stored procedures, local variables provide a method of storing data that can only be accessed within the procedure itself. They allow for the processing of data and can be very powerful within a database application.

To declare a local variable:

In this example, a local variable customerName is declared within a stored procedure and used to output the customer’s name.

Local variables are also highly effective in control-of-flow statements like loops and conditionals within procedures.

Local variable salesTotal is used here in an IF conditional to provide simple feedback about sales performance.

Understanding and effectively utilizing variables in MySQL 8 can optimize the interactivity and dynamic configuration of your database. This guide aims to assist you in mastering the use of different types of variables, paving the way for advanced database management and optimization.

Next Article: How to install/update MySQL on Windows

Previous Article: MySQL 8: How to select all columns except password column

Series: MySQL Tutorials: From Basic to Advanced

Related Articles

• How to implement cursor-based pagination in MySQL (3 examples)
• MySQL: How to reset the AUTO_INCREMENT value of a table
• MySQL: How to add a calculated column to SELECT query
• MySQL: Eliminate orphan rows in one-to-many relationships
• MySQL: Using R-Tree Indexes for Spatial Data Types
• How to Create Custom Collations in MySQL
• Using Hash Indexes in MySQL: A Practical Guide
• Understanding Full-Text Indexes in MySQL
• Partial Indexes in MySQL: A Practical Guide
• MySQL: How to Remove FOREIGN KEY Constraints
• Using ENUM in MySQL 8: A Practical Guide (with Examples)
• MySQL: Creating a Fixed-Size Table by Using Triggers

Search tutorials, examples, and resources

• PHP programming
• Symfony & Doctrine
• Laravel & Eloquent
• Tailwind CSS
• Sequelize.js
• Mongoose.js

Home » MySQL Stored Procedures » MySQL Stored Procedure Variables

MySQL Stored Procedure Variables

Summary : in this tutorial, you will learn about MySQL stored procedure’s variables including how to declare and use them.

A variable is a named data object whose value can change during the execution of a stored procedure .

Typically, you use variables to hold immediate results. These variables are local to the stored procedure.

Before using a variable, you need to declare it.

Declaring variables

To declare a variable inside a stored procedure, you use the DECLARE  statement as follows:

In this syntax:

• First, specify the name of the variable after the DECLARE keyword. Ensure the variable name adheres to MySQL table column naming rules.
• Second, define the data type and length of the variable. Variables can have any MySQL data type , such as INT , VARCHAR , and  DATETIME .
• Third, assign a default value to the variable using the DEFAULT option. If you declare a variable without specifying a default value, its default value is NULL .

The following example declares a variable named totalSale with the data type DEC(10,2) and default value of 0.0 :

MySQL allows you to declare two or more variables that share the same data type using a single DECLARE statement.

For example, the following example declares two integer variables totalQty and  stockCount , and sets their default values to zero.

After declaring a variable, you can start using it.

As of MySQL 8.0.34, it is not possible to declare multiple variables with different data types using a single DECLARE statement.

For example, the following declaration will cause a syntax error:

To fix the error, you need to use multiple DECLARE statements as follows:

Assigning variables

To assign a variable a value, you use the SET statement:

For example:

The value of the total variable is 10  after the assignment.

In addition to the SET  statement, you can use the  SELECT INTO statement to assign the result of a query to a variable as shown in the following example:

In this example:

• First, declare a variable named productCount and initialize its value to 0 .
• Then, use the SELECT INTO statement to assign the productCount variable the number of products selected from the products table.

Variable scopes

A variable has its own scope, which determines its lifetime. If you declare a variable inside a stored procedure, it will be out of scope when the END statement of the stored procedure is reached.

When you declare a variable inside the BEGIN...END block, it goes out of scope once the END is reached.

MySQL allows you to declare two or more variables that share the same name in different scopes because a variable is only effective within its scope.

However, declaring variables with the same name in different scopes is not considered good programming practice.

A variable whose name begins with the @ sign is a session variable , available and accessible until the session ends.

MySQL Stored Procedure Variable Example

The following example illustrates how to declare and use a variable in a stored procedure:

How it works.

First, declare a variable totalOrder with a default value of zero. This variable will store the number of orders from the orders table.

Second, use the SELECT INTO   statement to assign the variable totalOrder the number of orders selected from the orders table:

Third, select the value of the variable totalOrder .

Note that you will learn how to use variables practically in the subsequent tutorials. The example in this tutorial serves as an illustration to help you understand the concept.

This statement calls the stored procedure GetTotalOrder() :

Here is the output:

• Use variables to hold immediate results in a stored procedure.
• The scope of a variable determines the variable’s lifetime.

MySQL Cookbook by

Get full access to MySQL Cookbook and 60K+ other titles, with a free 10-day trial of O'Reilly.

There are also live events, courses curated by job role, and more.

Using SQL Variables in Queries

You want to save a value from a query so you can refer to it in a subsequent query.

Use a SQL variable to store the value for later use.

As of MySQL 3.23.6, you can assign a value returned by a SELECT statement to a variable, then refer to the variable later in your mysql session. This provides a way to save a result returned from one query, then refer to it later in other queries. The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you’re retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.

A common situation in which SQL variables come in handy is when you need to issue successive queries on multiple tables that are related by a common key value. Suppose you have a customers table with a cust_id column that identifies each customer, and an orders table that also has a cust_id column to indicate which customer each order is associated with. If you have a customer name and you want to delete the customer record as well as all the customer’s orders, you need to determine the proper cust_id value for that customer, then delete records from both the customers and orders tables that match the ID. One way to do this is to first save the ID value in a variable, then refer to the variable in the DELETE statements: [ 5 ]

The preceding SELECT statement assigns a column value to a variable, but variables also can be assigned values from arbitrary expressions. The following statement determines the highest sum of the arms and legs columns in the limbs table and assigns it to the @max_limbs variable:

Another use for a variable is to save the result from LAST_INSERT_ID( ) after creating a new record in a table that has an AUTO_INCREMENT column:

LAST_INSERT_ID( ) returns the value of the new AUTO_INCREMENT value. By saving it in a variable, you can refer to the value several times in subsequent statements, even if you issue other statements that create their own AUTO_INCREMENT values and thus change the value returned by LAST_INSERT_ID( ) . This is discussed further in Chapter 11 .

SQL variables hold single values. If you assign a value to a variable using a statement that returns multiple rows, the value from the last row is used:

If the statement returns no rows, no assignment takes place and the variable retains its previous value. If the variable has not been used previously, that value is NULL :

To set a variable explicitly to a particular value, use a SET statement. SET syntax uses = rather than := to assign the value:

A given variable’s value persists until you assign it another value or until the end of your mysql session, whichever comes first.

Variable names are case sensitive:

SQL variables can be used only where expressions are allowed, not where constants or literal identifiers must be provided. Although it’s tempting to attempt to use variables for such things as table names, it doesn’t work. For example, you might try to generate a temporary table name using a variable as follows, but the result is only an error message:

SQL variables are a MySQL-specific extension, so they will not work with other database engines.

[ 5 ] In MySQL 4, you can use multiple-table DELETE statements to accomplish tasks like this with a single query. See Chapter 12 for examples.

Get MySQL Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Don’t leave empty-handed

Get Mark Richards’s Software Architecture Patterns ebook to better understand how to design components—and how they should interact.

It’s yours, free.

Check it out now on O’Reilly

Dive in for free with a 10-day trial of the O’Reilly learning platform—then explore all the other resources our members count on to build skills and solve problems every day.

• SQL Cheat Sheet
• SQL Interview Questions
• MySQL Interview Questions
• PL/SQL Interview Questions
• Learn SQL and Database

User-Defined Variables

User-Defined Variables in are a way to store temporary values that can be used within a session . These variables are unique to a session and are not persistent across multiple sessions. They can be very useful for storing intermediate results or values that you want to reuse within a single session.

MySQL also supports the concept of User-defined variables , which allows passing of a value from one statement to another. A user-defined variable in MySQL is written as @var_name where, var_name is the name of the variable and can consist of alphanumeric characters, ., _, and $.

Key Features of a User-Defined Variable

• A User-Defined Variable is session specific i.e variable defined by one client is not shared to other client and when the session ends these variables are automatically expired.
• These variables are not case-sensitive. So, @mark or @Mark both refer to same value.
• Maximum length of variables can be 64 characters.
• Variable name can include other characters like- {!, #, ^, -, ..} in its name, if they are quoted. For ex- @’var@1′ or @”var^2″ or @`var3`.
• These variables can’t be declared, they are only initialized i.e at time of declaration they should be assigned a value.
• An undeclared variable can also be accessed in a SQL statement but their values is set as NULL .
• These variables can take values from the following set of datatypes- { integer, floating-point, decimal, binary, nonbinary string or NULL value}.

1. Assigning value to a variable using SET command.

Values of these variables can be displayed by referring them in SELECT statement-

2. Accessing a undeclared variable

Here, variable @var3 is undeclared so its default value is NULL.

3. Assigning value to a variable without using SET .

In the above example- the variable @var3 should be assigned value using only := not = , the latter is treated as comparison in non- SET statement. Like-

How these variables are used for storing values, that are used in future.

Consider, the following Student table-

Now, we have to find rank of these students by using user-defined variables.

For this, we initialize two variables- @rank and @prev_mark .

Here, variable @rank is used to store the rank of student and @prev_mark is used to store the marks of previous student marks.

Comparison between marks is made so that in case if two students are having equal marks then increment in @rank variable can be avoided.

Changes in both variables take place after student table is sorted in descending order by “mark” column.

Thus, we get the resulted student table sorted by “marks” column in descending order along with rank of students.

Note: In the above query, take care of order of column in select statement. If “marks” column is written before “rank” column then we don’t get desired output. Because every time @prev_mark is assigned the mark of current student which results evaluation @prev_mark!=mark as false . So, the rank of every student is displayed as non-incremented i.e it will remain as 0.

User-Defined Variables – FAQs

What is an example of a user variable.

A user variable is a variable that you define and use in your SQL session to store temporary values. For example, if you want to store the number 10 in a variable, you can do it like this: SET @myVar = 10; Now, @myVar holds the value 10, and you can use it in your queries: SELECT @myVar; — This will return 10

What is a user-defined variable in SQL?

A user-defined variable in SQL is a variable that you, the user, create and use within a session. These variables are used to store temporary values that can be reused in subsequent SQL statements within the same session. They are not stored permanently in the database. For example: SET @totalSales = 1000; Here, @totalSales is a user-defined variable that stores the value 1000.

Please Login to comment...

Similar reads, improve your coding skills with practice.

What kind of Experience do you want to share?

Multi container apps

Up to this point, you've been working with single container apps. But, now you will add MySQL to the application stack. The following question often arises - "Where will MySQL run? Install it in the same container or run it separately?" In general, each container should do one thing and do it well. The following are a few reasons to run the container separately:

• There's a good chance you'd have to scale APIs and front-ends differently than databases.
• Separate containers let you version and update versions in isolation.
• While you may use a container for the database locally, you may want to use a managed service for the database in production. You don't want to ship your database engine with your app then.
• Running multiple processes will require a process manager (the container only starts one process), which adds complexity to container startup/shutdown.

And there are more reasons. So, like the following diagram, it's best to run your app in multiple containers.

Container networking

Remember that containers, by default, run in isolation and don't know anything about other processes or containers on the same machine. So, how do you allow one container to talk to another? The answer is networking. If you place the two containers on the same network, they can talk to each other.

Start MySQL

There are two ways to put a container on a network:

• Assign the network when starting the container.
• Connect an already running container to a network.

In the following steps, you'll create the network first and then attach the MySQL container at startup.

Create the network.

Start a MySQL container and attach it to the network. You're also going to define a few environment variables that the database will use to initialize the database. To learn more about the MySQL environment variables, see the "Environment Variables" section in the MySQL Docker Hub listing .

In the previous command, you can see the --network-alias flag. In a later section, you'll learn more about this flag.

Tip You'll notice a volume named todo-mysql-data in the above command that is mounted at /var/lib/mysql , which is where MySQL stores its data. However, you never ran a docker volume create command. Docker recognizes you want to use a named volume and creates one automatically for you.

To confirm you have the database up and running, connect to the database and verify that it connects.

When the password prompt comes up, type in secret . In the MySQL shell, list the databases and verify you see the todos database.

You should see output that looks like this:

Exit the MySQL shell to return to the shell on your machine.

You now have a todos database and it's ready for you to use.

Connect to MySQL

Now that you know MySQL is up and running, you can use it. But, how do you use it? If you run another container on the same network, how do you find the container? Remember that each container has its own IP address.

To answer the questions above and better understand container networking, you're going to make use of the nicolaka/netshoot container, which ships with a lot of tools that are useful for troubleshooting or debugging networking issues.

Start a new container using the nicolaka/netshoot image. Make sure to connect it to the same network.

Inside the container, you're going to use the dig command, which is a useful DNS tool. You're going to look up the IP address for the hostname mysql .

You should get output like the following.

In the "ANSWER SECTION", you will see an A record for mysql that resolves to 172.23.0.2 (your IP address will most likely have a different value). While mysql isn't normally a valid hostname, Docker was able to resolve it to the IP address of the container that had that network alias. Remember, you used the --network-alias earlier.

What this means is that your app only simply needs to connect to a host named mysql and it'll talk to the database.

Run your app with MySQL

The todo app supports the setting of a few environment variables to specify MySQL connection settings. They are:

• MYSQL_HOST - the hostname for the running MySQL server
• MYSQL_USER - the username to use for the connection
• MYSQL_PASSWORD - the password to use for the connection
• MYSQL_DB - the database to use once connected

Note While using env vars to set connection settings is generally accepted for development, it's highly discouraged when running applications in production. Diogo Monica, a former lead of security at Docker, wrote a fantastic blog post explaining why. A more secure mechanism is to use the secret support provided by your container orchestration framework. In most cases, these secrets are mounted as files in the running container. You'll see many apps (including the MySQL image and the todo app) also support env vars with a _FILE suffix to point to a file containing the variable. As an example, setting the MYSQL_PASSWORD_FILE var will cause the app to use the contents of the referenced file as the connection password. Docker doesn't do anything to support these env vars. Your app will need to know to look for the variable and get the file contents.

You can now start your dev-ready container.

Specify each of the previous environment variables, as well as connect the container to your app network. Make sure that you are in the getting-started-app directory when you run this command.

In Windows, run this command in PowerShell.

In Windows, run this command in Command Prompt.

If you look at the logs for the container ( docker logs -f <container-id> ), you should see a message similar to the following, which indicates it's using the mysql database.

Open the app in your browser and add a few items to your todo list.

Connect to the mysql database and prove that the items are being written to the database. Remember, the password is secret .

And in the mysql shell, run the following:

Your table will look different because it has your items. But, you should see them stored there.

At this point, you have an application that now stores its data in an external database running in a separate container. You learned a little bit about container networking and service discovery using DNS.

Related information:

• docker CLI reference
• Networking overview

There's a good chance you are starting to feel a little overwhelmed with everything you need to do to start up this application. You have to create a network, start containers, specify all of the environment variables, expose ports, and more. That's a lot to remember and it's certainly making things harder to pass along to someone else.

In the next section, you'll learn about Docker Compose. With Docker Compose, you can share your application stacks in a much easier way and let others spin them up with a single, simple command.

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

MySql set multiple variables from one select

I'm starting to pull my hair out here is something like this even possible?

The above doesn't work but i am trying to figure out how I would accomplish this. My ultimate goal here is to call a select and than call stored proc with data from select. Thanks

• stored-procedures

2 Answers 2

For MySQL, please take a look this example code:

• 1 when i run this through mysql_query() function then it doesn't work –  Kamal Kumar Commented Nov 22, 2016 at 9:50

This worked for me.

Thanks to Zec. The first link helped me understand the correct syntax or at least what is working for me.

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged mysql stored-procedures or ask your own question .

• The Overflow Blog
• Ryan Dahl explains why Deno had to evolve with version 2.0
• From PHP to JavaScript to Kubernetes: how one backend engineer evolved over time
• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Bringing clarity to status tag usage on meta sites
• Feedback requested: How do you use tag hover descriptions for curating and do...
• What does a new user need in a homepage experience on Stack Overflow?

Hot Network Questions

• Can pedestrians and cyclists use the Channel Tunnel?
• Version of Dracula (Bram Stoker)
• Sun rise on Venus from East or West (North as North Eclliptic Pole)
• I am a fifteen-year-old from India planning to fly to Germany alone (without my parents accompanying me) to see my relatives.What documents do I need?
• Can the speed of light inhibit the synchronisation of a power grid?
• Everyone hates this Key Account Manager, but company won’t act
• Is it OK to use the same field in the database to store both a percentage rate and a fixed money fee?
• grep command fails with out-of-memory error
• What (if any) pre-breathes were "attempted" on the ISS, and why?
• Does gluing two points prevent simple connectedness?
• Why would aliens want to invade Earth?
• Highlight shortest path between two clickable graph vertices
• Can a "sharp turn" on a trace with an SMD resistor also present a risk of reflection?
• Home water pressure higher than city water pressure?
• Postdoc supervisor has stopped helping
• One IO to control two LEDs. When one is lit, the other is not
• Why don't we observe protons deflecting in J.J. Thomson's experiment?
• The McDonald's Option
• Book about a colony ship making an unscheduled stop in a star system with no habitable planets
• Has the government of Afghanistan clarified what they mean/intend by the ban on 'images of living beings'?
• Difference between 以外に and 以外は, 他に and 他は
• What sort of impact did the discovery that water could be broken down (via electrolysis) into gas have?
• Reed-Solomon error count if errors cannot be corrected
• Visualizing histogram of data on unit circle?