MySQL
keyboard_arrow_down 295 guides
chevron_leftColumn Cookbooks
check_circle
Mark as learned thumb_up
0
thumb_down
0
chat_bubble_outline
0
Comment auto_stories Bi-column layout
settings
Specifying position of a new column in MySQL
schedule Aug 12, 2023
Last updated local_offer
Tags MySQL
Master the mathematics behind data science with 100+ top-tier guides
Start your free 7-days trial now!
Start your free 7-days trial now!
In MySQL you can specify the position of a new column using the FIRST / AFTER keywords.
Syntax
To add the new column as the first column:
ALTER TABLE table_name ADD COLUMN new_column datatype FIRST;
To add the new column after a particular column:
ALTER TABLE table_name ADD COLUMN new_column datatype AFTER existing_column_name;
Example
Consider the following table showing whether students hold a drivers license:
student_id | full_name | age | has_license |
|---|---|---|---|
1 | Sky Towner | 17 | 0 |
2 | Ben Davis | 19 | 1 |
3 | Travis Apple | 18 | 0 |
4 | Arthur David | 16 | 0 |
5 | Benjamin Town | 17 | 1 |
The above sample table can be created using the code here.
To add a new column attempts as the first column:
ALTER TABLE drivers_license ADD COLUMN attempts INT FIRST;
Query OK, 0 rows affected (0.16 sec)Records: 0 Duplicates: 0 Warnings: 0
To check that attempts has indeed been added as the first column:
DESCRIBE drivers_license;
+-------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+--------------+------+-----+---------+----------------+| attempts | int | YES | | NULL | || student_id | int unsigned | NO | PRI | NULL | auto_increment || full_name | varchar(30) | YES | | NULL | || age | int | YES | | NULL | || has_license | tinyint(1) | YES | | NULL | |+-------------+--------------+------+-----+---------+----------------+
If we were to add the new column attempts after the has_license column:
ALTER TABLE drivers_license ADD COLUMN attempts INT AFTER has_license;
Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
To check that attempts has indeed been added after has_license:
DESCRIBE drivers_license;
+-------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+--------------+------+-----+---------+----------------+| student_id | int unsigned | NO | PRI | NULL | auto_increment || full_name | varchar(30) | YES | | NULL | || age | int | YES | | NULL | || has_license | tinyint(1) | YES | | NULL | || attempts | int | YES | | NULL | |+-------------+--------------+------+-----+---------+----------------+
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Comment
Citation
Ask a question or leave a feedback...
thumb_up
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!
