CTEs and Recursive CTEs appeared in MySQL 8.0; first in a Labs release and now in the official release 8.0.1. Several blogs have been published: here, here and here ; my colleague Øystein also wrote about how using a CTE made one DBT3 query run twice faster.…
[Update of April 2017: the features discussed here are now also available in the official release 8.0.1 of MySQL.]
[Note: this is the first post in a series; there is also a second post].
The MySQL development team just published a Labs release of the MySQL Server (available under “MySQL Server 8.0.0 Optimizer”).…
Correctness of data comes in different forms. One is referential integrity, also known as foreign keys. Another is CHECK constraints. While MySQL supports foreign keys, it doesn’t support CHECK constraints. Today we will look at three ways to emulate them:
- generated columns (new in MySQL 5.7)
This is also relevant to another SQL feature: DOMAIN, which, in short, is a user-defined type composed of a base type (INT, CHAR, …), a default value, and a constraint on acceptable values – the latter being some simple sort of CHECK constraint.…
If you have used SQL a bit, you are certainly familiar with so-called set functions or aggregate functions COUNT, SUM, AVG, described in the manual. For example, let’s say that I am the owner of a shop and I keep track of daily sales in this table:
create table sales (month int, day int, amount int);
The first column is the number of the month, between 1 and 12, the second column is the number of the day in the month, between 1 and 31, and the third column is how much we sold on that date.…
After seeing that several blogs discuss storage of UUID values into MySQL, and that this topic is recurrent on forums, I thought I would compile some sensible ideas I have seen, and also add a couple new ones.
Say we have a table of users, and each user has a UUID.…
This is a follow-up post to my recent announcement of
only_full_group_by improvements in 5.7, where I’d like to address some tricky corner cases where GROUP BY contains non-column expressions (functions).
In the SQL standard, GROUP BY should contain only table columns and never expressions.…
When the MySQL server receives a SELECT query, the query goes through several consecutive phases:
- parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
- resolution: the output of the parsing stage contains names of columns and names of tables.