Creating a table in MySQL with an auto increment is straightforward. The value is stored as part of the table definition and updated in that location. Creating a table with an auto_incrementing id in MySQL can be done as shown below.
CREATE TABLE t1 ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) AUTO_INCREMENT=1 ;
Postgres tracks this separately in the database with a “sequence.” You can stand up a new table using the “SERIAL” keyword to denote a type of auto_increment for a field, but behind the scenes, it creates a sequence for the table and tracks the integer in that sequence table. Below is an example of a sequence in Postgres.
test=# \d No relations found. test=# CREATE TABLE t1 ( id SERIAL PRIMARY KEY ); CREATE TABLE test=# \d List of relations Schema | Name | Type | Owner --------+-----------+----------+---------- public | t1 | table | postgres public | t1_id_seq | sequence | postgres (2 rows) test=# \x Expanded display is on. test=# select * from t1_id_seq; -[ RECORD 1 ]-+-------------------- sequence_name | t1_id_seq last_value | 1 start_value | 1 increment_by | 1 max_value | 9223372036854775807 min_value | 1 cache_value | 1 log_cnt | 0 is_cycled | f is_called | f test=# \x Expanded display is off.
As you can see, a sequence is generated from the use of the SERIAL data type. This sequence lists the last value, the start value, how much to increment by and many other values. If we were to pg_dump this database, we will see the below information regarding the sequence.
bash-4.2$ pg_dump test ... CREATE TABLE t1 ( id integer NOT NULL ); ALTER TABLE public.t1 OWNER TO postgres; -- -- Name: t1_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE t1_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.t1_id_seq OWNER TO postgres; -- -- Name: t1_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres -- ALTER SEQUENCE t1_id_seq OWNED BY t1.id; ...
We can tell by the Postgres output that it is possible to change the values for “INCREMENT BY” for this individual sequence. In MySQL changing the amount the integer increments by is done at a global level with auto_increment_increment and auto_increment_offset. The increment by and offset values can be important to pay attention to when setting up multi-master replication, or when you are bringing data from multiple write masters back to a central server. Utilizing offsets when managing sequences or incrementing values can help to avoid key collisions when data is replicated or copied between servers.