In MySQL, we can use the -> operator to extract data from a JSON document.
We provide the column that contains the JSON document to the left of the operator, and the path to the value that we want to extract to the right.
The -> operator is an alias for the two-argument form of the JSON_EXTRACT() function.
In MySQL, the MEMBER OF() operator tests whether or not a value is a member of a given JSON array.
It returns true (1) if the array contains the value, and false (0) if it doesn’t.
The MEMBER OF() operator was added in MySQL 8.0.17.
In MySQL, JSON_OVERLAPS() is a function that tests whether or not two JSON documents have any key-value pairs or array elements in common.
The function returns true (1) if the documents have any key-value pairs or array elements in common, and false (0) if they don’t.
The JSON_OVERLAPS() function was added in MySQL 8.0.17.
In MySQL, JSON_MERGE() is a deprecated function that merges two or more JSON documents and returns the result.
It was deprecated in MySQL 8.0.3 and is subject to removal in a future release.
Fortunately, the JSON_MERGE_PRESERVE() function was added in MySQL 8.0.3 as a synonym for JSON_MERGE(), and it therefore does the same thing that JSON_MERGE() does/did.
Therefore, instead of using JSON_MERGE(), use JSON_MERGE_PRESERVE() instead.
Alternatively, you can use JSON_MERGE_PATCH(), which performs an RFC 7396 compliant merge of two or more JSON documents, without preserving members having duplicate keys.
In MySQL, we can use the SHOW CREATE TABLE statement to generate a CREATE TABLE script for existing tables. This enables us to recreate the table without having to manually type out the table’s definition.
In MySQL, JSON_TABLE() is a built-in function that converts JSON data into a relational form.
In other words, it lets you return a JSON document as a table.
The JSON_TABLE() function was introduced in MySQL 8.0.
In MySQL, CAST() is a built in function that converts a value to another data type. It takes a value of one type and returns a value of the specified type.
You provide the value as an argument when you call the function, as well as the type that you’d like it converted to.
CAST() works similar to CONVERT(), except that the syntax used is slightly different.
If you’re getting error 1298 that reads something like “ERROR 1298 (HY000): Unknown or incorrect time zone: ‘UTC’” in MySQL, it’s probably because you’re specifying a time zone name, but your MySQL installation hasn’t yet been configured for named time zones.
To fix this issue, be sure that your MySQL installation has been configured for named time zones.
Continue readingIn MySQL, the LAST_INSERT_ID() function returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.
It can also be called with an argument, in which case, it returns the value of the expression and the next call to LAST_INSERT_ID() will return the same value.
Below are seven functions we can use to extract the hours, minutes, seconds, and microseconds from a time value in MySQL.
Continue reading