-
Notifications
You must be signed in to change notification settings - Fork 5
Data Type Mapping for Arguments
Roland edited this page Jul 3, 2013
·
2 revisions
The following table illustrates the relationships between the different type systems pertinent to the js UDFs with regard to mapping arguments:
+------------------------+------------------------+---------------------+-------------+---------+
| Type family | MySQL column data type | MYSQL UDF data type | v8 type | JS Type |
+------------------------+------------------------+---------------------+-------------+---------+
| Integral numbers | BIGINT | INT_RESULT | v8::Integer | Number |
| | INT | | or | |
| | MEDIUMINT | | v8::Number | |
| | SMALLINT | | | |
| | TINYINT | | | |
+------------------------+------------------------+---------------------+-------------| |
| Floating point numbers | DOUBLE | REAL_RESULT | v8::Number | |
| | FLOAT | | | |
+------------------------+------------------------+---------------------+ | |
| Decimal numbers | DECIMAL | DECIMAL_RESULT | | |
+------------------------+------------------------+---------------------+-------------+---------+
| Binary String | BINARY | STRING_RESULT | v8::String | String |
| | BLOB | | | |
| | LONGBLOB | | | |
| | MEDIUMBLOB | | | |
| | VARBINARY | | | |
| | TINYBLOB | | | |
+------------------------+------------------------+ | | |
| Character String | CHAR | | | |
| | LONGTEXT | | | |
| | MEDIUMTEXT | | | |
| | VARCHAR | | | |
| | TEXT | | | |
| | TINYTEXT | | | |
+------------------------+------------------------+ | | |
| Structured String | ENUM | | | |
| | SET | | | |
+------------------------+------------------------+ | | |
| Temporal | DATE | | | |
| | DATETIME | | | |
| | TIME | | | |
| | TIMESTAMP | | | |
+------------------------+------------------------+---------------------+-------------+---------+
Some remarks:
- The MySQL column data type is a storage data type. Storage types are managed by MySQL storage engines.
- The MYSQL UDF data type is the runtime type of an argument passed to any UDF. This is a dramatically smaller set than the storage data types (of which the UDF has no knowledge)
- The js UDFs use type coercion to turn DECIMAL argument values into REAL argument values, which in turn causes them to end up as (floating point) javascript Numbers. Since this is a conversion from an exact fractional type to a floating point type, this coercion causes possible loss of accuracy. However, since javascript itself does not have a native exact fractional type, the only alternative would be to turn MySQL DECIMAL values into javascript Strings, which would be a severe drawback if one were to use those values in calculation. Since a precision preserving conversion from decimal to string can reasonably easily be done by the caller by casting the DECIMAL values to a CHAR type, and since for many calculations, the possible loss of precision might be acceptable, it was decided that the coercion of DECIMAL to REAL was the lesser of all evils.
- The v8 type is what is used to turn the UDF runtime type into a value accessible to the javascript runtime.
- Within javascript, each argument is either a String or a Number. While javascript has a few more primitive datatypes, there simply is not enough information to convert a value from MySQL land to js land to more specific javascript data types.
- For integral numbers, the value of the argument is examined to determine whether to use v8::Integer or v8::Number. This has no bearing on how the value behaves as seen from javascript land, but it may allow v8 to do more efficient and accurate integer arithmetic