Movatterモバイル変換

MySQL 9.3 Reference Manual

27.3.4 JavaScript Stored Program Data Types and Argument Handling

Most MySQL data types are supported for MLE stored program input and output arguments, as well as for return data types. The data types are listed here:

Integer: All variants and aliases of MySQL integer data types are supported, includingTINYINT,SMALLINT,MEDIUMINT,INT, andBIGINT.
SIGNED andUNSIGNED are supported for all these types.
BOOL andSERIAL are also supported, and treated as integer types.
String: TheCHAR,VARCHAR,TEXT, andBLOB string types are supported.
These types are supported as in the MySQL server with the following exceptions:
1. String argument and return types can use theutf8mb4 or binary character sets; use of other character sets for these raises an error. This restriction applies to argument and return type declarations; the server attempts to convert argumentvalues using other character sets toutfmb4 whenever necessary, as with SQL stored programs.
2. The maximum supported length for aLONGTEXT value is 1073741799 (2³⁰ - 2⁴ - 2³ - 1) characters; forLONGBLOB, the maximum supported length is 2147483639 (2³¹ - 2⁸ - 1).
Support forBLOB types includes support forBINARY andVARBINARY.
The MySQLJSON data type is also supported.
Floating point:FLOAT andDOUBLE are supported along with their aliases.REAL is also treated as floating point, butUNSIGNED FLOAT andUNSIGNED DOUBLE are deprecated in MySQL, and are not supported by MLE.
Temporal types:DATE,DATETIME, andTIMESTAMP are supported, and are converted to JavaScriptDate values.TIME values are treated as strings;YEAR values are treated as numbers.
The first time a given JavaScript stored procedure is executed, it is associated with the current MySQL session time zone, and this time zone continues to be used by the stored program, even if the MySQL session time zone is changed concurrently, for the duration of the MLE component session, or untilmle_session_reset() is invoked. More more information, seeTime zone support, later in this section.
VECTOR is supported in MySQL 9.1 and later.
DECIMAL andNUMERIC are supported in MySQL 9.3 and later.

Input arguments (IN andINOUT parameters) are automatically converted into JavaScript types based on the mapping shown in the following table:

Table 27.1 Conversion of MySQL data types to JavaScript types

MySQL Type	JavaScript Type
`TINYINT`,`SMALLINT`,`MEDIUMINT`,`INT`,`BOOL`,`BIGINT`, or`SERIAL`	If safe:`Number`; otherwise:`String`
`FLOAT` or`DOUBLE`	`Number`
`CHAR`,`VARCHAR`,`TINYTEXT`,`TEXT`,`MEDIUMTEXT`, or`LONGTEXT`	`String`
`TINYBLOB`,`BLOB`,`MEDIUMBLOB`,`LONGBLOB`,`BINARY`, or`VARBINARY`	`Uint8Array`
`DATE`,`DATETIME`, or`TIMESTAMP`	`Date`
`TIME`	`String`
`YEAR`	`Number`
`VECTOR`	`Float32Array`

Conversion to or from a MySQL integer whose value lies outside the range -(2⁵³-1) (-9007199254740991) to 2⁵³-1 (9007199254740991) is lossy. How conversion from MySQL integers to JavaScript is performed can be changed for the current session usingmle_set_session_state(); the default behavior is equivalent to calling this function usingUNSAFE_STRING as the value forinteger_type. See the description of that function for more information.

SQLNULL is supported for all the types listed, and is converted to and from JavaScriptnull as required.

JavaScript (unlike SQL) is a dynamically typed language, which means that return types are known only at execution time. JavaScript return value and output arguments (OUT andINOUT parameters) are automatically converted back into the expected MySQL type based on the mappings shown in the following table:

Table 27.2 Type Conversion: JavaScript to MySQL

