ข้ามไปยังเนื้อหาหลัก

MySQL SET Statement

The `SET` statement in MySQL is used to assign values to variables or modify system variables during a session. It is essential for configuring session-specific settings or temporary data storage.

Usage

The `SET` statement is used when you need to change the value of a system variable or define user variables for use within a session. It is particularly useful for customizing environment settings or managing temporary data.

SET variable_name = value;

In this syntax, `SET variable_name = value` assigns the specified `value` to the `variable_name`.

System variables, which manage server operation parameters, differ from user variables that hold temporary data. Use `SET` to modify system variables for the session, and `SET GLOBAL` for changes affecting all sessions.

Examples

1. Assigning a User Variable

SET @counter = 10;

This example assigns the value `10` to the user-defined variable `@counter`, which can be used later in the session. User-defined variables are case-sensitive and must begin with an `@` symbol. They persist only for the duration of the session.

2. Changing a System Variable

SET sql_mode = 'STRICT_TRANS_TABLES';

Here, the `SET` statement modifies the `sql_mode` system variable to enforce strict SQL standards for the current session.

3. Multiple Assignments

SET @x = 5, @y = @x + 1, @z = @y * 2;

In this example, multiple variables are assigned in a single `SET` statement, demonstrating sequential evaluations.

Tips and Best Practices

  • Initialize variables clearly. Use `SET` to initialize variables with explicit values to avoid unexpected behavior.
  • Use session variables cautiously. System variable changes with `SET` apply only to the current session unless modified globally with `SET GLOBAL`.
  • Chain assignments wisely. When making multiple assignments, be mindful of the order as it affects the outcome.
  • Check variable types. Ensure compatible data types when assigning values to avoid errors or data loss.
  • View current settings. Use `SHOW VARIABLES` to see current settings before making changes, particularly for system variables.