How do I add a column after a specific column in MySQL?

---

You can use the following methods in MySQL to add a column to a table after a specific existing column:

Method 1: Add One New Column After Specific Column

ALTER TABLE athletes
  ADD COLUMN rebounds INT NOT NULL AFTER team;

This particular example adds an integer column named rebounds after the column named team in the table named athletes.

Method 2: Add Multiple New Columns After Specific Column

ALTER TABLE athletes
  ADD COLUMN assists INT NOT NULL AFTER team,
  ADD COLUMN rebounds INT NOT NULL AFTER team,
  ADD COLUMN steals INT NOT NULL AFTER team;

This particular example adds three new integer columns named assists, rebounds and steals after the column named team in the table named athletes.

Note: The new columns will be added to the table in the reverse order that they are specified. For example, steals will be added directly after team, then rebounds will be added, then assists will be added.

The following example shows how to use each of these methods in practice with the following table named athletes that contains information about various basketball players:

-- create table 
CREATE TABLE athletes (
  athleteID INT PRIMARY KEY,
  team TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Warriors', 14);
INSERT INTO athletes VALUES (0003, 'Nuggets', 37);
INSERT INTO athletes VALUES (0004, 'Lakers', 19);
INSERT INTO athletes VALUES (0005, 'Celtics', 26);

-- view all rows in table
SELECT * FROM athletes;

Output:

+-----------+----------+--------+
| athleteID | team     | points |
+-----------+----------+--------+
|         1 | Mavs     |     22 |
|         2 | Warriors |     14 |
|         3 | Nuggets  |     37 |
|         4 | Lakers   |     19 |
|         5 | Celtics  |     26 |
+-----------+----------+--------+

Let’s jump in!

Example 1: Add One New Column After Specific Column in MySQL

We can use the following syntax to add one new column named rebounds directly after the existing team column:

-- add rebounds column directly after team column
ALTER TABLE athletes
  ADD COLUMN rebounds INT NOT NULL AFTER team;

-- view all rows in updated table
SELECT * FROM athletes;

Output:

+-----------+----------+----------+--------+
| athleteID | team     | rebounds | points |
+-----------+----------+----------+--------+
|         1 | Mavs     |        0 |     22 |
|         2 | Warriors |        0 |     14 |
|         3 | Nuggets  |        0 |     37 |
|         4 | Lakers   |        0 |     19 |
|         5 | Celtics  |        0 |     26 |
+-----------+----------+----------+--------+

Notice that the new column named rebounds has been added directly after the team column in the table.

Example 2: Add Multiple New Columns After Specific Column in MySQL

We can use the following syntax to add three new columns after the existing team column:

-- add three new columns after team column
ALTER TABLE athletes
  ADD COLUMN assists INT NOT NULL AFTER team,
  ADD COLUMN rebounds INT NOT NULL AFTER team,
  ADD COLUMN steals INT NOT NULL AFTER team;

-- view all rows in updated table
SELECT * FROM athletes;

Output:

+-----------+----------+--------+----------+---------+--------+
| athleteID | team     | steals | rebounds | assists | points |
+-----------+----------+--------+----------+---------+--------+
|         1 | Mavs     |      0 |        0 |       0 |     22 |
|         2 | Warriors |      0 |        0 |       0 |     14 |
|         3 | Nuggets  |      0 |        0 |       0 |     37 |
|         4 | Lakers   |      0 |        0 |       0 |     19 |
|         5 | Celtics  |      0 |        0 |       0 |     26 |
+-----------+----------+--------+----------+---------+--------+

Notice that three new integer columns named steals, rebounds and assists have been added directly after the team column in the table.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL: