Search Knowledge Base by Keyword
Know More About Variables In MySQL
Variables are used to store information. Variables are the way to label data with the name and help the reader to understand the program more clearly. The main purpose of the variable is to store data in memory and can be used in the program
MySQL supports User-Defined Variables, Local Variables, and System Variables
- User-Defined Variables – Clients can not configure user-defined variables. These directives are set by the database administrator. User-defined variables allow the passing of a value from one statement to another.
Following are the main characteristics of User-Defined Variables –
- These are not case-sensitive. Example -@john and @Johnboth refer to the same value.
- The maximum length of these variables is 64 characters
- A variable name can include other characters like – {! @ # $} . Example – @’var@2′ @’var#2′
- These variables are only initialized and cannot be declared
- These variables can take value from different sets of data types – integer, floating-point, decimal, binary
Syntax – @var_name = expression
2. Local Variables – These variables are not prefixed with the ‘@’ symbol. MySQL uses DECLARE keyword to declare the local variable and the default value of the local variable is NULL. It is mainly used by developers in stored procedures.
Syntax – DECLARE variable_name datatype(size) [DEFAULT default_value];
3. System Variables – These are special class and contains predefined variables. MYSQL contains various system variables and each system variable contains a default value. Users can modify System Variables by using the SET command at runtime. It makes the changes without stopping the server.
Different System Variables that are used in MYSQL are GLOBAL, MIX, and SESSION types.
GLOBAL Variables – As the name suggests, GLOBAL variables remain as it is throughout the lifecycle of the server
SESSION Variables – These variables remains only till the particular session remains active
