MySQL/Language/User Variables

Local variables

The local variables can't be reached from outside their function or stored procedure[1].

They are declared like this[2]:

DECLARE MyVariable1 INT DEFAULT 1;

Session variables

  • The ability to set variables in a statement with the := assignment operator:
  • For e.g. (@total) to calculate the total in an example, you have to have the total column first because it must be calculated before the individual percentage calculations.
  • Session variables are set for the duration of the thread.
  • In the vast majority of cases you'd use a programming language to do this sort of thing.
  • MySQL variables can be useful when working on the MySQL command line.
  • If no records are returned, the user variable will not be set for that statement.
  • A user variable set in the field list cannot be used as a condition.
  • The value of a variable is set with the SET statement or in a SELECT statement with :=
 select @test := 2;
 select @test + 1;  -- returns 3

 set @startdate='some_start_date', @enddate='some_end_date'

 SELECT @toremember:=count(*) FROM membros;
	
 select @numzero := count(*) from table1 where field=0; 
 select @numdistinct := count(distinct field) from table1 where field <> 0 ; 
 select @numzero @numdistinct;
  • You can copy values retrieved by a SELECT into one or more variables:
 SET @id = 0, @name = '';
 SELECT id, name INTO @id, @name FROM table1 limit 1;
 SELECT @id, @name;

Global variables

A global variable is visible to all users, it allows to modify the configuration files settings during the session or definitely. So when changing them, it's necessary to precise this permanent or ephemera criteria, with respectively set global and set session. Example:

 mysql> set @@global.max_connections = 1000;
 mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 60 |
+---------------+-------+
1 row in set (0.00 sec)
 mysql> set @@session.wait_timeout=120;

References


  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.

MySQL/Language/User_Variables
 



 

Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.


Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.


  Contact Us