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?
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:
SKIP LOCKED (and
NOWAIT) will be supported in jOOQ 3.10 as well.
Source link https://blog.jooq.org/2017/07/24/jooq-3-10-supports-exciting-mysql-8-0-features/