MySQL possibility to create global routines (stored procedures and/or functions)

Question

Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database.

When I tried to create a routine from console (without prior issuing use dbname) I am getting an error:

ERROR 1046 (3D000): No database selected

We have tons of identical databases (data is different) and the goal is to create some triggers for some tablenames. But we want to run only one routine so we don't have to create those routines for every database (since they are identical, routines would work the same for each database).

• Raymond Nijland: Are you sure that you want to create triggers in mysql? read this it an article off mine dba.stackexchange.com/questions/48797/…

Answer 1

There is no way to define stored procedures or stored functions (or events) that are global.

One approach is to create a shared common schema and then qualify the calls to the functions and procedures with the name of that schema (CALL shared.the_procedure();).

This is something I do with my collection of custom date/time calculation functions (e.g., SELECT date_time.next_quarter_start_after(NOW())), and with the ever-so-handy common_schema framework, which, of course, lives in `common_schema`.

If you take that approach, you have to remember then when a routine is running, the "current" database automatically changes, and the return value of the DATABASE() function returns the name of the schema under which the routine was defined, not your session's current database. It changes back when the routine exits, so if used in a trigger, it doesn't break anything surrounding it but you don't have a way of knowing from inside the routine what the current database was, if you needed to know.

Answer 2

Just to expand a little upon @Michael's answer, but while referencing a common schema is the way to go, you can create "aliases" in local databases to make this a bit easier to manage.

For example, I'm working with a MySQL derivative that doesn't yet have MySQL 8's UUID_TO_BIN function, so I've created my own, and stored it in a database specifically for global stuff that I've called common. So to reference this function I now have to use common.UUID_TO_BIN in all of my queries and stored procedures. Not a huge problem, but not quite as easy as simply calling UUID_TO_BIN (as I would if the native function were available).

So what I've done is also added an "alias" to each of my databases like so:

CREATE FUNCTION `UUID_TO_BIN`(a_uuid CHAR(36), a_reorder BOOL) RETURNS binary(16)
 DETERMINISTIC
RETURN `common`.UUID_TO_BIN(a_uuid, a_reorder);

This way, in each database I add this "alias" I can now simply call UUID_TO_BIN(some_uuid, TRUE) without any database name added, but without the hassle of duplicating the entire function, i.e- if I needed to change or optimise the function for some reason, I only have to do so in a single place (common.UUID_TO_BIN) rather than updating every single database.

If I later upgrade to a database with a native UUID_TO_BIN I can also simply remove all of my "alias" functions and all of my existing queries and procedures will now use it without any further modification. Or if the global function were to be moved to a different database, I only have to update my aliases, rather than every single one of my queries that use it.

I'm not sure how smart MySQL is when it comes to optimising away a function that simply calls another function, so there may be a slight cost associated with redirecting it in this way, but I think it's worth it for the simplified management, while retaining only a single "global" definition.

Answer 3

A very simple approach is this:

1. Make sure that you have common variables and parameters that will be passed to a certain function.
2. And simply call the function that you have made from other database. ex. select [databasename].empstatus(empid) as status;

PHP Script:

$empid=trim($_REQUEST['empid']);
$conn=mysqli_connect($db_host, $db_user, $db_pass, $db_name);
$sqld="SELECT [otherdatabasename].empstatus('$empid') as employee_status";
$rs=mysqli_query($conn,$sqld);
$rw= mysqli_fetch_array($rs);

Answer 4

If someone like me end up in this old question, nowadays loadable function were created.

According to the documentation

Use the loadable function interface. A loadable function is compiled as a library file and then loaded and unloaded from the server dynamically using the CREATE FUNCTION and DROP FUNCTION statements.