MySQL is known to every developer – who hasn’t heard of this DBMS? Some use the database management system in college assignments, some use them to build forum software (ever heard of phpBB? MyBB? vBulletin? IPBoard? They’re all MySQL-based), and some even use the relational database management system to build data breach search engines (spoiler alert – the BreachDirectory data breach search engine allows you to check whether your account is at risk of identity theft or not by allowing you to search through a huge trove of data breaches.)
However, what may not be known to everyone who works with MySQL is the fact that MySQL offers a wide range of interesting functions developers can choose to employ – some of those functions are used daily by developers and DBAs who don’t even think twice about using them, but some make you think twice – “wh.. what is this? Is this one really necessary?” This is why today we’re digging further into them!
The Functions Available Within MySQL
Have a look through the functions and operators list provided by MySQL – it’s pretty extensive, isn’t it? MySQL offers a bunch of functions across a wide variety of areas including, but not limited to:
Sadly, in this blog we won’t walk you through all of them – but read this blog until the end and you will have a very good understanding of what the functions within MySQL entail!
Interesting Functions in MySQL
Full-Text Search Functions
Some of the most interesting functions within the database management system are related to searching for data and performing read-based operations: and read-based operations are nothing without wildcards.
Wildcards in MySQL have two forms – they can take the form of a percentage (“%”) sign, or they can also represent a wildcard (“*”) – the former is used by almost everyone who knows at least something about MySQL, while the latter is used by those who want to play with full-text searching operations.
Those operations look like so:
1
SELECT * FROM [your_table] WHERE MATCH(column) AGAINST (‘Text*’ IN BOOLEAN MODE);
Say, if we would like to find values starting from Lond and ending with anything, we’d need to run a query just like the following:
Do note that the IN BOOLEAN MODE modifier is necessary for any wildcard-based queries to run (that’s not the case if full-text indexes are not in place.)
MySQL version 5.7 also has a bug where if you have a lot of data with “@” signs (think email addresses and the like), have a full-text index on the column and run a search query on the table just like the one we’ve provided above, chances are you’d crash the entire MySQL server because the query would never complete. Why? Noone knows... (That’s a confirmed bug within MySQL!)
JSON-based Search Functions
Take a look at these queries:
We’ve set two variables – one of them a JSON array, another – a number. Then we’ve used the JSON_CONTAINS function to figure out whether the number in the second variable exists within the JSON array of the first variable – neat, huh?
And that’s the crux of the JSON_CONTAINS function within MySQL – it allows us to provide a JSON array, then search for something within the array.
Performance Schema Functions
Starting from MySQL 8.0.16, MySQL also includes a couple of functions that can be used to read data from the performance schema – these functions would likely mostly be used by advanced DBAs, but they’re interesting nonetheless. MySQL offers four functions related to the performance schema:
The first one helps us format bytes to a readable value and would likely be used the most frequently, the second one allows us to format time in picoseconds (e.g. nanoseconds) to a more readable time (e.g. minutes), the third one provides the current thread ID that’s assigned to a connection ID, and the last one provides just the thread ID. Here’s what the usage of the FORMAT_PICO_TIME function would look like (do note that DbVisualizer doesn’t recognize the function because we’re not running MySQL 8 or above):
Window Functions
MySQL also has these so-called “window” functions that perform operations on rows. Such operations include GROUP BY and other operations according to two key concepts:
Queries using window functions look like so:
1
SELECT SUM(customers) AS total_customers FROM users;
2
3
SELECT * FROM sales ORDER BY product, user_country;
You get the point – these are not very complex. A window function is any function that matches both aforementioned concepts. That’s it!
Invisible Functions
Contrary to a popular belief, MySQL also has a couple of functions that are intended to be used only by the server and not the client (the user.) In other words, certain functions do exist, but attempts to invoke them will provoke an error by MySQL:
Why does MySQL even have these functions? The answer is plain and simple – to make the server’s work possible. Hey, MySQL has to use some help, right?
Such functions include, but are not limited to:
MySQL does offer other functions and the full list of internal functions inside of MySQL can be seen here.
Summary
In this blog, we’ve walked you through some of the most interesting functions within MySQL as well as its brothers and sisters Percona Server and MariaDB. Some of these functions include functions that are a necessity for everyone who searches for data, some of them help with performance optimization, while some are only made to work internally.
Take a look through our blog to learn more about database behavior, and until next time!
