Setting custom variable

In MatrixOne, custom variables are a mechanism for storing and manipulating values. Custom variables can be set through the the’SET’statement, and the value can be kept unchanged throughout the session. You can customize variables through the SET command and use it in SQL. The specific syntax is as follows:

  1. SET @variable_name = value;

@variable_name is the name of the custom variable, and value is the value to be assigned to the variable. Once defined, the variable can be used in the SQL statement instead of the actual value.

For example, the following statement will define a variable named @max_salary and set it to 100000:

  1. SET @max_salary = 100000;
  2. --View the value set by the @max_salary variable
  3. mysql> select @max_salary;
  4. +-------------+
  5. | @max_salary |
  6. +-------------+
  7. | 100000      |
  8. +-------------+
  9. 1 row in set (0.01 sec)

When using a custom variable, you can include it in an SQL statement and reference it as @variable_name. For example, the following statement will return all employee records whose salary is less than @max_salary:

  1. SELECT * FROM employees WHERE salary < @max_salary;

You can affect the result of an SQL query by changing the value of a custom variable. For example, the following statement will change the value of @max_salary and return a new query result:

  1. SET @max_salary = 80000;
  2. SELECT * FROM employees WHERE salary < @max_salary;

It should be noted that custom variables only remain valid in the current session, and when the session ends, the variables will be deleted and released. In addition, variable names must start with the @ symbol and are case-sensitive.

Simple Example

Now let’s define two variables, a and b:

  1. > SET  @a=2, @b=3;
  2. Query OK, 0 rows affected (0.00 sec)
  4. > select @a;
  5. +------+
  6. | @a   |
  7. +------+
  8. |    2 |
  9. +------+
  10. 1 row in set (0.00 sec)
  12. > select @b;
  13. +------+
  14. | @b   |
  15. +------+
  16. |    3 |
  17. +------+
  18. 1 row in set (0.00 sec)

Using custom variables in SQL:

  1. > create table t1(a int,b varchar(1));
  2. Query OK, 0 rows affected (0.02 sec)
  4. > insert into t1 values(@a,@b);
  5. Query OK, 1 row affected (0.02 sec)
  7. > select * from t1;
  8. +------+------+
  9. | a    | b    |
  10. +------+------+
  11. |    2 | 3    |
  12. +------+------+
  13. 1 row in set (0.01 sec)

Note

The variables a and b are both ints here. If you want a string of 2 or 3, it is recommended to use SET @a ='2', @b='3';.

MySQL compatibility

MatrixOne supports the session level, which is the same as MySQL support.