MySQL Assistance - Renaming Multiple Columns That Match A Specific Criteria
-
I can do this in php, but I would like to do this only in MySQL
We need to rename ~155 something columns in one table, and I was trying to find the best way to accomplish this. My thoughts at the moment are to query the information_schema to obtain a list of column names matching a specific criteria. My research has pointed me to using a cursor holding the results of the information_schema, and attempting to loop through the cursor. I am by no means a MySQL expert, and the syntax is too confusing for me too follow. Does anyone know of a way to do this? Any guidance will be very appreciated. Looking at you DBAs out there O_o
I am matching on "comments" and will be replacing it with "assessments"
Format of column names
day(1-5)_comments_a(0-31)There are also some columns named day(1-5)_comments_a(25,29,30,31)_s0. I can perform these changes manually, but I would to make 1 SQL file to perform all of these changes (To coincide with code deployment)
-
You'll want to use standard SQL syntax for this. A command similar to this:
ALTER TABLE mytable CHANGE columnold columnnew INT
-
@scottalanmiller said:
You'll want to use standard SQL syntax for this. A command similar to this:
ALTER TABLE mytable CHANGE columnold columnnew INT
This is basically it. Just make a script and save it. Really not enough columns to fret over..
-
@JaredBusch Right. For 37 columns, I would do them by hand. In the MySQL client you can "up arrow" to recall the last line typed in. It is as easy to type the column pairs that way as into any other form of into. Whole thing should only take five minutes.
-
Sorry that should say 155 columns.
-
-
I exported a list by querying the information_schema. I know there are other ways I could do this:
- Export the table and use replace function in the file. Then recreate the table
- Code the logic in PHP and make the changes via loop
Part of this is curiosity/learning, part of it is wanting to be able to make these changes quickly, part is for future use since this comes up sometimes if the DB columns were not named properly initially. Ideally, I would like this to be all contained within one .sql file that can be ran automatically during deployment.
