Using the Global `Intl` JavaScript Object in MySQL
As I mentioned in some previous posts, MySQ: HeatWave and MySQL Enterprise support writing stored functions using JavaScript. When I started playing around with this feature, I wanted to know how much support there was for global JavaScript objects. There is a lot of support for these, and in this post, we will talk about using the global Intl
object.
About Intl
If you have never used the Intl
object in JavaScript, check out the documentation on MDN. This object can be used to handle a variety of internationalization. I will use the NumberFormat()
method for this demo to return a “compact” formatted number. I mean that instead of returning a value of 4999
, it will return 5k
. This formatting can be useful when you don’t need the exact number. Take a look at the image below that is taken from the MySQL Shorts playlist page. It shows the rounded number of views in a compact format.
The Data
Before we start with the function definition, let’s create a table and populate it with some data we can use for this demo. We will use a table named scores
using the following statement:
This statement creates a simple table with three columns:
id
, which is the primary keyname
, which is the name of a playerscore
, which is the player’s score
We are going to populate the table using the following data:
Let’s look at the data sorted by the value of score
in descending value.
We can see that Kate has the highest score, while Scott has the lowest.
Let’s assume we have a requirement to return the value of score
in a format that matches 53k
instead of 53000
and 1m
instead of 1000000
.
Creating a JavaScript-Based Stored Function
As I noted above, Intl
can handle this requirement with the NumberFormat()
method much easier than we could using a function defined using SQL. Here is the code to create the function. I’ll break this down into more manageable chunks below.
The first line of our function definition tells MySQL we are creating a function named compactNumberFormat()
. This function accepts a single argument named num
, which is the int
data type.
The second line of our function definition specifies that we are returning a varchar(256)
value and that the language will be javascript
. We want to return varchar
because our result may include text such as 'K
or M
. Lastly, we use $$
to delimit the body of our function from the rest of the statement. Using this method, we do not need to change the delimiter to get MySQL to interpret the function correctly.
The body of the function consists of a chained method call. We return the value from Intl.NumberFormat().format()
. The arguments passed to NumberFormat()
are:
en-US
- the locale for which we want our number to be formatted.- A JSON object that contains:
notation
- we set this tocompact
to get a format similar to65K
instead of65000
.maximumSignificantDigits
- the maximum number of digits appearing in our result. This includes any values to the right of a decimal point.
We then call the format()
method and pass in the number we wish to format.
The last line of our function includes $$
to tell MySQL we are done defining our function.
Using the Function
We can use this function the same way we can use any other MySQL function. We are going to use it in a simple select statement.
This query gives us the following result:
Notice that some returned values have a decimal (45,1M
), and some do not (534K
). This behavior is due to the maximumSignificantDigits
property.
I know that Intl
can be used on the front end to accomplish the same result, but I am a fan of letting the database do what it is intended to do: manipulate data. Returning this value from the database in the correct format can streamline any frontend code displaying this data.
You can learn more about the functionality of Intl
by checking out the documentation.
Wrap-up
By using JavaScript to create stored functions and procedures, we can tap into many of its features—including global objects such as Intl
. This allows us to provide formatted data directly from the database without needing to format it on the front end of an application. In future posts, I plan on exploring more use cases for using JavaScript to create stored functions and using global JavaScript objects inside these functions.
Photo by Nareeta Martin on Unsplash