Mysql iterating over column names

Question

I would like to get all the column names from the MySQL table, skip each column name and then start the stored procedure using these column names as a variable. Sort 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 column names, but how would I get the column names in a loop?

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

Answer 1

I think you need 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 to information_schema to get the column names:

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

Column names are then returned in the same way as any data from the table.