How to Change the Column Name in MYSQL

Save

Sometimes a MySQL database designer does not know the appropriate column name for data until after he has designed and implemented a table. Designers may use placeholder names while they design the table’s data structure and relationships and wait until the end to complete its interface by assigning appropriate column names. In other situations, what seemed like a good name initially may no longer make sense as a database grows or once external software must access and update the data. Fortunately, MySQL incorporates simple to use SQL commands that allow for this needed flexibility.

Things You'll Need

  • MySQL database server
  • Open a terminal window (Unix, Linux or Mac OS X) or command line (Windows) and start a MySQL client shell at the prompt. You must start the shell as a user with privileges to create, alter and drop databases and tables. What you enter at the prompt may look like any of the following, depending on how you or your systems administrator have configured the MySQL server and user accounts:

    mysql -u someusername -p

    or

    mysql -u someusername

    or simply

    mysql

  • Create a sample database to experiment on by entering the following at the MySQL prompt:

    CREATE DATABASE sample
    GO

    Select the database:

    USE sample
    GO

    And create a table in it:

    CREATE TABLE people_table ( id INT NOT NULL PRIMARY KEY, name VARCHAR(25), last_name VARCHAR(25));

  • Enter the command:

    DESCRIBE people_table
    GO

    You will see a formatted display of the people_table schema with column names listed in the first column.

  • Change the second column’s name by entering this command:

    ALTER TABLE people_table CHANGE COLUMN name first_name VARCHAR(25)
    GO

    Changing a column name is straightforward; the syntax is:

    ALTER TABLE table_name CHANGE [COLUMN] old_column_name new_column_name data_definition
    GO

    The brackets indicate that the COLUMN statement is optional.

  • View the altered table again using the DESCRIBE function:

    DESCRIBE people_table
    GO

    The new column name will be visible in the list of names in the first column displayed.

  • Now, alter multiple column names simultaneously by entering the following:

    ALTER TABLE people_table CHANGE COLUMN first_name name_one VARCHAR(25), CHANGE COLUMN last_name name_two VARCHAR(25)
    GO

    When changing multiple column names the COLUMN statement is required, otherwise the syntax is identical to changing a single column. Each CHANGE COLUMN clause must be separated by a comma.

  • View the most recent changes with DESCRIBE:

    DESCRIBE people_table
    GO

    Now, both column names listed in the left hand column have the new names name_one and name_two.

  • When finished experimenting, delete the sample database with the following command:

    DROP DATABASE sample;

References

  • MySQL Documentation
  • "Learning MySQL, 1st Edition"; Seyed M.M. Tahaghoghi, Hugh E. Williams; 2006
  • Photo Credit deep in database image by .shock from Fotolia.com
Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!