Tags: index

SQL Variables in Mysql

By admin on Jun 5, 2008 | In Formula 1 - More advanced stuff, Highway Maintenance - DBA stuff | Send feedback »

The use of SQL variables can make life a lot easier when processing data from tables. You can assign a value returned by a SELECT statement to a variable, then refer to the variable later in your mysql session. Note that the variable only contains a single value though. The syntax for assigning a variable within a select statement is:
@var_name := value
A good demonstration of how useful it can be, is when you need to perform deletes from multiple tables where the rows are linked by a common id field (foreign key). For example say you have employees in the emp table that belong to the 'CALLDESK' department, and you need to delete those records, and also records in other tables that are related to 'CALLDESK' department.
First we populate our variable with the relevant id.

Code:

SELECT @id := deptno
FROM dept
WHERE dname = 'CALLDESK';
+---------------+
| @id := deptno |
+---------------+
|            50 |
+---------------+

We can then delete from related tables:

Code:

DELETE FROM emp where deptno = @id;
 
DELETE FROM dept where deptno = @id;

You can also, if you wish, assign a standalone value to a SQL variable eg:

Code:

mysql> set @sum = 52 * 350;
 
select @sum;
+-------+
| @sum  |
+-------+
| 18200 |
+-------+

So, for instance, you could use this variable to find all contractors or employees in your database tables who are earning more 350 a week over the year. Of course, this is a very basic example, but you can of course make the calculation a little more difficult. You can also update the value in the variable for example:

Code:

set @sum = @sum * 1.20;
 
select @sum;
+----------+
| @sum     |
+----------+
| 21840.00 |
+----------+

Another use: Suppose you want to add a column to an existing table and you want to set a value that increments by 5 for each row. One way of doing this would be to create a new table based on the old table, and then renaming the table (ALTER TABLE tname RENAME TO newtname; )
You start by assigning the variable
SET @id = 0;
Then you can perform the select in this way:

Code:

INSERT INTO TBL_NEW
(field1, field2, field3, new_seq)
SELECT field1, field2, field3,
@id :=@id+5
FROM TBL_OLD;

You have to be careful when doing something like this, as indexes and privileges on the table may have to be re-created. However the INSERT would normally be a lot quicker than doing an UPDATE.

One last example on how using a SQL variable can speed things up. Supposing you want to select a single random record from a large table (say more than 50000 records). that does have a sequence index field.
Using this following statement would be very slow as MYsql has to load all of the table into a temporary table and sort:

Code:

SELECT * FROM BIG_TABLE
ORDER BY RAND() LIMIT 1;

A better way to do this would be to first select the count of the table into a SQL variable, and then to create a random figure from that total.

Code:

SELECT @row_id := count(*) from BIG_TABLE;
 
SELECT @row_id := FLOOR(RAND() * @row_id) + 1;

You can then select the row from the table that has a sequence number equivalent to @row_id

Code:

SELECT * from BIG_TABLE
WHERE seq_field = @row_id

As you can probably guess, this would be a a lot more efficient way of retrieving a random record.

Tags: index, random, related records, sql variable, variables