Showing entries 1 to 2
Displaying posts with tag: cursors (reset)
MySQL/MariaDB cursors and temp tables

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 FROM a temporary table: you are reading from a temporary tables, yes, but an internal temporary table is created anyway.
  • Impossible WHERE and LIMIT 0.

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 …
[Read more]
MariaDB/MySQL Cursors: a brief Tutorial

Versione italiana

In MariaDB and MySQL, Cursors can only be used within a Stored Program, are slow, and have very limited functionalities. That said, they can still be useful in some cases. This page explains how to use them in action, with a trivial example.

Here is our example Stored Procedure which uses a Cursor:

DELIMITER ||
    
DROP TABLE IF EXISTS `test`.`tab1`;
CREATE TABLE `test`.`tab1` (`c` TINYINT UNSIGNED);
INSERT INTO `test`.`tab1` (`c`) VALUES
    (1),
    (2),
    (3);
    
DROP PROCEDURE IF EXISTS `test`.`demo`;
CREATE PROCEDURE `test`.`demo`()
    READS SQL DATA
BEGIN
    DECLARE `eof` BOOL;
    DECLARE `val` INT;
    
    DECLARE `crs` CURSOR FOR
        SELECT `c`
            FROM `test`.`tab1`;
    
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND
        SET eof = TRUE;
    
    OPEN …
[Read more]
Showing entries 1 to 2