Skip to main content
Markdown

MySQL Server Functions

Expose a typed Codon calculation as a MySQL user-defined function (UDF). The SDK builds a native library and registration SQL so queries can call the function directly. This walkthrough implements an expected-loss calculation and a customer-label formatter.

Before you begin: install the all-SDK distribution, use an Oracle MySQL 8.0+ development server with matching headers, and arrange administrator access to its plugin directory. MariaDB is not supported.

Compile and Load Functions​

Mark typed top-level functions with @mysql.function. Save these as functions.codon:

functions.codon
import exaloop.databases.mysql as mysql


@mysql.function
def expected_loss(balance: float, probability: float) -> float:
    return balance * probability


@mysql.function
def customer_label(name: Optional[str]) -> str:
    return "Customer: " + (name if name is not None else "unknown")

Compile the file with your installed SDK:

Terminal
exaloop --kind mysql-udf build functions.codon -o "$PWD/build/codon_functions.so"
mysql -e "SELECT @@plugin_dir"
Use the SDK builder directly
Terminal
python3 /path/to/sdk/mysql/build.py functions.codon \
  --codon /absolute/path/to/codon -o "$PWD/build/codon_functions.so"

Have an administrator install the resulting library in that server's plugin_dir, with the matching Codon runtime libraries available to the server's loader. Then register the functions using the generated SQL:

Terminal
mysql < build/codon_functions.sql
mysql -e "SELECT expected_loss(1000.0, 0.02), CONVERT(customer_label(NULL) USING utf8mb4)"

The first result is 20.0; the second is Customer: unknown. Use the generated SQL to keep the database declarations consistent with the compiled functions. Building requires Python, a C++17 compiler, and MySQL development headers, but the loaded functions run without Python.

Types and Errors​

Codon typeMySQL UDF result class
bool, i16, i32, int, i64INTEGER
float32, floatREAL
str, bytesSTRING
Optional[T]Same result class, with SQL NULL support

Use Optional[T] when your function needs to make a decision about missing data. The customer_label example accepts None and supplies a fallback label, so customer_label(NULL) produces Customer: unknown. With a non-optional argument, MySQL returns SQL NULL without calling your function. An optional return type lets your function return None as SQL NULL.

Integer arguments must fit the declared Codon type; they are never silently truncated or wrapped. Functions that accept or return floating-point values must use finite numbers.

String functions use UTF-8. MySQL treats a UDF's string result as binary, which is why the example uses CONVERT(... USING utf8mb4) to obtain SQL text.

Handle expected failures

An uncaught exception causes MySQL to return NULL for the current and remaining rows of that expression. A NULL result alone does not tell you whether evaluation succeeded.

Next Steps​