Custom variables

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

SET @variable_name = value;

@variable_name is the name of the custom variable, and value is the value to be assigned to that variable. Once a variable is defined, it 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:

SET @max_salary = 100000;
-- View the value set by @max_salary variable
mysql> select @max_salary;
+-----------------+
| @max_salary |
+-----------------+
| 100000 |
+-----------------+
1 row in set (0.01 sec)

When using custom variables, you can include them in SQL statements, referenced in the form of @variable_name. For example, the following statement will return all employee records with salary less than @max_salary:

SELECT * FROM employees WHERE salary < @max_salary;

The results of SQL queries can be affected by changing the value of a custom variable. For example, the following statement changes the value of @max_salary and returns a new query result:

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

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

Simple example

Now take the example of defining two variables a and b:

> SET @a=2, @b=3;
Query OK, 0 rows affected (0.00 sec)

> select @a;
+------+
| @a |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

> select @b;
+------+
| @b |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

Use custom variables in SQL:

> create table t1(a int,b varchar(1));
Query OK, 0 rows affected (0.02 sec)

> insert into t1 values(@a,@b);
Query OK, 1 row affected (0.02 sec)

> select * from t1;
+------+------+
| a | b |
+------+------+
| 2 | 3 |
+------+------+
1 row in set (0.01 sec)

Note

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

MySQL Compatibility

MatrixOne supports session level, the same as MySQL support.