Adding a Column to a MySQL Database

To add a column to a MySQL database, you can use the following steps:

1. Start the text-based MySQL client
   
   $ mysql -u testacct -p
   
   The -u testacct parameter specifies that the client should be started using the account named testacct, while the -p parameter indicates that the system should prompt you for the password.



2. At the mysql> prompt, enter the command use dbname;, where dbname is the database name. If you don't know the name of the database, you can see a list of available databases with the show databases; command.



3. Use the alter table command to modify the appropriate table. If you need to see a list of tables in the database, you can use the show tables; command. E.g. to add a column, delivered, which will hold a delivery date for a shipment, to the table requests, you could use the command below:
   
   ALTER TABLE requests ADD delivered DATE;
   
   That would put the new column at the end of the existing columns. If you want to add the column after a specific column, you can specify that column with AFTER colname. E.g., suppose I wish to add the column delivered after the column orderdate. I could use the command below:
   
   ALTER TABLE requests ADD delivered DATE AFTER orderdate;
   
   If you don't know the names of the existing columns, you can use the command SHOW COLUMNS FROM dbname;. E.g., if the table is named requests, I could use the command below:
   
   mysql> show columns from requests;

References:

1. Add a column to an existing MySQL table
   Created: February 8, 2004
   Updated: July 17, 2004
   tech-recipes

[/software/database/mysql] permanent link