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

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.

Update

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:

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:

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.