mysql，遍历列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4950252/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql, iterate through column names
提问by RyanKDalton
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:
我想从 MySQL 表中获取所有列名，遍历每个列名，然后使用这些列名作为变量运行存储过程。有以下作用：
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 myTablewill give me the column names, but how would I get the column names into a loop?
看起来SHOW COLUMNS FROM myTable会给我列名，但是我如何将列名放入循环中？
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.
我真的很想使用本机 SQL 在存储过程中运行所有这些。由于我仍在学习 MySQL 的复杂性，这对我的项目很有帮助。谢谢你的帮助。
回答by user470714
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;
回答by Ned Batchelder
You can write a query against information_schema to get the column names:
您可以针对 information_schema 编写查询以获取列名：
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.
然后就像表中的任何数据一样返回列名。
