MySQL Variables

MySQL Variables

MySQL provides variables that are used to store values temporarily during a session. These variables can be categorized into two main types:

1. User-Defined Variables: Variables created and used within a session.
2. System Variables: Built-in variables that control the behavior and configuration of the MySQL server.

1. User-Defined Variables

Definition

• User-defined variables are variables created by the user for temporary use within a session.
• These variables are session-specific and are not shared across connections.

Syntax

To define or assign a value to a user-defined variable:

SET @variable_name = value;
SELECT @variable_name := value;

To retrieve the value of a variable:

SELECT @variable_name;

Examples

1. Assigning and Retrieving Values

   SET @user = 'Alice';
   SELECT @user; -- Output: Alice
   

2. Using Variables in Calculations

   SET @a = 10, @b = 20;
   SELECT @a + @b AS Sum; -- Output: 30
   

3. Using Variables in Queries

   SET @threshold = 100;
   SELECT * FROM orders WHERE total_amount > @threshold;
   

2. System Variables

Definition

• System variables are built-in variables that control the configuration and behavior of the MySQL server.
• They can be either global or session-specific:
  • Global variables affect the entire server and all sessions.
  • Session variables affect only the current session.

Syntax

1. To view system variables:

   SHOW VARIABLES;
   SHOW VARIABLES LIKE 'variable_name';
   

2. To set system variables:

   SET GLOBAL variable_name = value;
   SET SESSION variable_name = value;
   

3. To view specific system variables:

   SELECT @@global.variable_name;
   SELECT @@session.variable_name;
   

Examples

1. View All System Variables

   SHOW VARIABLES;
   

2. View a Specific Variable

   SHOW VARIABLES LIKE 'max_connections';
   

3. Set a Session Variable

   SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
   

4. Set a Global Variable

   SET GLOBAL max_connections = 200;
   

5. Retrieve Variable Values

   SELECT @@global.max_connections;
   SELECT @@session.sql_mode;
   

Differences Between User-Defined and System Variables

Feature	User-Defined Variables	System Variables
Scope	Session-specific	Global or session-specific
Persistence	Temporary, lasts for the session	Can be persistent (in config)
Assignment	SET or SELECT	SET SESSION or SET GLOBAL
Default Values	None must be explicitly set	Predefined by MySQL

Use Cases

1. User-Defined Variables

   • Temporary data storage for calculations or intermediate query results.
   • Dynamic queries based on session-specific values.

2. System Variables

   • Adjusting server configuration, e.g., increasing connection limits.
   • Tuning performance settings, e.g., buffer sizes or cache settings.

Conclusion

MySQL variables, whether user-defined or system-level, are versatile tools for session management, query customization, and server configuration. By understanding how to use these variables, you can efficiently manage and optimize your MySQL environment.