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');