Modern approaches to replacing accumulation user-defined variable hacks, via MySQL 8.0 Window functions and CTEs
A common MySQL strategy to perform updates with accumulating functions is to employ user-defined variables, using the
UPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol)) pattern.
This pattern though doesn’t play well with the optimizer (leading to non-deterministic behavior), so it has been deprecated. This left a sort of void, since the (relatively) sophisticated logic is now harder to reproduce, at least with the same simplicity.
In this article, I’ll have a look at two ways to apply such logic: using, canonically, window functions, and, a bit more creatively, using recursive CTEs.
- Requirements and background
- The problem
- The old-school approach
- Modern approach #1: Window functions
- Modern approach #2: Recursive CTE