How to DROP a column from a table in MySQL using a column number

Question

I am trying to remove a column from a MySQL table that I am using with Handsontable. When I remove a column from the Handsontable, I can get the column's index using the afterRemoveCol() callback:

afterRemoveCol: function (index, amount) {
    alert(index +'  amount: '+amount);
}

I would like to remove the column using the column number (n) returned by this callback function from the MySQL table using something like:

ALTER TABLE tbl_Blah DROP COLUMN n;

So, if I want to drop column #3 from the MySQL table, How would I do this using just the column number?

Answer 1

To add to RMathis answer, you can do everything within SQL by also using SET to define the DROP string in conjuntion with PREPARE and EXECUTE

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`col1` varchar(1), 
     `col2` varchar(1), 
     `col3` varchar(1), 
     `col4` varchar(1), 
     `col5` varchar(1))
;

set @col = (select column_name 
            from information_schema.columns 
            where table_name='table1' and ordinal_position=3);

SET @s = CONCAT("alter table table1 drop column ", @col);

PREPARE stmt FROM @s;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Query 1:

desc table1

Results:

| COLUMN_NAME | COLUMN_TYPE | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA |
|-------------|-------------|-------------|------------|----------------|-------|
|        col1 |  varchar(1) |         YES |            |         (null) |       |
|        col2 |  varchar(1) |         YES |            |         (null) |       |
|        col4 |  varchar(1) |         YES |            |         (null) |       |
|        col5 |  varchar(1) |         YES |            |         (null) |       |