mysql，遍历列名

2021-11-20 00:00:00 sql mysql

我想从 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.

推荐答案

我想你想要这样的:

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;

相关文章