Adding a New Column in MySQL and Setting Up Auto-Numbering

This time I needed to add a new column to a table in a database running on MySQL and set up auto-numbering, so here's a summary of that process.

First, check the table's current structure.

show columns from robin.PurchaseList;

The current table structure is as follows.

Field	Type	Null	Key	Default	Extra
ASIN	varchar(10)	NO	PRI	NULL
SKU	varchar(255)	NO	PRI	NULL
PurchaseDatetime	datetime	YES		NULL
Brand	varchar(255)	YES		NULL
Title	varchar(255)	YES		NULL
Model	varchar(255)	YES		NULL
LocalCurrency	varchar(10)	YES		NULL
Quantity	double	YES		NULL
LocalPrice	double	YES		NULL
Price	double	YES		NULL
shipped_num	double	YES		NULL
seller_id	varchar(14)	NO		NULL
CreateDate	datetime	NO		CURRENT_TIMESTAMP
UpdateDate	datetime	NO		CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP

Next, add the new column `stock_id` at the beginning.

ALTER TABLE PurchaseList ADD stock_id INT FIRST;

The values of the new column 'stock_id' are all NULL, so we fill in sequential numbers.

The following query assigns sequential numbers to every row.

set @n:=0;
update PurchaseList set stock_id=@n:=@n+1;

Reset 'stock_id' as the primary key.

ALTER TABLE PurchaseList DROP PRIMARY KEY, ADD PRIMARY KEY (stock_id);

After that, add the new column `shipped_num` after `Price`.

ALTER TABLE PurchaseList ADD shipped_num double AFTER Price;

Next, configure auto-increment on `stock_id`.

ALTER TABLE PurchaseList MODIFY stock_id INT NOT NULL AUTO_INCREMENT;

With this, the changes to the PurchaseList table are complete.

Finally, actual data operations were performed. The query to display the five most recent records is below.

SELECT stock_id, SKU, ASIN FROM PurchaseList ORDER BY stock_id DESC LIMIT 5;

We could confirm that sequential numbers are set.

Finally, let's check that auto-numbering works for new data.

INSERT INTO PurchaseList (ASIN, SKU) VALUES ('test', 'test');
SELECT stock_id, SKU, ASIN FROM PurchaseList ORDER BY stock_id DESC LIMIT 5;

We could confirm that auto-numbering works.

As cleanup, delete the test data and we're done.

DELETE FROM PurchaseList WHERE ASIN='test' AND SKU='test';

That's the flow for adding an auto-numbering column in MySQL. Please use it as a reference for keeping your systems running smoothly.

Recent Posts