Variables can be used in MySQL statements:
mysql> SELECT @M := MAX(id) FROM footable;
mysql> INSERT INTO bartable (x, y, z) VALUES (1, 2, @M);
However, @M is just a temporary variable, local to the current thread. Other clients can’t get the @M value and as soon as the current client disconnects, @M is lost.
But what if, for instance, you want to use MySQL to keep the realtime number of online users on your web site?
The traditional way to do it is to create a dummy table, with ENGINE=MEMORY. This is just an ugly workaround for the lack of global, persistent variables.
Persistent MySQL user variables is something I wanted for a long time. So I finally implemented that as a MySQL UDF.
Here’s where you can download the UDF: MySQL persistent user variables UDF
It was tested on OpenBSD and Linux, with MySQL 5.0.33, but it probably works with other MySQL versions and OS as well.
Sample usage:
mysql> DO GLOBAL_SET("users_count", 42);
(you can then disconnect or connect from another client)
mysql> SELECT GLOBAL_GET("users_count") AS nb;
42
Variables can also be atomically incremented or decremented:
mysql> SELECT GLOBAL_ADD("users_count", 1) AS nb;
43
Bug reports, suggestions, patches, etc. are welcome.