Replace all column names in a table if a column name has a space, change it to an underscore-CodePudding

I have a few columns in multiple tables in a project that I've inherited where the column names are like "Owner ID", "Contractor ID", "Phone Number" and many more like them. Is there a way to just go through tables like this and change the names so they're like "owner_id", "contractor_id" and so on? I've been doing this manually with few alter statements but it's takes a lot of time...

CodePudding user response：

mysql> show create table mytable;

CREATE TABLE `mytable` (
  `Owner ID` int DEFAULT NULL,
  `Contractor ID` int DEFAULT NULL,
  `Phone Number` varchar(20) DEFAULT NULL
)

mysql> select concat('alter table `', table_schema, '`.`', table_name,
  '` rename column `', column_name, '` to `', replace(column_name, ' ', '_'), '`;') as _sql 
  from information_schema.columns where locate(' ', column_name);

 -------------------------------------------------------------------------------- 
| _sql                                                                           |
 -------------------------------------------------------------------------------- 
| alter table `test`.`mytable` rename column `Contractor ID` to `Contractor_ID`; |
| alter table `test`.`mytable` rename column `Owner ID` to `Owner_ID`;           |
| alter table `test`.`mytable` rename column `Phone Number` to `Phone_Number`;   |
 --------------------------------------------------------------------------------

CodePudding user response：

By using @Bill Karwin's answer, if you just want to test it in a specific table first, something like this will work:

select concat('alter table `mytable` rename column `', column_name, '` to `', replace(lower(column_name), ' ', '_'), '`;') as _sql 
  from (select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'mytable') as mt where locate(' ', column_name);