In MySQL, as in other databases, in addition to adding and retrieving data, you can change the structure of tables. One such possibility is to add a new column or columns to an existing table. To add new columns, there is a special command – ALTER TABLE ADD COLUMN.
Rotation can also be set as parameters, ie choose after which existing columns in the table you want to add new ones. This article will show you how to add a column to a version MySQL table on Ubuntu 20.04.
How to add a column to a MySQL table
To add new columns to a table, MySQL has a special command – ALTER TABLE ADD COLUMN . Its syntax is given below:
ALTER TABLE table_name ADD COLUMN new_column_name data_type [FIRST|AFTER existing_column_name];
First, the name of the table to which the new column will be added is specified. The name comes immediately after the ALTER TABLE command . Next, after the ADD COLUMN directive , you must specify the name of the new column that will be created and set its data type. The keyword COLUMN can be omitted, it is not required. Also, as additional parameters, you can specify the position of the new column in the table. If you specify FIRST , then the column will be created at the very beginning of the table, you can also specify the location after a specific column (provided that it is present in the table) for this you need to enter the AFTER keywordand specify the name of an already existing column in the table.
As an example, a table named personal_information will be created with a single column named id. The command to create a table with one column is given below:
Let’s make sure that the column was successfully created and is present in the table. To do this, you need to execute the DESCRIBE command, passing it the table name as a parameter:
In addition to displaying all columns in a table, the DESCRIBE command also displays the data types of the columns and the presence of primary and foreign keys.
Adding one column to a table
In order to add a column to a MySQL table without specifying an order (if there is no order, the newly created column will be placed at the very end of the table, in this example the new column named name will be after the column named id) you need to run the following command:
Adding multiple columns to a table
To add two or more columns, you must use the same ALTER TABLE ADD COLUMN command, while listing this argument separated by commas. In the example below , 2 new columns named last_name and city will be added to the table named personal_information , since the position of the columns was not specified, the columns will be added to the end of the table. The command will look like this:
Adding a column to the beginning of a table
If there is a need to add a column to the very beginning of the table, this can be done by adding the FIRST option to the ALTER TABLE ADD COLUMN command . First, let’s display all the columns of the table named personal_information:
Now we need to add a new column called customer_id which should come before the id column . The command to add this column would look like this:
As you can see in the screenshot above, a new column called customer_id has been added at the very beginning (the first column was called id).
Adding a column after a specific column
It is also possible to create a MySQL column after a specific column. As an example, a new column named country should be added after the last_name column (see table structure below):
In order to add a new column after a certain column, you must add the AFTER parameter, followed by the name of an already present column in the table. The command will look like this:
The newly created country column has been added after the last_name column (see screenshot above).
Findings
In this short article, we have seen how to add a column to a MySQL table. In addition, such addition possibilities have been described as adding columns at the very beginning of the table and after a specific column. Do you have experience with MySQL DBMS? Tell in the comments!