In recent months, there had been some really exciting news from the MySQL team:
These two SQL standard language features are among the most powerful SQL features that are available from most other databases. I frequently include them in conference talks about SQL (
see my article about 10 SQL Tricks That You Didn’t Think Were Possible), and as well in the
Data Geekery SQL Masterclass. With MySQL 8.0 now supporting these exciting features, the masterclass will be including MySQL as well (along with Oracle, SQL Server, PostgreSQL, and DB2). And, of course, these features are now supported in the upcoming jOOQ 3.10 as well.
Want to try it out yourself? Just run:
docker pull mysql:8.0.2
docker run --name MYSQL802 --net=host -p 3306:3306 -e MYSQL_ROOT_PASSWORD=test -d mysql:8.0.2
Then, connect to this instance and run this nice little query in it:
WITH RECURSIVE t(a, b) AS (
SELECT 1, CAST('a' AS CHAR(15))
UNION ALL
SELECT t.a + 1, CONCAT(t.b, 'a')
FROM t
WHERE t.a < 10
)
SELECT a, SUM(a) OVER (ORDER BY a) AS ∑, b
FROM t
And get this result:
a ∑ b
--------------------------
1 1 a
2 3 aa
3 6 aaa
4 10 aaaa
5 15 aaaaa
6 21 aaaaaa
7 28 aaaaaaa
8 36 aaaaaaaa
9 45 aaaaaaaaa
10 55 aaaaaaaaaa
Would you believe this is MySQL?
Bonus
A nice “hidden” feature is the support of new pessimistic locking clauses, in particular
FOR UPDATE SKIP LOCKED
. This has been available in Oracle for ages and since recently in PostgreSQL as well, and now in MySQL. A very useful feature when implementing simple message queues or reservation systems. More details in this article here:
MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows
Of course,
SKIP LOCKED
(and
NOWAIT
) will be supported in jOOQ 3.10 as well.
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
Hi, thank you for sharing.
How do you express this query in jooq ?
Like this:
The usual static imports are assumed here:
Note, there’s a known issue to get this working on MySQL: https://github.com/jOOQ/jOOQ/issues/6431
The variables a and b are not defined in the code sample. How to define them so that they reference t?
I’ve edited my comment with an example