MariaDB provides the capability of user-defined variables. These are variables that you define yourself and use them within the SQL session. They are not meant to interact with data stored in your database, but to assist in the construction and processing of your queries.
Here are the steps on how you can use variables in MariaDB:
1. Declaring a Variable: We use the set command to declare a variable in MariaDB.
\`\`\` SETvariable\_name = value;
\`\`\`
Example:
\`\`\`sql
SET total\_sales = 1000;
\`\`\`
1. Using a Variable: Once you’ve set a variable using either method, you can use it by prefixing the variable name with an @ symbol. You can use the variable throughout your session until closing the client.
Example: \`\`\`sql SELECT @total\_sales; \`\`\`
1. Reuse Variable: Assigned variables can also be used in SQL statements such as SELECT, INSERT, UPDATE or DELETE etc.
Example: \`\`\`sql UPDATE sales SET total_sales = total_sales + @total_sales WHERE sales_id = 1; \`\`\`
1. Set Multiple Variables: To declare multiple variables in Maria DB, we use the set command and separate each variable declaration by a comma.
Example:
\`\`\`sql
SET var1 = 1, var2 = 2, @var3 = 3;
\`\`\`
Important notes:
- The Variable names are case sensitive (var1 and Var1 are two different variables)
- These variables do not require declaration and they have no type. Their value can be changed at any moment.
- The scope of a user-defined variable is the session, which means a variable defined by a client can’t be seen or used by other clients. Furthermore, all variables for a client session are lost when that client logs out.
- If a value is assigned to the variable that contains a `NULL` reference or a value is not assigned to the user-defined variable during declaration, the variable will have a `NULL` value.
- Variables which are only mentioned (in the context of a SQL command where a variable reference would be permitted syntactically) before being assigned are treated as `NULL`.
