In MySQL, we can use the PS_CURRENT_THREAD_ID() function to get the Performance Schema thread ID assigned to the current connection.
The PS_CURRENT_THREAD_ID() function was added in MySQL 8.0.16, and it does the same thing as PS_THREAD_ID(CONNECTION_ID()).
In MySQL, format_pico_time() is a Performance Schema function that converts a numeric Performance Schema latency or wait time in picoseconds into a human-readable format. It returns a string consisting of a value and a units indicator.
In MySQL, the DEFAULT() function returns the default value for a given column in the database.
If the column doesn’t have a default value, an error is returned.
Continue readingThe following table contains a full list of the aggregate functions in MySQL.
Continue readingIn MySQL, the GROUPING() function allows us to identity which rows have been generated by the WITH ROLLUP modifier of the GROUP BY clause.
By default, WITH ROLLUP outputs NULL as the value that represents all aggregate values. By this, I mean it doesn’t provide us with a nice easy to read label. It simply outputs NULL. This makes it more difficult for us to distinguish between normal rows and super aggregate rows that were generated by WITH ROLLUP.
In MySQL, list_add() is a system function that adds a value to a comma-separated list of values and returns the result.
So it’s a handy function that allows us to append a value to a list.
Continue readingIn MySQL, list_drop() is a system function that removes a value from a comma-separated list of values and returns the result.
In MySQL, ANY_VALUE() is an aggregate function that allows us to include nonaggregated columns in the SELECT list when using the GROUP BY clause.
The ANY_VALUE() function is commonly used to resolve issues that arise when a query includes columns that are not part of the GROUP BY clause or aggregate functions. It can be useful in scenarios where we want to include descriptive columns in a grouped result set without affecting the grouping behaviour.
MySQL provides us with a bunch of functions and operators that allow us to search JSON documents for various things, such as specific values, paths, keys, etc.
Here are seven functions and three operators that search JSON documents in MySQL.
Continue readingIn MySQL, the VAR_SAMP() function returns the sample variance of an expression. The denominator is the number of rows minus one.
If there are no matching rows, or if the expression is NULL, VAR_SAMP() returns NULL.
VAR_SAMP() is an aggregate function, and so it can be used with the GROUP BY clause.
The VAR_SAMP() function can also be used as a window function.