MySQL Variables

The MySQL can use variables to store and manipulate values within SQL statements and stored procedures. MySQL supports both user-defined and system variables. Here are the basics of using variables in MySQL:

User-Defined Variables:

User-defined variables in MySQL are preceded by the “@” symbol. You can set and retrieve values from these variables in SQL statements.

To set the value of a user-defined variable, use the SET statement:

SET @my_variable = 'Hello, World!';

To retrieve the value of a user-defined variable, you can use it in SQL statements:

SELECT @my_variable;

System Variables:

MySQL has many system variables that control the behavior of the server. You can read the value of these variables, but they are typically read-only. To read the value of a system variable, use the SELECT @@ syntax:

SELECT @@global.max_connections; -- Read global system variable
SELECT @@session.sql_mode; -- Read session system variable

Local Variables in Stored Procedures:

You can declare and use local variables within stored procedures. These variables are only accessible within the scope of the stored procedure:

DELIMITER //
CREATE PROCEDURE my_procedure()
BEGIN
  DECLARE local_variable INT;
  SET local_variable = 42;
  -- Use local_variable within the procedure
END;
//
DELIMITER ;

User-Defined Functions:

You can use user-defined functions in MySQL to create reusable logic. Functions can have parameters and return values, making them useful for calculations or data transformations.

DELIMITER //
CREATE FUNCTION add_two_numbers(a INT, b INT)
RETURNS INT
BEGIN
  RETURN a + b;
END;
//
DELIMITER ;

-- Usage
SELECT add_two_numbers(5, 7);

Session Variables:

Session variables are similar to user-defined variables but are specific to a MySQL session. They are not shared across multiple sessions. Session variables can be set and read using the SET and SELECT statements with the SESSION keyword:

SET SESSION my_session_variable = 'Session data';
SELECT SESSION my_session_variable;

Variables can be very useful in dynamic SQL statements, stored procedures, and functions. They allow you to store intermediate results, pass values between SQL statements, and control the flow of your queries and procedures. When using variables, it’s essential to choose meaningful names to make your code more readable and maintainable.