In MariaDB and MySQL, cursors create a temporary table.
Does this statement deserve a whole blog post? Apparently not. However, in some cases one does not expect a temporary table to be created:
SELECT ... FOR UPDATE: An exclusive lock is created, yes, but you still read data from a temporary table.
SELECT FROMa temporary table: you are reading from a temporary tables, yes, but an internal temporary table is created anyway.
A quick example:
CREATE TEMPORARY TABLE t ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ); CREATE PROCEDURE p() BEGIN DECLARE c CURSOR FOR SELECT id FROM t WHERE 0 LIMIT 0 FOR UPDATE; OPEN c; CLOSE c; END; MySQL [test]> SHOW STATUS LIKE 'Created_tmp_tables'; +--------------------+-------+ | Variable_name …