How to get column name by index using MySQL?

Question

How can I get a column name by its index using sql query in command line?

I expect something like show columns from my_table where col_index = 2;

I found many answers about how to show all columns but there's no answer on how to get exact column name by its position in the table.

@scaisEdge since it represents the column position then yes :) — Maxim Gotovchits, Sep 24 '16 at 16:02
I don't think this is a duplicate guys. He wants 1 column name coming back. Even though he wrote it confusingly imo — Drew, Sep 25 '16 at 05:57

score 5 · Accepted Answer · edited Sep 24 '16 at 16:31

5

use table information_schema.columns

select column_name 
from information_schema.columns 
where table_name = 'my_table_name' and ordinal_position = 2;

edited Sep 24 '16 at 16:31

Drew

answered Sep 24 '16 at 16:02

Fujiao Liu

Thx, that works :) – Maxim Gotovchits Sep 24 '16 at 16:03
1

glad it works :-) – Fujiao Liu Sep 24 '16 at 16:04
Warning: if your database contains more than one schema and each can contain the same table name the above will return more than one row. You can refine the query: `... where table_name = 'my_table_name' and table_schema='my_schema_name' and ordinal_position = 2` – JonP Feb 08 '18 at 14:26

score 0 · Answer 2 · answered Sep 24 '16 at 16:04

0

You can select from INFORMATION_SCHEMA.COLUMNS and use limit

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table'
limit 1, 1;

answered Sep 24 '16 at 16:04

Lajos Arpad

2 Answers2