The main xxx() function should be declared
as shown in this section. Note that the return type and
parameters differ, depending on whether you declare the SQL
function XXX() to return
STRING,
INTEGER, or
REAL in the
CREATE FUNCTION statement:

The initid parameter is passed to all three
functions. It points to a UDF_INIT
structure that is used to communicate information between
functions. The UDF_INIT structure members
follow. The initialization function should fill in any members
that it wishes to change. (To use the default for a member,
leave it unchanged.)

my_bool maybe_null

xxx_init() should set
maybe_null to 1 if
xxx() can return
NULL. The default value is
1 if any of the arguments are declared
maybe_null.

unsigned int decimals

The number of decimal digits to the right of the decimal
point. The default value is the maximum number of decimal
digits in the arguments passed to the main function. For
example, if the function is passed
1.34, 1.345, and
1.3, the default would be 3, because
1.345 has 3 decimal digits.

For arguments that have no fixed number of decimals, the
decimals value is set to 31, which is 1
more than the maximum number of decimals permitted for the
DECIMAL,
FLOAT, and
DOUBLE data types. In MySQL
5.7, this value is available as the constant
NOT_FIXED_DEC in the
mysql_com.h header file.

A decimals value of 31 is used for
arguments in cases such as a
FLOAT or
DOUBLE column declared
without an explicit number of decimals (for example,
FLOAT rather than
FLOAT(10,3)) and for floating-point
constants such as 1345E-3. It is also
used for string and other nonnumber arguments that might
be converted within the function to numeric form.

The value to which the decimals member
is initialized is only a default. It can be changed within
the function to reflect the actual calculation performed.
The default is determined such that the largest number of
decimals of the arguments is used. If the number of
decimals is NOT_FIXED_DEC for even one
of the arguments, that is the value used for
decimals.

unsigned int max_length

The maximum length of the result. The default
max_length value differs depending on
the result type of the function. For string functions, the
default is the length of the longest argument. For integer
functions, the default is 21 digits. For real functions,
the default is 13 plus the number of decimal digits
indicated by initid->decimals. (For
numeric functions, the length includes any sign or decimal
point characters.)

If you want to return a blob value, you can set
max_length to 65KB or 16MB. This memory
is not allocated, but the value is used to decide which
data type to use if there is a need to temporarily store
the data.

char *ptr

A pointer that the function can use for its own purposes.
For example, functions can use
initid->ptr to communicate allocated
memory among themselves. xxx_init()
should allocate the memory and assign it to this pointer:

initid->ptr = allocated_memory;

In xxx() and
xxx_deinit(), refer to
initid->ptr to use or deallocate the
memory.

my_bool const_item

xxx_init() should set
const_item to 1 if
xxx() always returns the same value and
to 0 otherwise.

• const_item is an input/output parameter. It comes in as 1 (true) if and only if all UDF arguments are known to be constant throughout the query. That is, const_item will come into init as true only if there are no NULLs in the args array.

• If const_item is returned non-zero (true), MySQL may assume that your main function returns the same value for all calls in this context, and only call your main function once. (Or it may call your main function multiple times anyway, as I have noticed. If you care to optimize this, you should cache your return value instead of recalculating every time.) Otherwise (const_item is zero upon return), your main function will be called for every row.

This means that, in the common case where your return value is only dependent on the arguments, your init function shouldn’t touch const_item at all. You should only change const_item if:

• You might return a different value even with the same args, in which case you should set const_item to 0 (false). Random number generators or sequence number functions would be examples of this.

• The values of certain constant args will cause your function to return the same value regardless of the remaining non-constant ones, in which case you should set const_item to 1 (true). (If your return value is always the same, and independent of the value of *any* args, you should check if your function is pointless.)