mysql，遍历列名 [英] mysql, iterate through column names

问题描述

我想从MySQL表中获取所有列名，遍历每个列名，然后使用这些列名作为变量运行存储过程.可以起到以下作用的

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

看起来SHOW COLUMNS FROM myTable会给我列名，但是如何将列名放入循环?

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

我真的很想使用本机SQL在存储过程中运行所有这些操作.由于我仍在学习MySQL的复杂性，因此这对我的项目确实很有帮助.感谢您的帮助.

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.

推荐答案

我认为您想要这样的东西:

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;

这篇关于mysql，遍历列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！