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

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).

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.

StackExchange Database Administrators Q#50678, answer score: 15