-   [](https://platform.exaloop.io/)
-   Databases
-   [MySQL](https://platform.exaloop.io/db/mysql/)
-   Server functions

# 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](https://platform.exaloop.io/start/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`:

```python title="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:

```sh title="Terminal"
exaloop --kind mysql-udf build functions.codon -o "$PWD/build/codon_functions.so"
mysql -e "SELECT @@plugin_dir"
```

Use the SDK builder directly

```sh title="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:

```sh title="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 type | MySQL UDF result class |
| --- | --- |
| `bool`, `i16`, `i32`, `int`, `i64` | `INTEGER` |
| `float32`, `float` | `REAL` |
| `str`, `bytes` | `STRING` |
| `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

-   [Deploy and update the library](https://platform.exaloop.io/db/mysql/deployment/) without overwriting a loaded binary.
-   Check the [MySQL reference](https://platform.exaloop.io/db/reference/#mysql) for connection and type details.
-   [Use a client application](https://platform.exaloop.io/db/mysql/client/) when the calculation should run outside MySQL.

Source: https://platform.exaloop.io/db/mysql/functions/