EXPLAIN EXTENDED

This is article 4 of 6 on linked lists in MySQL:

• Sorting lists
• Sorting lists: moving items
• Sorting lists: adding items
• Sorting lists: deleting items
• Sorting lists: moving blocks
• Sorting lists: deleting blocks

In this aticle I'll describe deleting an item from a linked list.

This operation is probably the most simple, as no constraints are violated here.

I'll cover it, first, just to be complete and, second, to illustrate some tricks I used before in more detail.

Here's the procedure that deletes an item with a given id:

CREATE PROCEDURE prc_delete(item INT)
BEGIN
        DECLARE _itemparent INT;
        DECLARE _itemchild INT;
        SELECT  (
                SELECT  parent
                FROM    t_list
                WHERE   id = item
                FOR UPDATE
                ),
                (
                SELECT  id
                FROM    t_list
                WHERE   parent = item
                FOR UPDATE
                )
        INTO    _itemparent, _itemchild;
        DELETE
        FROM    t_list
        WHERE   id = item;
        UPDATE  t_list
        SET     parent = _itemparent
        WHERE   id = _itemchild
                AND _itemparent IS NOT NULL;
END

What does it do?

1. In the SELECT statement two things happen: the parent and the child of the item in question are being selected; and the both the item row and its child are locked.

   All this is performed in one statement: first, to be more concise, and, second, to avoid ugly HANDLER FOR NOT FOUND.

   The main query will always select exactly one row, and if there is no either item or a child, it will return NULL to the appropriate variable.

2. In the DELETE statement, the item row is being deleted. If there is no item row (item is NULL or does not exists), then nothing will happen.

3. In the UPDATE statement, the child is being updated to follow the item's parent. Again, if there were no item before DELETE, or it had no child, nothing will happen in the UPDATE statement.

   There is just one more additional check for _itemparent IS NOT NULL. It's included to handle attempts to delete 0. It doesn't exist as an item, but does exist as a surrogate parent. Without this check, first item's parent would be updated to NULL, which is wrong and should not happen.

To be continued.