MySQL How to Auto Increment Primary Key
Creating a primary key that auto increments for a MySQL database is easy. Here is a very basic example showing how you can set this up.
CREATE TABLE food ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) );
You can insert a record by omitting the primary key like this.
INSERT INTO food (name) VALUES ('chocolate'),('spinach'),('sushi'), ('nutrient pill'),('ice cream'),('steak');
If you specify a value, it will be used. Also, when you do this the sequence will be reset. The highest value in the column will be used as a new position for the sequence. The value after that will be used for the next automatically generated value.
INSERT INTO food (id,name) VALUES(100,'roast duck'); INSERT INTO food (id,name) VALUES(NULL,'coffee'); SELECT * FROM food;
Assigning a null value will result in a sequence number being generated:
INSERT INTO food (id,name) VALUES(NULL,'bread');
If you specify 0 the actual value will be auto generated. This is the case unless NO_AUTO_VALUE_ON_ZERO is set.
INSERT INTO food (id,name) VALUES(0,'sushi');