MySQL provides us with a few utility functions that act on JSON values, or strings that can be parsed as JSON values.
Below are three JSON utility functions that we can use for such tasks.
Ian
How to Add Headings for WITH ROLLUP in MySQL
MySQL provides us with the WITH ROLLUP modifier for the GROUP BY function for generating super aggregate data.
But by default, this modifier doesn’t label its data. By this I mean, it doesn’t provide us with meaningful labels for the rows that it generates. It simply uses NULL in those rows.
Maybe you’ve encountered this and are now looking for a solution.
Below is a handy little technique we can use to replace NULL with our own headings for these rows.
Fix “WRONGTYPE Operation against a key holding the wrong kind of value” when using ZINCRBY in Redis
If you get an error that reads “WRONGTYPE Operation against a key holding the wrong kind of value” when using the ZINTER command in Redis, it’s probably because you’re passing a key with the wrong data type.
To fix this issue, be sure that the key you pass to the ZINTER command holds a sorted set.
Using the sys.format_statement() Function in MySQL to Truncate a SQL Statement
In MySQL, the sys.format_statement() system function reduces the length of a given string/SQL statement to the length stored in the statement_truncate_len configuration option.
This function can be handy for truncating potentially long SQL statements retrieved from Performance Schema tables into a more suitable length (64 characters by default).
How to Create a CHECK Constraint Against a JSON Column in MySQL
When we create or modify a table in MySQL, we have the option of applying a CHECK constraint against one or more columns. This allows us to check the data before it enters the database. Data can only enter the database if it doesn’t violate the rules in our CHECK constraint.
If the column is a JSON column, we have the option of creating a CHECK constraint that checks that the JSON document doesn’t violate its JSON schema. To do this, we can include the schema in the CHECK constraint.
Understanding the PS_THREAD_ID() Function in MySQL
In MySQL, we can use the PS_THREAD_ID() function to get the Performance Schema thread ID assigned to a given connection ID. This can be useful whenever we use a function that takes the thread ID as its parameter, but we only know the connection ID.
If no thread ID exists for the connection, then NULL is returned.
The built-in PS_THREAD_ID() function does the same thing as the now deprecated sys.ps_thread_id() function. Therefore, we can call the function without needing to qualify it with sys or making sys our current schema.
Understanding INFORMATION_SCHEMA in SQL
Behind every well-structured and organised SQL database lies a treasure trove of information called metadata. Metadata is data that provides information about other data. In relational database management systems, this metadata can typically include information like the names of databases, tables, columns, the data type of a column, access privileges, and much more.
The SQL standard provides a collection of tables/views that allow us to view metadata about the objects in the database. The SQL standard refers to this as the information schema and calls it INFORMATION_SCHEMA.
Fix “column … can only be updated to DEFAULT” in PostgreSQL
If you’re getting an error in PostgreSQL that tells you that a column “…can only be updated to DEFAULT” with detail that explains that it “…is an identity column defined as GENERATED ALWAYS“, it’s probably because you’re trying to update an identity column with your own value, but the identity column was created with the GENERATED ALWAYS option.
Using the sys.format_path() Function to Dynamically Replace a Subpath with its Equivalent System Variable in MySQL
In MySQL, the sys.format_path() function is a stored function in the sys schema. It accepts a path name, and returns the modified path name after replacing subpaths that match the values of certain system variables.
So we can use the function to dynamically replace part or all of our path with a system variable that matches that path segment.
Understanding the sys.extract_table_from_file_name() Function in MySQL
In MySQL, the sys.extract_table_from_file_name() function is a stored function in the sys schema.
It accepts a path name, and returns the path component that represents the table name.