MySQL Variables

 

In MySQL, variables can be used to store and manage values temporarily. These variables can be user-defined or system-defined and can be employed for various purposes, including control flow, data manipulation, and query optimization. Here are some key types of variables in MySQL:

1. **User-Defined Variables:**

   - **Syntax:** User-defined variables are prefixed with the '@' symbol. For example, `@my_variable`.

   - **Usage:** You can use user-defined variables to store and retrieve values within a session. These variables persist until the end of the session or until you explicitly change or unset them.

   ```sql

   -- Example of setting and using a user-defined variable

   SET @my_variable = 42;

   SELECT @my_variable;

   ```

2. **System Variables:**

   - **Syntax:** System variables are predefined by MySQL and often control various server settings and behaviors.

   - **Usage:** System variables can be read or modified to adjust the server's behavior. They are accessed without the '@' symbol.

   ```sql

   -- Example of setting a system variable

   SET global max_connections = 100;

   ```

3. **Session Variables:**

   - **Syntax:** Session variables are similar to system variables but apply only to the current session.

   - **Usage:** Changes to session variables affect only the current session and do not persist beyond that session.

   ```sql

   -- Example of setting a session variable

   SET max_sp_recursion_depth = 10;

   ```

4. **Local Variables:**

   - **Syntax:** Local variables are used in stored procedures and functions. They are declared using the DECLARE keyword.

   - **Usage:** Local variables are specific to the block in which they are declared and are not accessible outside that block.

   ```sql

   -- Example of using a local variable in a stored procedure

   DELIMITER //

   CREATE PROCEDURE example_procedure()

   BEGIN

       DECLARE my_local_variable INT;

       SET my_local_variable = 123;

       -- Further logic using my_local_variable

   END //

   DELIMITER ;

   ```

5. **Session System Variables:**

   - **Syntax:** Session system variables are a subset of system variables. They can be set for the duration of a session but do not require the GLOBAL keyword.

   - **Usage:** Changes to session system variables apply only to the current session.

   ```sql

   -- Example of setting a session system variable

   SET optimizer_switch = 'index_condition_pushdown=on';

   ```

These variables provide flexibility and control within a MySQL session, allowing users to customize the behavior of the server and streamline data manipulation tasks. Always refer to the official MySQL documentation for a comprehensive list of variables and their usage.