As correctly noted by Charlieface, this is a Gaps and Islands problem. Another way of solving this specific variation – also involving a window function, though a different one this time – would go like this:
, DATEDIFF(Date, '1970-01-01') - ROW_NUMBER() OVER (ORDER BY Date ASC) AS PartID
the correct error message is
Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger
SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute ...
This is a type of gaps-and-islands problem, of which there are a number of solutions.
Here is one:
We can identify the starting points of each island by using LAG to check the previous row (with a default for the first row)
We can then number the islands using a running COUNT
Then simply group by that number
WITH StartingPoints AS (
WITH RECURSIVE -- calendar generation needs in recuirsion
-- generate calendar, DATE() performs parameter checking additionally
calendar AS ( SELECT DATE(@start) created_at
SELECT created_at + INTERVAL 1 DAY
WHERE created_at < DATE(@end) ),
-- collect currencies list
In general, a mapping table does not need its own id. Instead, the PRIMARY KEY should be a composite key with the 2 (or more) column-ids referencing the tables that are being related in a many-to-many relationship.
For a 1-to-many relationship, a mapping table is unnecessary.
Your "DLB" smells like a "Relation", hence the above comments ...