MySQL and GraalVM

This session shows the support of JavaScript Functions and Stored Procedures in MySQL Enterprise Edition.

This was presented during the MySQL Tech Tour Italia in Roma, in October 2024.

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL MySQL Tech Tour Italia - O obre 2024 MySQL and GraalVM Support for JavaScript Functions and Stored Procedures in MySQL Enterprise Edition
2. Who am I ? about.me/lefred Copyright @ 2024 Oracle and/or its affiliates. | 2
3. @lefred MySQL Evangelist using MySQL since version 3.20 devops believer living in h ps://lefred.be Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 3
4. GraalVM & Polyglot What is GraalVM ? Copyright @ 2024 Oracle and/or its affiliates. 4
5. GraalVM is a high-performance, polyglot virtual machine Supports multiple languages: JavaScript, Python, Ruby, R and more Enhances performance and enables interoperability between languages What is GraalVM? Copyright @ 2024 Oracle and/or its affiliates. 5
6. GraalVM allows us to execute multiple programming languages e ciently on a single runtime. In database contexts, it allows to expand the scope of how we can execute logic beyond SQL. What is GraalVM? (2) Copyright @ 2024 Oracle and/or its affiliates. 6
7. GraalVM allows us to execute multiple programming languages e ciently on a single runtime. In database contexts, it allows to expand the scope of how we can execute logic beyond SQL. For MySQL Enterprise, GraalVM's integration allows JavaScript to be executed natively, o ering developers exibility in de ning stored procedures. What is GraalVM? (2) Copyright @ 2024 Oracle and/or its affiliates. 7
8. Why JavaScript? JavaScript is the rst external language supported in MySQL, why ? Copyright @ 2024 Oracle and/or its affiliates. 8
9. most used languages by developers more than 98% of all web pages use JavaScript it has a huge development eco-system Why JavaScript? JavaScript is the rst external language supported in MySQL, why ? Copyright @ 2024 Oracle and/or its affiliates. 8
10. Why GraalVM in MySQL Enterprise? Copyright @ 2024 Oracle and/or its affiliates. 9
11. Why GraalVM in MySQL Enterprise? Native support for JavaScript as a stored procedure language Enables more complex logic directly in the database Enhanced performance over traditional interpreted approaches No need to retrieve sensitive data from the database for processing Polyglot capabilities (use other languages inside MySQL) traditional MySQL stored procedures language is very limited faster JavaScript execution Copyright @ 2024 Oracle and/or its affiliates. 10
12. validate data process and format JSON & strings operate Data Cleansing / Transformation Why GraalVM in MySQL Enterprise? (2) JavaScript can be used inside MySQL to By processing directly within the server developers can minimize data movement between server and clients, improving performance and reducing network overhead, reducing latency within applications, and improving data security. Copyright @ 2024 Oracle and/or its affiliates. 11
13. Let's start MySQL EE & MLE / ɛm ɛl i/ Copyright @ 2024 Oracle and/or its affiliates. 12
14. Starting with MySQL EE & MLE If you are a MySQL customer, you get JavaScript support since MySQL 9.0. As a developer, you can get free access to MySQL Enterprise from Oracle Technology Network (OTN): h ps://www.oracle.com/mysql/technologies/mysql-enterprise-edition- downloads.html Copyright @ 2024 Oracle and/or its affiliates. 13
15. Starting with MySQL EE & MLE If you are a MySQL customer, you get JavaScript support since MySQL 9.0. As a developer, you can get free access to MySQL Enterprise from Oracle Technology Network (OTN): h ps://www.oracle.com/mysql/technologies/mysql-enterprise-edition- downloads.html To use JavaScript in MySQL, we use the Multilingual Engine Component (MLE). MLE is composed of two les: libpolyglot.so (161M on OL8 x64) component_mle.so (+/- 556K) Copyright @ 2024 Oracle and/or its affiliates. 13
16. Loading MLE We need to load the component if not yet loaded: SQL SQL > > install component install component 'file://component_mle' 'file://component_mle'; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0928 0.0928 sec sec) ) SQL SQL > > select select * * from from mysql mysql. .component component where where component_urn component_urn like like '%mle' '%mle'; ; + +--------------+--------------------+----------------------+ --------------+--------------------+----------------------+ | | component_id component_id | | component_group_id component_group_id | | component_urn component_urn | | + +--------------+--------------------+----------------------+ --------------+--------------------+----------------------+ | | 34 34 | | 20 20 | | file file: ://component_mle | //component_mle | + +--------------+--------------------+----------------------+ --------------+--------------------+----------------------+ 1 1 row row in in set set ( (0.0003 0.0003 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 14
17. Loading MLE - Error log We can also parse the error log for information related to MLE: SQL SQL > > select select * * from from performance_schema performance_schema. .error_log error_log where where SUBSYSTEM SUBSYSTEM= ='MLE' 'MLE'; ; + +----------------------------+-----------+------+------------+-----------+-------------------------------------------+ ----------------------------+-----------+------+------------+-----------+-------------------------------------------+ | | LOGGED LOGGED | | THREAD_ID THREAD_ID | | PRIO PRIO | | ERROR_CODE ERROR_CODE | | SUBSYSTEM SUBSYSTEM | | DATA DATA | | + +----------------------------+-----------+------+------------+-----------+-------------------------------------------+ ----------------------------+-----------+------+------------+-----------+-------------------------------------------+ | | 2024 2024- -09 09- -24 24 21 21: :28 28: :26.146678 26.146678 | | 10 10 | | Note Note | | MY MY- -015000 015000 | | MLE MLE | | Component Component starting starting. .. .. . | | | | 2024 2024- -09 09- -24 24 21 21: :28 28: :26.174638 26.174638 | | 10 10 | | Note Note | | MY MY- -015000 015000 | | MLE MLE | | Component state: INACTIVE Component state: INACTIVE | | | | 2024 2024- -09 09- -24 24 21 21: :33 33: :01.296445 01.296445 | | 10 10 | | Note Note | | MY MY- -015000 015000 | | MLE MLE | | Component state: ACTIVE Component state: ACTIVE | | | | 2024 2024- -09 09- -24 24 21 21: :33 33: :01.296475 01.296475 | | 10 10 | | Note Note | | MY MY- -015000 015000 | | MLE MLE | | Max memory Max memory 1363148800 1363148800 produces produces - -Xmx Xmx= =525 525MB MB | | + +----------------------------+-----------+------+------------+-----------+-------------------------------------------+ ----------------------------+-----------+------+------------+-----------+-------------------------------------------+ 4 4 rows rows in in set set ( (0.0023 0.0023 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 15
18. MLE - Status Variables + +------------------------------+------------+ ------------------------------+------------+ | | Variable_name Variable_name | | Value Value | | + +------------------------------+------------+ ------------------------------+------------+ | | mle_heap_status mle_heap_status | | Allocated Allocated | | | | mle_languages_supported mle_languages_supported | | JavaScript JavaScript | | | | mle_memory_used mle_memory_used | | 23 23 | | | | mle_oom_errors mle_oom_errors | | 0 0 | | | | mle_session_resets mle_session_resets | | 0 0 | | | | mle_sessions mle_sessions | | 1 1 | | | | mle_sessions_max mle_sessions_max | | 1 1 | | | | mle_status mle_status | | Active Active | | | | mle_stored_functions mle_stored_functions | | 1 1 | | | | mle_stored_procedures mle_stored_procedures | | 0 0 | | | | mle_stored_program_bytes_max mle_stored_program_bytes_max | | 33 33 | | | | mle_stored_program_sql_max mle_stored_program_sql_max | | 0 0 | | | | mle_stored_programs mle_stored_programs | | 1 1 | | | | mle_threads mle_threads | | 1 1 | | | | mle_threads_max mle_threads_max | | 1 1 | | + +------------------------------+------------+ ------------------------------+------------+ MLE - Variable + +----------------+------------+ ----------------+------------+ | | Variable_name Variable_name | | Value Value | | + +----------------+------------+ ----------------+------------+ | | mle mle. .memory_max memory_max | | 1363148800 1363148800 | | + +----------------+------------+ ----------------+------------+ SQL SQL > > select select format_bytes format_bytes( (@ @@mle.memory_max @mle.memory_max) ); ; + +--------------------------------+ --------------------------------+ | | format_bytes format_bytes( (@ @@mle.memory_max @mle.memory_max) ) | | + +--------------------------------+ --------------------------------+ | | 1.27 1.27 GiB GiB | | + +--------------------------------+ --------------------------------+ Copyright @ 2024 Oracle and/or its affiliates. 16
19. MLE - UDFs SQL SQL > > select select * * from from performance_schema performance_schema. .user_defined_functions user_defined_functions where where udf_name udf_name like like 'mle%' 'mle%'; ; + +-----------------------+-----------------+----------+-------------+-----------------+ -----------------------+-----------------+----------+-------------+-----------------+ | | UDF_NAME UDF_NAME | | UDF_RETURN_TYPE UDF_RETURN_TYPE | | UDF_TYPE UDF_TYPE | | UDF_LIBRARY UDF_LIBRARY | | UDF_USAGE_COUNT UDF_USAGE_COUNT | | + +-----------------------+-----------------+----------+-------------+-----------------+ -----------------------+-----------------+----------+-------------+-----------------+ | | mle_session_reset mle_session_reset | | char char | | function function | | NULL NULL | | 1 1 | | | | mle_session_state mle_session_state | | char char | | function function | | NULL NULL | | 1 1 | | | | mle_set_session_state mle_set_session_state | | integer integer | | function function | | NULL NULL | | 1 1 | | + +-----------------------+-----------------+----------+-------------+-----------------+ -----------------------+-----------------+----------+-------------+-----------------+ 3 3 rows rows in in set set ( (0.0004 0.0004 sec sec) ) mle_session_state(): obtain session information about the MLE stored program that was most recently executed. mle_session_set(): determine the rules in e ect during the current session for converting MySQL integer types (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT) to JavaScript values Copyright @ 2024 Oracle and/or its affiliates. 17
20. First JavaScript Function Hello World Copyright @ 2024 Oracle and/or its affiliates. 18
21. SQL SQL > > CREATE CREATE FUNCTION FUNCTION hello_world_js hello_world_js( () ) RETURNS RETURNS CHAR CHAR( (12 12) ) DETERMINISTIC DETERMINISTIC LANGUAGE LANGUAGE JAVASCRIPT JAVASCRIPT AS AS $$ $$ return return ( ("Ciao Italia!" "Ciao Italia!") ); ; $$ $$ ; ; Hello World Let's create our rst JavaScript function inside MySQL: Copyright @ 2024 Oracle and/or its affiliates. 19
22. SQL SQL > > CREATE CREATE FUNCTION FUNCTION hello_world_js hello_world_js( () ) RETURNS RETURNS CHAR CHAR( (12 12) ) DETERMINISTIC DETERMINISTIC LANGUAGE LANGUAGE JAVASCRIPT JAVASCRIPT AS AS $$ $$ return return ( ("Ciao Italia!" "Ciao Italia!") ); ; $$ $$ ; ; SQL SQL > > select select hello_world_js hello_world_js( () ); ; + +------------------+ ------------------+ | | hello_world_js hello_world_js( () ) | | + +------------------+ ------------------+ | | Ciao Italia Ciao Italia! ! | | + +------------------+ ------------------+ 1 1 row row in in set set ( (0.0007 0.0007 sec sec) ) Hello World Let's create our rst JavaScript function inside MySQL: Copyright @ 2024 Oracle and/or its affiliates. 19
23. Data - Cleansing and Transformation the Global Intl JavaScript Object Copyright @ 2024 Oracle and/or its affiliates. 20
24. Cleansing Data Stored Programs can be used to clean data in MySQL. Let's see an example for cleaning phone numbers: SQL SQL > > select select * * from from users users; ; + +----+-------+-------------------+ ----+-------+-------------------+ | | id id | | name name | | phone phone | | + +----+-------+-------------------+ ----+-------+-------------------+ | | 1 1 | | Andra Andra | | + +49 49 ( (234 234) ) 567 567- -890 890 | | | | 2 2 | | Luca Luca | | 0049.458 0049.458.696 .696 | | | | 3 3 | | Marco Marco | | boh? boh? | | + +----+-------+-------------------+ ----+-------+-------------------+ 3 3 rows rows in in set set ( (0.0007 0.0007 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 21
25. Cleansing Data - SQL DELIMITER DELIMITER $$ $$ CREATE CREATE FUNCTION FUNCTION CleanPhoneNumbers_sql CleanPhoneNumbers_sql( (phone_in phone_in VARCHAR VARCHAR( (50 50) )) ) RETURNS RETURNS CHAR CHAR( (13 13) ) DETERMINISTIC DETERMINISTIC BEGIN BEGIN DECLARE DECLARE cleaned cleaned VARCHAR VARCHAR( (50 50) ); ; -- Replace the leading '+' with '00' -- Replace the leading '+' with '00' SET SET cleaned cleaned = = IF IF( (LEFT LEFT( (phone_in phone_in, , 1 1) ) = = '+' '+', , CONCAT CONCAT( ('00' '00', , SUBSTRING SUBSTRING( (phone_in phone_in, , 2 2) )) ), , phone_in phone_in) ); ; -- Remove all non-numeric characters -- Remove all non-numeric characters SET SET cleaned cleaned = = REPLACE REPLACE( (REPLACE REPLACE( (REPLACE REPLACE( (REPLACE REPLACE( (REPLACE REPLACE( (REPLACE REPLACE( (REPLACE REPLACE( (REPLACE REPLACE( (REPLACE REPLACE( (REPLACE REPLACE( ( cleaned cleaned, , '-' '-', , '' '') ), , '(' '(', , '' '') ), , ')' ')', , '' '') ), , ' ' ' ', , '' '') ), , '.' '.', , '' '') ), , '+' '+', , '' '') ), , ',' ',', , '' '') ), , '/' '/', , '' '') ), , '\\' '\\', , '' '') ), , ':' ':', , '' '') ); ; -- Return the cleaned phone number if it's 10 or 13 digits, otherwise return NULL -- Return the cleaned phone number if it's 10 or 13 digits, otherwise return NULL IF IF LENGTH LENGTH( (cleaned cleaned) ) = = 10 10 OR OR LENGTH LENGTH( (cleaned cleaned) ) = = 13 13 THEN THEN RETURN RETURN cleaned cleaned; ; ELSE ELSE RETURN RETURN NULL NULL; ; END END IF IF; ; END END $$ $$ DELIMITER DELIMITER ; ; Copyright @ 2024 Oracle and/or its affiliates. 22
26. Cleansing Data - SQL (2) SQL SQL > > update update users users set set phone phone= =CleanPhoneNumbers_sql CleanPhoneNumbers_sql( (phone phone) ); ; Query OK Query OK, , 3 3 rows rows affected affected ( (0.0059 0.0059 sec sec) ) Rows Rows matched matched: : 3 3 Changed: Changed: 3 3 Warnings Warnings: : 0 0 SQL SQL > > select select * * from from users users; ; + +----+-------+---------------+ ----+-------+---------------+ | | id id | | name name | | phone phone | | + +----+-------+---------------+ ----+-------+---------------+ | | 1 1 | | Andra Andra | | 0049234567890 0049234567890 | | | | 2 2 | | Luca Luca | | 0049458696 0049458696 | | | | 3 3 | | Marco Marco | | NULL NULL | | + +----+-------+---------------+ ----+-------+---------------+ 3 3 rows rows in in set set ( (0.0006 0.0006 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 23
27. Cleansing Data - JavaScript CREATE CREATE FUNCTION FUNCTION CleanPhoneNumbers_js CleanPhoneNumbers_js( (phone_in phone_in varchar varchar( (50 50) )) ) returns returns char char( (13 13) ) DETERMINISTIC DETERMINISTIC language language javascript javascript as as $$ $$ let cleaned let cleaned = = phone_in phone_in. .replace replace( (/ /^ ^\ \+ +/ /, ,'00' '00') ); ; cleaned cleaned = = cleaned cleaned. .replace replace( (/ /\D \D/ /g g, , '' '') ); ; return return ( (cleaned cleaned. .length length = == == = 10 10 || || cleaned cleaned. .length length = == == = 13 13) ) ? cleaned : ? cleaned : null null; ; $$ $$ ; ; Copyright @ 2024 Oracle and/or its affiliates. 24
28. Cleansing Data - JavaScript (2) SQL SQL > > update update users users set set phone phone= =CleanPhoneNumbers_js CleanPhoneNumbers_js( (phone phone) ); ; Query OK Query OK, , 3 3 rows rows affected affected ( (0.0037 0.0037 sec sec) ) Rows Rows matched matched: : 3 3 Changed: Changed: 3 3 Warnings Warnings: : 0 0 SQL SQL > > select select * * from from users users; ; + +----+-------+---------------+ ----+-------+---------------+ | | id id | | name name | | phone phone | | + +----+-------+---------------+ ----+-------+---------------+ | | 1 1 | | Andra Andra | | 0049234567890 0049234567890 | | | | 2 2 | | Luca Luca | | 0049458696 0049458696 | | | | 3 3 | | Marco Marco | | NULL NULL | | + +----+-------+---------------+ ----+-------+---------------+ 3 3 rows rows in in set set ( (0.0006 0.0006 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 25
29. Transforming Data - the Intl Javascript Object This JavaScript Object is used to handle a variety of internationalization constructors and other language sensitive functions. Let's have a look at this table: SQL SQL > > select select * * from from users users; ; + +----+-------+---------------+---------+ ----+-------+---------------+---------+ | | id id | | name name | | phone phone | | sales sales | | + +----+-------+---------------+---------+ ----+-------+---------------+---------+ | | 1 1 | | Andra Andra | | 0049234567890 0049234567890 | | 310000 310000 | | | | 2 2 | | Luca Luca | | 0049458696 0049458696 | | 9800000 9800000 | | | | 3 3 | | Marco Marco | | NULL NULL | | 2500 2500 | | + +----+-------+---------------+---------+ ----+-------+---------------+---------+ 3 3 rows rows in in set set ( (0.0007 0.0007 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 26
30. Transformation - Intl Let's create a function to display the amount in the sales column to something easier to read: DROP DROP FUNCTION FUNCTION IF IF EXISTS EXISTS format_sales_js format_sales_js; ; CREATE CREATE FUNCTION FUNCTION format_sales_js format_sales_js( (sales_in sales_in int int) ) returns returns varchar varchar( (30 30) ) DETERMINISTIC DETERMINISTIC language language javascript javascript as as $$ $$ return return new Intl new Intl. .NumberFormat NumberFormat( ('en-US' 'en-US', , { { notation: notation:'compact' 'compact', , maximumSignificantDigits: maximumSignificantDigits: 3 3 } }) ). .format format( (sales_in sales_in) ) $$ $$ ; ; Copyright @ 2024 Oracle and/or its affiliates. 27
31. Transformation - Intl (2) SQL SQL > > select select * *, , format_sales_js format_sales_js( (sales sales) ) from from users users; ; + +----+-------+---------------+---------+------------------------+ ----+-------+---------------+---------+------------------------+ | | id id | | name name | | phone phone | | sales sales | | format_sales_js format_sales_js( (sales sales) ) | | + +----+-------+---------------+---------+------------------------+ ----+-------+---------------+---------+------------------------+ | | 1 1 | | Andra Andra | | 0049234567890 0049234567890 | | 310000 310000 | | 310 310K K | | | | 2 2 | | Luca Luca | | 0049458696 0049458696 | | 9800000 9800000 | | 9.8 9.8M M | | | | 3 3 | | Marco Marco | | NULL NULL | | 2500 2500 | | 2.5 2.5K K | | + +----+-------+---------------+---------+------------------------+ ----+-------+---------------+---------+------------------------+ 3 3 rows rows in in set set ( (0.0070 0.0070 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 28
32. Transformation - Intl (3) But it would be nice to have the possibility to concatenate the currency too: DROP DROP FUNCTION FUNCTION format_sales_js format_sales_js; ; CREATE CREATE FUNCTION FUNCTION format_sales_js format_sales_js( (sales_in sales_in int int) ) returns returns varchar varchar( (30 30) ) DETERMINISTIC DETERMINISTIC language language javascript javascript as as $$ $$ return return new Intl new Intl. .NumberFormat NumberFormat( ('it-IT' 'it-IT', , { { style: style: 'currency' 'currency', , currency: currency: 'EUR' 'EUR', , notation: notation:'compact' 'compact', , maximumSignificantDigits: maximumSignificantDigits: 3 3 } }) ). .format format( (sales_in sales_in) ) $$ $$ ; ; Copyright @ 2024 Oracle and/or its affiliates. 29
33. Transformation - Intl (4) SQL SQL > > select select * *, , format_sales_js format_sales_js( (sales sales) ) from from users users; ; + +----+-------+---------------+---------+------------------------+ ----+-------+---------------+---------+------------------------+ | | id id | | name name | | phone phone | | sales sales | | format_sales_js format_sales_js( (sales sales) ) | | + +----+-------+---------------+---------+------------------------+ ----+-------+---------------+---------+------------------------+ | | 1 1 | | Andra Andra | | 0049234567890 0049234567890 | | 310000 310000 | | 310.000 310.000 € € | | | | 2 2 | | Luca Luca | | 0049458696 0049458696 | | 9800000 9800000 | | 9 9, ,8 8 Mio € Mio € | | | | 3 3 | | Marco Marco | | NULL NULL | | 2500 2500 | | 2500 2500 € € | | + +----+-------+---------------+---------+------------------------+ ----+-------+---------------+---------+------------------------+ 3 3 rows rows in in set set ( (0.0063 0.0063 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 30
34. Transformation - Int: example SQL SQL > > select select * *, , format_sales_js format_sales_js( (sales sales, , 'USD' 'USD', , 'fr-FR' 'fr-FR') ) sales sales from from users users; ; + +----+-------+---------------+---------+---------------+ ----+-------+---------------+---------+---------------+ | | id id | | name name | | phone phone | | sales sales | | sales sales | | + +----+-------+---------------+---------+---------------+ ----+-------+---------------+---------+---------------+ | | 1 1 | | Andra Andra | | 0049234567890 0049234567890 | | 310000 310000 | | 310 310 000 000 $US $US | | | | 2 2 | | Luca Luca | | 0049458696 0049458696 | | 9800000 9800000 | | 9 9 800 800 000 000 $US $US | | | | 3 3 | | Marco Marco | | NULL NULL | | 2500 2500 | | 2 2 500 500 $US $US | | + +----+-------+---------------+---------+---------------+ ----+-------+---------------+---------+---------------+ 3 3 rows rows in in set set ( (0.0019 0.0019 sec sec) ) SQL SQL > > select select * *, , format_sales_js format_sales_js( (sales sales, , 'USD' 'USD', , 'en-US' 'en-US') ) sales sales from from users users; ; + +----+-------+---------------+---------+------------+ ----+-------+---------------+---------+------------+ | | id id | | name name | | phone phone | | sales sales | | sales sales | | + +----+-------+---------------+---------+------------+ ----+-------+---------------+---------+------------+ | | 1 1 | | Andra Andra | | 0049234567890 0049234567890 | | 310000 310000 | | $ $310 310, ,000 000 | | | | 2 2 | | Luca Luca | | 0049458696 0049458696 | | 9800000 9800000 | | $ $9 9, ,800 800, ,000 000 | | | | 3 3 | | Marco Marco | | NULL NULL | | 2500 2500 | | $ $2 2, ,500 500 | | + +----+-------+---------------+---------+------------+ ----+-------+---------------+---------+------------+ 3 3 rows rows in in set set ( (0.0022 0.0022 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 31
35. Datatypes Playing with JSON, Arrays and Vectors Copyright @ 2024 Oracle and/or its affiliates. 32
36. JavaScript and datatypes - JSON Of course JS works perfectly with JSON. We can parse, sort, and play with JSON inside any MLE function created in JavaScript. Let's have a look a the following table: SQL SQL > > select select * * from from italia italia; ; + +----+-----------------------------------------------+ ----+-----------------------------------------------+ | | id id | | user user | | + +----+-----------------------------------------------+ ----+-----------------------------------------------+ | | 1 1 | | { {"city" "city": : "Fara Gera d Adda" "Fara Gera d Adda", , "name" "name": : "Marco" "Marco"} } | | | | 2 2 | | { {"city" "city": : "Milano" "Milano", , "name" "name": : "Andra" "Andra"} } | | | | 3 3 | | { {"city" "city": : "Palermo" "Palermo", , "name" "name": : "Luca" "Luca"} } | | + +----+-----------------------------------------------+ ----+-----------------------------------------------+ 3 3 rows rows in in set set ( (0.0002 0.0002 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 33
37. JavaScript and datatypes - JSON (2) drop drop function function if if exists exists sortArrayByProperty sortArrayByProperty; ; create create function function sortArrayByProperty sortArrayByProperty( (items items varchar varchar( (10000 10000) ), , prop prop varchar varchar( (100 100) )) ) returns returns varchar varchar( (10000 10000) ) DETERMINISTIC DETERMINISTIC language language javascript javascript as as $$ $$ const arr const arr = = JSON JSON. .parse parse( (items items) ) function function compare compare( (a a, ,b b) ){ { if if( (a a[ [prop prop] ] < < b b[ [prop prop] ]) ) return return - -1 1 else else if if ( (a a[ [prop prop] ] > > b b[ [prop prop] ]) ) return return 1 1 return return 0 0 } } return return JSON JSON. .stringify stringify( (arr arr. .sort sort( (compare compare) )) ) $$ $$; ; See h ps://blogs.oracle.com/mysql/post/sorting-json-arrays-in-mysql-with-javascript Copyright @ 2024 Oracle and/or its affiliates. 34
38. JavaScript and datatypes - JSON (3) SQL SQL > > select select json_pretty json_pretty( (sortArrayByProperty sortArrayByProperty( (arr arr, , 'name' 'name') )) ) from from ( (select select JSON_ARRAYAGG JSON_ARRAYAGG( (user user) ) arr arr from from ( (select select user user from from italia italia) ) a a) )b\G b\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * json_pretty json_pretty( (sortArrayByProperty sortArrayByProperty( (arr arr, , 'name' 'name') )) ): : [ [ { { "city" "city": : "Milano" "Milano", , "name" "name": : "Andra" "Andra" } }, , { { "city" "city": : "Palermo" "Palermo", , "name" "name": : "Luca" "Luca" } }, , { { "city" "city": : "Fara Gera d Adda" "Fara Gera d Adda", , "name" "name": : "Marco" "Marco" } } ] ] 1 1 row row in in set set ( (0.0015 0.0015 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 35
39. JavaScript and datatypes - UUIDs We can also create functions to parse MySQL's UUIDs (v1): SQL SQL > > select select now now( () ), , uuid uuid( () ), , from_unixtime from_unixtime( (uuid_to_unixtime_js uuid_to_unixtime_js( (uuid uuid( () )) )) ) uuid_ts uuid_ts; ; + +---------------------+--------------------------------------+----------------------------+ ---------------------+--------------------------------------+----------------------------+ | | now now( () ) | | uuid uuid( () ) | | uuid_ts uuid_ts | | + +---------------------+--------------------------------------+----------------------------+ ---------------------+--------------------------------------+----------------------------+ | | 2024 2024- -09 09- -25 25 15 15: :06 06: :44 44 | | 03 03c07e0e c07e0e- -7 7b3f b3f- -11 11ef ef- -8434 8434- -5 5e1b9081e705 e1b9081e705 | | 2024 2024- -09 09- -25 25 15 15: :06 06: :44.787000 44.787000 | | + +---------------------+--------------------------------------+----------------------------+ ---------------------+--------------------------------------+----------------------------+ 1 1 row row in in set set ( (0.0010 0.0010 sec sec) ) see h ps://github.com/lefred/mysql-graalvm-examples Copyright @ 2024 Oracle and/or its affiliates. 36
40. JavaScript and datatypes - UUIDs (2) Or generate any type of UUIDs: SQL SQL > > select select js_uuidv7 js_uuidv7( () ); ; + +--------------------------------------+ --------------------------------------+ | | js_uuidv7 js_uuidv7( () ) | | + +--------------------------------------+ --------------------------------------+ | | 0192294 0192294d d- -1848 1848- -771 771b b- -3 3aa0 aa0- -2 2a612dc3932e a612dc3932e | | + +--------------------------------------+ --------------------------------------+ 1 1 row row in in set set ( (0.0034 0.0034 sec sec) ) SQL SQL > > select select js_uuidv7 js_uuidv7( () ); ; + +--------------------------------------+ --------------------------------------+ | | js_uuidv7 js_uuidv7( () ) | | + +--------------------------------------+ --------------------------------------+ | | 0192294 0192294d d- -25 25f6 f6- -71 71bc bc- -0113 0113- -a8a8b2cee175 a8a8b2cee175 | | + +--------------------------------------+ --------------------------------------+ 1 1 row row in in set set ( (0.0009 0.0009 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 37
41. JavaScript and datatypes - VECTOR Since MySQL 9.0, the new VECTOR datatype is supported in MySQL. It's again very easy to deal with this new datatype in JavaScript. Let's see how to addition two vectors. Copyright @ 2024 Oracle and/or its affiliates. 38
42. JavaScript and datatypes - VECTOR (2) create create function function vector_addition_js vector_addition_js( (a a varchar varchar( (15000 15000) ), , b b varchar varchar( (15000 15000) )) ) returns returns varchar varchar( (15000 15000) ) deterministic deterministic language language javascript javascript as as $$ $$ const vec1 const vec1 = = JSON JSON. .parse parse( (a a) ) const vec2 const vec2 = = JSON JSON. .parse parse( (b b) ) if if ( (vec1 vec1. .length length != !== = vec2 vec2. .length length) ) { { throw new Error throw new Error( ("Vectors must have the same dimension" "Vectors must have the same dimension") ) } } const result const result = = [ [] ] let i let i = = 0 0 while while ( (i i < < vec1 vec1. .length length) ) { { result result. .push push( (vec1 vec1[ [i i] ] + + vec2 vec2[ [i i] ]) ) i i+ ++ + } } const resultStr const resultStr = = JSON JSON. .stringify stringify( (result result) ) try { try { const parsedResult const parsedResult = = JSON JSON. .parse parse( (resultStr resultStr) ) return return resultStr resultStr } catch } catch ( (error error) ) { { throw new Error throw new Error( ("Invalid vector conversion" "Invalid vector conversion") ) } } $$ $$; ; Copyright @ 2024 Oracle and/or its affiliates. 39
43. JavaScript and datatypes - VECTOR (3) SQL SQL > > select select vector_addition_js vector_addition_js( ('[1,2,3]' '[1,2,3]', ,'[4,5,6]' '[4,5,6]') ); ; + +-----------------------------------------+ -----------------------------------------+ | | vector_addition_js vector_addition_js( ('[1,2,3]' '[1,2,3]', ,'[4,5,6]' '[4,5,6]') ) | | + +-----------------------------------------+ -----------------------------------------+ | | [ [5 5, ,7 7, ,9 9] ] | | + +-----------------------------------------+ -----------------------------------------+ 1 1 row row in in set set ( (0.0053 0.0053 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 40
44. JavaScript and datatypes - VECTOR (3) SQL SQL > > select select vector_addition_js vector_addition_js( ('[1,2,3]' '[1,2,3]', ,'[4,5,6]' '[4,5,6]') ); ; + +-----------------------------------------+ -----------------------------------------+ | | vector_addition_js vector_addition_js( ('[1,2,3]' '[1,2,3]', ,'[4,5,6]' '[4,5,6]') ) | | + +-----------------------------------------+ -----------------------------------------+ | | [ [5 5, ,7 7, ,9 9] ] | | + +-----------------------------------------+ -----------------------------------------+ 1 1 row row in in set set ( (0.0053 0.0053 sec sec) ) Imagination is the only limit ! Copyright @ 2024 Oracle and/or its affiliates. 40
45. DEBUG How to debug JavaScript Stored Functions Copyright @ 2024 Oracle and/or its affiliates. 41
46. MLE JavaScript - debug To debug we use the MLE UDFs we encountered earlier and we add some output to the console in the code like this: SQL SQL > > CREATE CREATE FUNCTION FUNCTION hello_world_js hello_world_js( () ) RETURNS RETURNS CHAR CHAR( (12 12) ) DETERMINISTIC DETERMINISTIC LANGUAGE LANGUAGE JAVASCRIPT JAVASCRIPT AS AS $$ $$ console console. .log log( ('DEBUG: This is a message' 'DEBUG: This is a message') ) console console. .error error( ('ERROR: This is an error' 'ERROR: This is an error') ) return return ( ("Ciao Italia!" "Ciao Italia!") ); ; $$ $$; ; Copyright @ 2024 Oracle and/or its affiliates. 42
47. MLE JavaScript - debug (2) Let's call this JavaScript function again: SQL SQL > > select select hello_world_js hello_world_js( () ); ; + +------------------+ ------------------+ | | hello_world_js hello_world_js( () ) | | + +------------------+ ------------------+ | | Ciao Italia Ciao Italia! ! | | + +------------------+ ------------------+ 1 1 row row in in set set ( (0.0051 0.0051 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 43
48. MLE JavaScript - debug (3) And now we can use mle_session_state() to retrieve the info: SQL SQL > > select select mle_session_state mle_session_state( ('stdout' 'stdout') )\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * mle_session_state mle_session_state( ('stdout' 'stdout') ): DEBUG: This : DEBUG: This is is a message a message 1 1 row row in in set set ( (0.0005 0.0005 sec sec) ) SQL SQL > > select select mle_session_state mle_session_state( ('stderr' 'stderr') )\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * mle_session_state mle_session_state( ('stderr' 'stderr') ): ERROR: This : ERROR: This is is an error an error 1 1 row row in in set set ( (0.0005 0.0005 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 44
49. MLE JavaScript - debug (4) To clear the history, we just need to use mle_session_reset(): SQL SQL > > select select mle_session_state mle_session_state( ('stdout' 'stdout') )\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * mle_session_state mle_session_state( ('stdout' 'stdout') ): DEBUG: This : DEBUG: This is is a message a message DEBUG: This DEBUG: This is is a message a message DEBUG: This DEBUG: This is is a message a message SQL SQL > > select select mle_session_reset mle_session_reset( () ); ; + +------------------------------------------+ ------------------------------------------+ | | mle_session_reset mle_session_reset( () ) | | + +------------------------------------------+ ------------------------------------------+ | | The The session session state state is is successfully reset successfully reset. . | | + +------------------------------------------+ ------------------------------------------+ 1 1 row row in in set set ( (0.0003 0.0003 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 45
50. Resources h ps://github.com/lefred/mysql-graalvm-examples h ps://blogs.oracle.com/mysql/post/mysql-vector-datatype-create-your-operations-part-1 h ps://blogs.oracle.com/mysql/post/a-quick-introduction-to-javascript-stored-programs-in-mysql h ps://blogs.oracle.com/mysql/post/javascript-support-in-mysql-the-uuid-example h ps://blogs.oracle.com/mysql/post/introducing-javascript-support-in-mysql h ps://blogs.oracle.com/mysql/post/debugging-javascript-stored-functions-in-mysql h ps://blogs.oracle.com/mysql/post/sorting-json-arrays-in-mysql-with-javascript h ps://blogs.oracle.com/mysql/post/more-javascript-in-mysql h ps://blogs.oracle.com/mysql/post/using-the-global-intl-javascript-object-in-mysql h ps://blogs.oracle.com/mysql/post/ ltering-json-arrays-with-javascript-in-mysql Copyright @ 2024 Oracle and/or its affiliates. 46
51. Share your ❤ to MySQL #mysql Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 47
52. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 48