MySQL Version
MySQL 8.0
Table Schema
CREATE TABLE users(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
-- Query OK, 0 rows affected
The result is:
> desc users
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | <null> | auto_increment |
| name | varchar(255) | NO | | <null> | |
+-------+--------------+------+-----+---------+----------------+
Add a column to the last field
ALTER TABLE users ADD COLUMN new_column1 VARCHAR(255);
The result is:
> desc users;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | <null> | auto_increment |
| name | varchar(255) | NO | | <null> | |
| new_column1 | varchar(255) | YES | | <null> | |
+-------------+--------------+------+-----+---------+----------------+
Add a column to the first field
ALTER TABLE users ADD COLUMN new_column1 VARCHAR(255) FIRST;
The result is:
> desc users
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| new_column1 | varchar(255) | YES | | <null> | |
| id | int(11) | NO | PRI | <null> | auto_increment |
| name | varchar(255) | NO | | <null> | |
+-------------+--------------+------+-----+---------+----------------+
Add a column after another column
ALTER TABLE users ADD COLUMN new_column1 VARCHAR(255) AFTER id;
The result is:
> desc users
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | <null> | auto_increment |
| new_column1 | varchar(255) | YES | | <null> | |
| name | varchar(255) | NO | | <null> | |
+-------------+--------------+------+-----+---------+----------------+
What about “before”?
Let’s use BEFORE instead of AFTER.
ALTER TABLE users ADD COLUMN new_column1 VARCHAR(255) BEFORE id;
-- (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEFORE id' at line 1")
It causes syntax error!
We can’t use BEFORE for ADD COLUMN statement.