Some open-source projects made by Frank DENIS.

MySQL Global User Variables UDF

A MySQL extension to store persistent variables

This shared library adds simple user functions to MySQL in order to keep persistent shared variables in memory. These variables and their values are available to all clients. Any data can be stored into these persistent variables, including BLOBs. Since updates are atomic and way faster than MEMORY tables, this is an easy and efficient way to handle counters and sequences.

Usage

Storing a value

An unlimited number of user variables can be created, as long as memory is available.

The GLOBAL_STORE(, ) stores a new shared global variable.

Examples: 

mysql> DO GLOBAL_STORE("online_users", 42);
mysql> DO GLOBAL_STORE("secret_key", "pajfUyfnd");

The GLOBAL_STORE() function always returns 1 unless an error occurred.

Fetching a value

Reading the value of a variable is the job of the GLOBAL_GET() function.

The value is returned, or NULL is the variable is undefined.

Example: 

mysql> SELECT GLOBAL_GET("online_users;);
42

mysql> SELECT id FROM pxs WHERE secret_key = GLOBAL_GET("secret_key");
1

Atomic increments

A single function call can read the previous value, add an integer (that can be negative), and store the new value into the variable.

The function is GLOBAL_ADD(, ) and the return value is the new value of the variable.

Updates are always atomic, if the old value is 18 and you add 1, you will always get back 19.

Example: 

mysql> DO GLOBAL_ADD("online_users", 1);
mysql> SELECT GLOBAL_ADD("online_users", -4);
39

If the value of a variable was a string, the new value is the increment:


mysql> SELECT GLOBAL_ADD("secret_key", 12);
12

Adding a value to an undefined variable returns NULL.

A handy variant is GLOBAL_ADDP(, ). GLOBAL_ADDP() is similar to GLOBAL_ADD() but returns the PREVIOUS value of the variable instead of the new one.

Example: 

mysql> DO GLOBAL_SET("xxx", 10);
mysql> SELECT GLOBAL_ADDP("xxx", 1);
10
mysql> SELECT GLOBAL_ADDP("xxx", 1);
11