From JavaScript Type	To MySQL`TINYINT`,`SMALLINT`,`MEDIUMINT`,`INT`,`BIGINT`,`BOOLEAN`, or`SERIAL`	To MySQL`CHAR` or`VARCHAR`	To MySQL`FLOAT` or`DOUBLE`	To MySQL`TINYTEXT`,`TEXT`,`MEDIUMTEXT`, or`LONGTEXT`	To MySQL`TINYBLOB`,`BLOB`,`MEDIUMBLOB`,`LONGBLOB`,`BINARY`,`VARBINARY`	To MySQL`VECTOR`	To MySQL`DECIMAL` (`NUMERIC`)
`Boolean`	Cast to`Integer`	Convert to`String`; check whether length of result is within expected range	Cast to`Float`	If JavaScript`Booleantrue`: convert to“true”; if JavaScript`Booleanfalse`: convert to“false”	Error	Error	Convert to`String` or`Number` (default`String`)
`Number`	Round value to`Integer`; check whether value is out of range	Convert to`String`; check whether length of result is within expected range	Retain value; check whether this is out of range	Convert to`String`; check whether length of result is within expected range	Error	Error	Convert to`String` or`Number` (default`String`)
`BigInteger`	Retain value; check whether out of range	Convert to`String`; check whether length of result is within expected range	Cast to`Float`; check whether result is out of range	Convert to`String`; check whether length of result is within expected range	Error	Error	Convert to`String` or`Number` (default`String`)
`String`	Parse as number and round to`Integer`; check for value out of range	Retain value; check whether length is within range	Parse value to`Float`; check for value out of range values	Use existing string value; check whether length of string is within expected range	Error	Error	Convert to`String` or`Number` (default`String`)
`Symbol` or`Object`	Raise invalid type conversion error	Convert to`String`; check whether length of result is within expected range	Raise invalid type conversion error	Convert to`String`; check whether length of result is within expected range	Error	Error	Error
Typed`Array`	Raise invalid type conversion error	Convert to`String`; check whether length of result is within expected range	Raise invalid type conversion error	Convert to`String`; check whether length of result is within expected range	Convert to byte array; check whether result is within expected size	Treat as`Float32Array`; convert to byte array, checking whether it is within the expected`VECTOR` field size	Error
`null` or`undefined`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`

Notes:

JavaScriptInfinity and-Infinity are treated as out-of-range values.
JavaScriptNaN raises an invalid type conversion error.
All rounding is performed usingMath.round().
Attempting to cast aBigInt orString having a non-numeric value to MySQLFLOAT raises an invalid type conversion error.
The maximum supported length for strings is 1073741799.
The maximum supported length forBLOB values is 2147483639.
JavaScript.MAX_SAFE_INTEGER is equal to 9007199254740991 (2⁵³-1).

Table 27.3 Type Conversion: JavaScript Dates to MySQL

JavaScript Type	MySQL`DATE`	MySQL`DATETIME`,`TIMESTAMP`	MySQL YEAR
`null` or`undefined`	`NULL`	`NULL`	`NULL`
`Date`	Retain value as is, rounding off any time part to the closest second.	Keep value as is.	Extract year from the`Date`
Type convertible to JavaScript`Date` (formattedstring)	Cast value to JavaScript`Date` and handle accordingly	Cast value to JavaScript`Date` and handle accordingly	If value contains 4-digit year, use it.
Type not convertible to JavaScript`Date`	Invalid type conversion error	Invalid type conversion error	If value contains 4-digit year, use it.

Passing a MySQL zero date (00-00-0000) or zero-in-date value (such as00-01-2023) leads to the creation of anInvalid Date instance ofDate. When passed a MySQL date which is invalid (for example, 31 February), MLE calls a JavaScriptDate constructor with invalid individual date and time component values.

The MySQLTIME type is handled as a string, and is validated inside MySQL. SeeSection 13.2.3, “The TIME Type”, for more information.

Table 27.4 Conversion of MySQL JSON types to JavaScript

MySQL JSON Type	JavaScript Type
`NULL`,`JSON NULL`	`null`
`JSON OBJECT`	`Object`
`JSON ARRAY`	`Array`
`JSON BOOLEAN`	`Boolean`
`JSON INTEGER`,`JSON DOUBLE`,`JSON DECIMAL`	`Number`
`JSON STRING`	`String`
`JSON DATETIME`,`JSON DATE`,`JSON TIME`	`String`
`JSON BLOB`,`JSON OPAQUE`	`String`

Note

A MySQL JSON string, when converted to a Javascript string, becomes unquoted.

Table 27.5 Conversion of JavaScript types to MySQL JSON

JavaScript Type	MySQL JSON Type
`null`,`undefined`	`NULL`
`Boolean`	Error
`Number`	Error
`String`	Can be parsed as JSON: JSON string, JSON object, or JSON array Cannot be parsed as JSON: Error `'null'`: JSON`null`
`BigInt`	Error
`Object`	JSON object or error (see text following table)
`Array`	JSON array
`Symbol`	Inside an object: ignored Inside an array: JSON`null` Scalar value: Error

Notes:

A value within a container such as a JSON array or JSON object is converted (loss of precision is possible forNumber values). A scalar value throws an error.
JavaScriptBigInt values cannot be converted to MySQL JSON; attempting to perform such a conversion always raises an error, regardless of whether the value is inside a container or not.
It may or may not be possible to convert a JavascriptObject to MySQL JSON, depending on howtoJSON() is implemented for the object in question. Some examples are listed here:
- ThetoJSON() method of the JavaScriptDate class converts aDate to a string having invalid JSON syntax, thus throwing a conversion error.
- For theSet class,toJSON() returns"{}" which is a valid JSON string.
- For JSON-like objects,toJSON() returns a valid JSON string.

Conversion to and from MySQL ENUM and SET. ENUM converts to a JavaScriptString;SET converts to a JavaScriptSet object, as shown in the following table:

Table 27.6 Conversion of the MySQL ENUM and SET types to JavaScript

MySQL Type	JavaScript Type
`ENUM`	`String`
`SET`	`Set`

The following table shows rules for converting a JavaScript type to a MySQLENUM orSET type:

Table 27.7 Type Conversion: JavaScript types to MySQL ENUM and SET

JavaScript Type	To MySQL`ENUM`	To MySQL`SET`
String	Preserve value; check whether string is valid`ENUM` value	Preserve value; check whether string is valid`SET` value
`null`,`undefined`	`NULL`	`NULL`
`Set`	Error	Convert to comma-separated string; check whether string is valid`SET` value
Any other type	Error	Error

Additional notes

All values used in or forENUM orSET values or their JavaScript equivalents must employ theutf8mb4 character set. SeeSection 12.9.1, “The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)”, for more information.
The server SQL mode can affect how an invalid JavaScript value is handled when attempting to insert it into anENUM orSET column. When strict mode is in effect (the default), an invalid value throws an error; otherwise, an empty string is inserted, with a warning. SeeSection 7.1.11, “Server SQL Modes”.

Conversion to and from MySQL DECIMAL and NUMERIC. MySQL decimal types (DECIMAL andNUMERIC) are converted to either of JavaScriptString orNumber, depending on the value ofsession.options.decimalType (STRING orNUMBER, respectively). The default behavior is to convert such values toString.

To set this variable on the session level so that MySQL decimal types are converted toNumber by default instead, callmle_set_session_state() like this:

mle_set_session_state("decimalType":"NUMBER")

The MLE stored program cache must be empty when this function is invoked; it is not empty, you can clear it usingmle_session_reset(). See the description ofmle_set_session_state() for more information.

To set thedecimalType option within a JavaScript stored routine, useSession.setOptions(), as shown here:

session.setOptions('{"decimalType":"mysql.DecimalType.NUMBER"}')

This sets the default for conversion of MySQL decimal values toNumber for the lifetime of the routine. Usemysql.DecimalType.STRING to makeString the default.

Rules for conversion of JavaScript values to the MySQLDECIMAL type (or its aliasNUMERIC) are shown in the following table:

Table 27.8 Type Conversion: JavaScript types to MySQL DECIMAL

JavaScript Type	Returns
`Object`,`Array`, or`Symbol`	Error: Conversion not supported
`Boolean`,`Number`,`String`, or`BigInt`	`DECIMAL` value
`null`,`undefined`	SQL`NULL`

The maximum which a decimal value can hold is determined by the precision and scale ofDECIMAL(M,D), whereM is the precision (maximum number of digits) in the range 1-65, andD is scale (the number of digits to the right of the decimal point, with the range 0-30. In addition,M must be greater than or equal toD. (SeeSection 13.1.3, “Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC”, for more information.)

In the event that a decimal value exceeds the range specified byDECIMAL(M,D) or cannot be stored within the constraints ofDECIMAL(M,D), the behavior depends on the server SQL mode (seeSection 7.1.11, “Server SQL Modes”), as follows:

Strict SQL Mode: An error is raised, and the operation fails.
Otherwise: The value is automatically capped to the nearest valid minimum or maximum value for the range given, and a warning is issued.

Time zone support. A JavaScript stored program uses the MySQL session timezone in effect at the time it is first invoked. This time zone remains in effect for this stored program for the duration of the session in the session.

Changing the MySQL session time zone is not automatically reflected in stored programs which have been used and thus are already cached. To make them use the new time zone, callmle_session_reset() to clear the cache; after this, stored programs use the new time zone.

Supported time zone types are listed here:

Time zone offsets from UTC, such as+11:00 or-07:15.
Timezones defined in theIANA time zone database are supported, with the exception of configurations using leap seconds. For example,Pacific/Nauru,Japan, andMET are supported, whileleap/Pacific/Nauru andright/Pacific/Nauru are not.

Range checks and invalid type conversion checks are performed following stored program execution. Casting is done inside JavaScript using type constructors such asNumber() andString(); rounding toInteger is performed usingMath.round().

An input argument (IN orINOUT parameter) named in a JavaScript stored program definition is accessible from within the routine body using the same argument identifier. Output arguments (INOUT andOUT parameters) are also available in JavaScript stored procedures. The same argument identifier can be used to set the value using the JavaScript assignment (=) operator. As with SQL stored procedureOUT arguments, the initial value is set to JavaScriptnull.

Caution

You shouldnot override program arguments usinglet,var, orconst inside JavaScript stored programs. Doing so turns them into variables which are local to the program, and makes any values passed into the program using the same-named parameters inaccessible.

Example:

mysql> CREATE FUNCTION myfunc(x INT)    ->   RETURNS INT LANGUAGE JAVASCRIPT AS    -> $$    $>   var x    $>       $>   return 2*x    $> $$    -> ;Query OK, 0 rows affected (0.03 sec)mysql> SELECT myfunc(10);ERROR 6000 (HY000): MLE-Type> Cannot convert value 'NaN' to INT from MLE in 'myfunc(10)'

The JavaScriptreturn statement should be used to return scalar values in stored functions. In stored procedures, this statement does not return a value, and merely exits the code block (this may or may not also exit the routine depending on program flow).return cannot be used to set stored procedureOUT orINOUT argument values; these must be set explicitly within the routine.

For information about accessing MySQL stored procedures and stored functions from within JavaScript stored routines, seeSection 27.3.6.10, “Stored Routine API”.

PREV HOME UP NEXT