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.

Claybourne

I just want to write this as native SQL from within MySQL workbench. I was planning to use the column name as part of an UPDATE query. – Claybourne

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;

Overmodest

That looks like exactly what I am after. Thanks so much! The first SELECT statement works, however, I am getting hung up with a syntax error when I add "DECLARE col_names CURSOR FOR". Any thoughts as to why? – Claybourne

OK! Turns out I just needed to change the delimiter and declare the num_rows, i, col_name and it works great. Thanks so much! – Claybourne

And also needs to open col_names;. – Laflam

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.

Dzoba

That's an excellent start to give me the column names! How would I put those into a variable (list?) to iterate over those, though? – Claybourne