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:

SET @myVar = 'Foo';
SELECT @myVar;

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:

SET @a = 10, @b = 20;
SELECT @a + @b;

This will output 30.

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

SET @fullName = CONCAT('John', ' ', 'Doe');
SELECT @fullName;

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:

SHOW VARIABLES;

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

SHOW VARIABLES LIKE 'max_join_size';

To set a session variable:

SET session max_join_size = 1000000;

And to set a global system variable:

SET global max_connections = 200;

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

SELECT @@autocommit;

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:

DELIMITER //
CREATE PROCEDURE GetCustomerData()
BEGIN
    DECLARE customerName VARCHAR(100);
    -- Set the value in the local variable
    SET customerName = 'Jane Doe';
    SELECT customerName;
END //
DELIMITER ;

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.

DELIMITER //
CREATE PROCEDURE CheckThreshold()
BEGIN
    DECLARE salesTotal INT;
    -- Imagine that we've obtained the following sum from a table called sales
    SET salesTotal = (SELECT SUM(amount) FROM sales);
    IF salesTotal > 100000 THEN
        SELECT 'Target achieved!';
    ELSE
        SELECT 'Keep selling!';
    END IF;
END //
DELIMITER ;

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

Conclusion

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.