MySQL Infusion UDF is a native extension that adds string, numeric, aggregate, bit-field, and utility functions to MySQL. I started the project in 2010 while replacing a collection of stored functions that had to be installed in every database separately. A native loadable function is registered once for the server and executes inside the database process.
The source remains available in the MySQL Infusion UDF repository under GPL Version 2. The repository contains C and C++ sources, an Autotools build, generated registration scripts, and an older Python test harness. It has no tagged release, so a deployment should be pinned to a reviewed commit rather than tracking the default branch implicitly.
What Belongs in a Database Extension?
Moving arbitrary business logic into the database remains a poor trade. It couples application releases to every database server, spends scarce database CPU, and makes a cluster harder to upgrade consistently. A UDF is most useful when it reduces data before it crosses the network, exposes an operation that SQL cannot express efficiently, or implements an aggregate that needs one pass over a group.
That boundary has shifted since the first version. MySQL 8 provides window functions and a wider native SQL surface, so a custom function should be compared with the built-in alternative before installation. The modern statistical SQL techniques cover many analyses without loading native code into the server.
How the Extension Is Loaded
The build installs udf_infusion.so into MySQL's plugin_dir. The generated load.sql then binds SQL names to symbols in that shared object with CREATE FUNCTION and CREATE AGGREGATE FUNCTION. These names are server-wide; they are not scoped to the database selected by USE.
SHOW VARIABLES LIKE 'plugin_dir';
CREATE FUNCTION cut
RETURNS STRING
SONAME 'udf_infusion.so';
CREATE AGGREGATE FUNCTION median
RETURNS REAL
SONAME 'udf_infusion.so'; The generated script is preferable to registering functions by hand because it stays aligned with the selected build. Registration does not copy the library. MySQL must already be able to load the installed file from its plugin directory.
The Registered API
The following surface comes from load.sql.sh on the repository's default branch. The signatures describe the SQL-facing intent; MySQL's loadable-function ABI ultimately passes values through its own argument and result structures.
Aggregate Functions
| Function | Purpose |
|---|---|
median(value) | Returns the median of a group. |
covariance(x, y) | Calculates covariance for paired observations. |
corr(x, y) | Calculates the correlation coefficient for paired observations. |
group_first(value) | Returns the first value received by the aggregate. |
group_last(value) | Returns the last value received by the aggregate. |
lesspart(value, sum) | Counts the smallest values whose partial sum stays below a limit. |
lesspartpct(value, fraction) | Applies the partial-sum test to a fraction of the total. |
lessavg(value) | Counts values below the group's average. |
percentile_cont(value, fraction) | Returns an interpolated continuous percentile. |
percentile_disc(value, fraction) | Returns the first value at or above a percentile position. |
skewness(value) | Calculates the third standardized moment. |
kurtosis(value) | Calculates the fourth standardized moment. |
stats_mode(value) | Returns the most frequent input value. |
group_first() and group_last() describe arrival order, not an inherent order of a SQL group. Without an ordering operation that the execution plan preserves, their result must not be treated as deterministic.
String Functions
| Function | Purpose |
|---|---|
cut(value, length, suffix) | Shortens text at a word boundary and appends a suffix. |
slug(value, separator) | Transliterates text into a URL-oriented identifier. |
ngram(value, size) | Generates a space-separated sequence of character n-grams. |
SELECT cut('This is the funny world of MySQL...', 15);
-- This is the...
SELECT slug('Max Müller Straße!', '-');
-- max-mueller-strasse
SELECT ngram('Lorem ipsum', 2);
-- _l lo or re em m_ _i ip ps su um m_ The repository describes these functions as UTF-8 aware, but their exact handling of collations, combining marks, malformed input, and newer Unicode versions should be tested against the target server. The MySQL UDF API does not expose every SQL collation operation as a convenient C-level primitive.
Math Functions
| Function | Purpose |
|---|---|
bround(value, base) | Rounds upward to the next multiple of a base. |
xround(value) | Rounds upward to a power of ten. |
bound(value, minimum, maximum) | Clamps a value; either bound may be NULL. |
noverk(n, k) | Calculates the binomial coefficient. |
SELECT bround(13, 3); -- 15
SELECT xround(55); -- 100
SELECT bound(12, 0, 4); -- 4
SELECT noverk(49, 6); -- 13983816 These integer examples fit comfortably in their result types. Production queries must still define behavior for negative inputs, zero bases, overflow, and values beyond the precision represented by the original implementation.
Binary Functions
| Function | Purpose |
|---|---|
isbit(mask, position) | Tests a zero-based bit position. |
setbit(mask, position, enabled) | Sets or clears a bit position. |
invbit(mask, position) | Toggles a bit position. |
rotbit(mask, count) | Rotates a bit pattern. |
rotint(mask, first, last, count) | Rotates a selected bit field. |
getint(mask, first, last) | Extracts an integer from a selected bit field. |
setint(mask, first, last, value) | Replaces a selected bit field. |
SELECT isbit(5, 2); -- 1
SELECT setbit(8, 4, 1); -- 24
SELECT invbit(8, 2); -- 12
SELECT getint(4283942, 4, 8); -- 2
SELECT setint(4283942, 4, 8, 10); -- 4284070 Bit positions are zero-based. Callers should reject negative positions and positions outside the width of MySQL's signed integer type before invoking the extension; C shifts outside the valid width are not a portable validation mechanism.
Miscellaneous Functions
| Function | Purpose |
|---|---|
rsumi(value) | Maintains a running integer sum. |
rsumd(value) | Maintains a running floating-point sum. |
fnv(value) | Calculates a 64-bit FNV hash and returns it through a signed SQL integer. |
MySQL 8 window functions are normally preferable to the stateful running-sum helpers because their partition and ordering semantics are explicit. An FNV hash is useful for compact bucketing or checks, but it is not suitable for passwords, signatures, or adversarial integrity checks.
The Historical 2010 Surface
The first article also documented several functions that are not registered by the current load.sql.sh. They remain part of the project's history, not promises made by the current build:
| Historical function | Original purpose |
|---|---|
numbit(mask) | Counted the set bits in an integer. |
msbit(mask) | Returned the highest set bit position. |
thumbscale(up, down, scale) | Mapped positive and negative votes onto a fixed scale. |
thumbratio(up, down) | Calculated a ratio from positive and negative votes. |
starratio(counts...) | Calculated an average from star-frequency buckets. |
Applications depending on one of these names must either pin an older source revision, port the function deliberately, or replace it with native SQL. Silently assuming that every function from the 2010 article still ships would make a new installation incomplete.
Building and Installing
A compiler, Autotools, and development files matching the target MySQL or MariaDB installation are required. Build against the same server family and ABI that will load the resulting shared object:
git clone https://github.com/infusion/udf_infusion.git
cd udf_infusion
./configure
make
sudo make install
mysql --defaults-extra-file="$HOME/.my.cnf" < load.sql A smaller build can enable only named functions:
./configure --enable-functions="cut slug ngram median percentile_cont percentile_disc"
make
sudo make install
mysql --defaults-extra-file="$HOME/.my.cnf" < load.sql The repository includes compatibility work for both MySQL and MariaDB headers, but that does not guarantee that an old binary can be copied between current releases. MySQL 8, MariaDB, compiler, architecture, and package updates can change the loadable-function ABI or plugin directory. Rebuild and test on the exact target release.
Testing
The included test harness creates a temporary udf_infusion_test database and historically expected Python 2.7, NumPy, and SciPy. Treat that environment as legacy infrastructure: isolate it, review its generated data and SQL, and add target-version integration tests before relying on it as a release gate.
make test_prepare make test make test_clean
Uninstalling and Upgrading
SQL bindings must be removed before the shared object. The generated unload script emits a DROP FUNCTION IF EXISTS statement for each selected function:
mysql --defaults-extra-file="$HOME/.my.cnf" < unload.sql
make uninstall For an upgrade, stop new calls, remove the old SQL bindings, replace the library, and recreate the bindings from the matching checkout. Do not overwrite a library while server threads may still execute code from it.
Security and Operational Boundary
A loadable function is native code inside mysqld. A memory error, undefined shift, or incompatible ABI can crash or corrupt the server process; a malicious library has the same operating-system access as that process. Only trusted administrators should install the file or execute CREATE FUNCTION and DROP FUNCTION.
Keep application accounts on least privilege: they should be allowed to execute approved functions but not write to plugin_dir, modify function registrations, or use broad file privileges. Build artifacts should be reproducible, reviewed, checksummed, and tested on a disposable server before production rollout.
References
- [Source]MySQL Infusion UDF source repository, implementation version 1.0.
- [MySQL]MySQL 8.4 Reference Manual: Adding a Loadable Function.
- [MariaDB]MariaDB Server Documentation: User-Defined Functions.