I have something like this in a trigger:

OPEN cursor_things;
        loop_through_rows : LOOP
            FETCH cursor_things INTO things;

            IF done THEN
                LEAVE loop_through_rows;
            END IF ;
        END LOOP;
 CLOSE cursor_things;

Where the SQL is :

DECLARE cursor_things CURSOR FOR
    SELECT id
    FROM things
    WHERE thing.id = OLD.id;

The problem is that the SQL returns multiple rows and the variable gets overwritten. Is there a way to store multiple values in the variable? Alternatively, is there a better way of doing this?

EDIT: A cursor may not be the best solution. Originally I was just trying a variable:

If we have books and authors tables. Authors have written many books. I want to get all the ids from the books table

I just want to be able to do this:

 INSERT INTO changes (`action`, `data`)
    VALUES ('changed',
            JSON_OBJECT(
                    'author', NEW.author,
                    'book_ids', #how do I get these ids
            )
  );

So maybe with something like this:

SET bookIds = (SELECT JSON_ARRAYAGG( book_id) from books WHERE author_id = 13);

and with a bit more detail, what I actually need is:

JSON_OBJECT(
                    'author', NEW.author,
                    'books', [
                    [related book1 id, related book1 type],
                    [related book2 id, related book2 type]
            )

CodePudding user response：

For the record, no, it is not possible to store multiple values in a scalar variable.

I do think there is a better way to accomplish what you describe. Most cases of using a CURSOR in a MySQL procedure can be done in a single query and no loop. For example:

INSERT INTO changes (`action`, `data`)
SELECT 'changed', JSON_OBJECT()
  'author', NEW.author,
  'books', JSON_ARRAYAGG(
             JSON_OBJECT(
               'book_id', id,
               'book_type', book_type
             )
           )
FROM books
WHERE author_id = NEW.author;