mysql, iterate through column names

Question

I would like to get all of the column names from a MySQL table, loop through each column name and then run a stored procedure using those column names as a variable. Something to the effect of:

colnames = get column names from table

for each colname
  if something changed then
    do something
  else
    do something else

It looks like SHOW COLUMNS FROM myTable will give me the column names, but how would I get the column names into a loop?

I would really like to run all of this in a stored procedure using native SQL. Since I'm still learning the intricacies of MySQL, and this would really help out my project. Thanks for your help.

Answer 1

I think you want something like this:

DECLARE col_names CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  ORDER BY ordinal_position;


select FOUND_ROWS() into num_rows;

SET i = 1;
the_loop: LOOP

   IF i > num_rows THEN
        CLOSE col_names;
        LEAVE the_loop;
    END IF;


    FETCH col_names 
    INTO col_name;     

     //do whatever else you need to do with the col name

    SET i = i + 1;  
END LOOP the_loop;

Answer 2

You can write a query against information_schema to get the column names:

SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  ORDER BY ordinal_position

The column names are then returned just as any data from a table would be.