Question 113446 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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…

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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);

  2. 0 
    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`;   |
+--------------------------------------------------------------------------------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search