How can I save the result of a query in an array using the MySQL stored procedure?


I want that when executing this query

SELECT tabla1.campo1 
            From tabla1, tabla2 
            Where tabla1.campo1 = tabla2.campo1 
            Group By tabla1.campo1;

Which will give me for example 600 records, save the 600 records in an array to later use the index of the array (from 1 to 600) as a comparison in another query, depending on whether I use an array and a for loop, but investigate and I do not find a clear example, I would like to know the following things:

Can I do what I want?

Is it efficient in terms of resources?

Is there a better method?

asked by El Cóndor 05.08.2016 в 20:40

1 answer


Although MySQL does not have a data type ARRAY , I think that what you want is to use the result of your query later, without having to be writing it again and again. This is possible: you can save the result in a temporary table, and use it as many times as you want:

-- Cambié el JOIN implícito (tu condición WHERE) por un JOIN explícito, por claridad
create temporary table temp_resultado
    SELECT tabla1.campo1 
    FROM tabla1 INNER JOIN tabla2 ON tabla1.campo1 = tabla2.campo1
    GROUP BY tabla1.campo1;
-- No olvides generar un índice:
ALTER TABLE temp_resultado
    ADD INDEX idx_campo1(campo1);

Now, if you need to assign a number to each record, you can use user variables:

create temporary table temp_resultado
    SELECT @n := @n + 1 as n
         , tabla1.campo1 
    FROM (select @n := 0) as init, (tabla1 INNER JOIN tabla2 ON tabla1.campo1 = tabla2.campo1)
    GROUP BY tabla1.campo1;
-- No olvides generar un índice:
ALTER TABLE temp_resultado
    ADD INDEX idx_campo1(campo1);

Remember: Temporary tables are only visible for the connection that creates them, and are destroyed once the connection that creates them is closed.


As you can see, I'm not using a stored procedure to generate the table you need, but I'm generating "on the fly" the row numbers you need.

How does it work?

SELECT @n := @n + 1 as n
     , tabla1.campo1 
FROM (select @n := 0) as init
   , (tabla1 INNER JOIN tabla2 ON tabla1.campo1 = tabla2.campo1)
GROUP BY tabla1.campo1;

In the clause FROM I include your two tables, and a subquery (which I call init ) to initialize the variable @n . Remember that in SQL the "order of execution" is:

  • FROM : The server checks that the data sources exist and can be read; In addition, the required relationships are established
  • WHERE : The relevant filters are applied to the data sources
  • GROUP BY : The data is grouped
  • SELECT : Fields are read and expressions are calculated (including aggregation expressions
  • HAVING : The relevant filters are applied to the data after if they have been grouped
  • So, taking advantage of the fact that the first thing that is executed is the FROM , I put there a query of variable initialization ( init ). The variable @n is initialized to zero, and each time the SELECT reads a row, it updates the value of @n , adding it 1.

    Finally, when creating a temporary table, you guarantee that the row numbers are not updated every time you execute the query (since you are not ordering the values, there is no guarantee that an order will be respected in the rows). / p>

    Once you execute the previous queries, you can use these values simply by selecting all the rows of the newly created table:

    select * from temp_resultado order by n;

    Now, I see that you are not using any aggregation function; I think then it's easier to use SELECT DISTINCT :

    drop table if exists temp_resultado; -- Si existe la tabla, bórrala
    create temporary table temp_resultado
        select distinct @n := @n + 1 as n, tabla1.campo1
        from (select @n := 0) as init
           , (tabla1 inner join tabla2 on tabla1.campo1 = tabla2.campo1)
           -- Si el campo que usas para relacionar las tablas tiene el mismo nombre,
           -- puedes usar:
           -- (tabla1 inner join tabla2 using (campo1))
        order by tabla1.campo1; -- Generalmente es prudente especificar 
                                -- el orden de los registros
    -- No olvides agregar los índices:
    alter table temp_resultado
        add unique index idx_n(n),
        add index idx_campo1(campo1);
    -- Muestra el resultado
    select * from temp_resultado;

    Why is it better "on the fly" than with a procedure?

    If you want to try a stored procedure, consider the following:

  • You need a table to save the data
  • You need to insert the data in that table
  • You need to go through that table row by row to add the value you want (although you could use a value AUTO_INCREMENT in the definition of the table, it would be better if the procedure stored the number better)
  • For mere economy of keys, you should generate the values "on the fly". On the other hand, when generating the values "on the fly" using temporary variables, you are not really giving the server too much extra work.

    answered by 05.08.2016 / 20:50