Changing multiple column names in a mysql table

Question

I have many columns in a table with a name starting with field_t and I have to change that to field_c

For example, here is the ALTER TABLE statement for changing the name of one of the columns:

ALTER TABLE my_table CHANGE field_t_class field_c_class longtext;

How can I change all the columns that follow this pattern instead of doing it in a one by one basis?

score 5 · Accepted Answer · answered Jan 25 '12 at 12:18

You can generated the ALTERs like this

SELECT
    CONCAT(
           'ALTER TABLE ', C.TABLE_NAME, ' CHANGE ', 
           C.COLUMN_NAME, ' ', REPLACE(C.COLUMN_NAME, 'field_t', 'field_c')
           )
FROM
    INFORMATION_SCHEMA.COLUMNS C
WHERE
    C.COLUMN_NAME LIKE 'field[_]t[_]%';

You'll also need to append DATA_TYPE etc and based on this CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME and COLLATION_NAME...

score 2 · Answer 2 · answered Jan 25 '12 at 13:06

2

Get 1st column names having name like 'field_t%';

select C.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS C            
WHERE
C.TABLE_NAME='YourTableName' AND C.COLUMN_NAME like 'field_t%';

Then make string of renaming column names like:

Make rename_string as:

rename_string= "RENAME COL1 to ReCol1
     RENAME COL2 to ReCol2"

Then do :

ALTER Table YourTableName {rename_string};

answered Jan 25 '12 at 13:06

Somnath Muluk

55,015
38
216
226

I can't see support for this in ALTER TABLE http://dev.mysql.com/doc/refman/5.5/en/alter-table.html However, if it works, it is cleaner then mine because the datatype etc isn't needed – gbn Jan 25 '12 at 15:21
1

@gbn: Yes sir, it is working. I have tested before on machine then itself I have posted answer. – Somnath Muluk Jan 26 '12 at 08:05

Changing multiple column names in a mysql table

2 Answers2