User variables, even though not part of standard SQL, are great objects in MySQL. They allow to keep some “state” for the life of a session: a session can execute a statement to compute a value, store that value in a user variable, and use it in all next statements.…
[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.…