Understanding the SERIAL Type in MySQL

If you work with MySQL databases, you may have encountered the SERIAL type in a table’s definition. And if you’ve come over from PostgreSQL, you might have a false expectation about how MySQL’s SERIAL works – unless you already know 😉

Below is a quick overview of MySQL’s SERIAL type, including an explanation of how it differs from PostgreSQL’s SERIAL type and similar functionality from other DBMSs.

What is the `SERIAL` Type in MySQL?

In MySQL, SERIAL is not exactly a true data type but rather a convenient alias for a combination of BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. It’s used to generate sequential, automatically incrementing numbers for a column, typically intended for use as a primary key.

When you define a column as SERIAL, MySQL treats it as an unsigned BIGINT (which is an 8-byte integer with a range of 0 to 18,446,744,073,709,551,615) with the AUTO_INCREMENT attribute, meaning the value will automatically increase with each new row inserted. It also ensures the column cannot contain NULL values and that it is unique.

Example of `SERIAL` in MySQL

Here’s an example of creating a table that has a SERIAL column defined:

CREATE TABLE Orders (
    OrderID SERIAL,
    CustomerID INT,
    OrderDate DATE,
    PRIMARY KEY (OrderID)
);

In this example:

The OrderID column is defined as SERIAL, and so it will be automatically assigned sequential values starting from 1.
OrderID is also defined as the PRIMARY KEY of the table, so it uniquely identifies each row.

Once we’ve run the above code, when we use SHOW CREATE TABLE to view the table’s definition, we can see the full definition of the SERIAL column:

SHOW CREATE TABLE Orders;

Output:

+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                    |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Orders | CREATE TABLE `orders` (
  `OrderID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `CustomerID` int DEFAULT NULL,
  `OrderDate` date DEFAULT NULL,
  PRIMARY KEY (`OrderID`),
  UNIQUE KEY `OrderID` (`OrderID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can see that the definition for the OrderId column is bigint unsigned NOT NULL AUTO_INCREMENT. In fact, the word SERIAL is nowhere to be found.

When to Use `SERIAL` vs. `AUTO_INCREMENT` in MySQL?

While SERIAL is a shortcut, using AUTO_INCREMENT is more flexible. You can apply AUTO_INCREMENT to any integer column, and it can be paired with any integer data type (like INT, BIGINT, MEDIUMINT, etc), not just BIGINT like in SERIAL.

Use cases for SERIAL:

When you need a simple auto-incrementing BIGINT column and don’t want to manually specify all the attributes.
When the size of your table might grow beyond the limits of INT, so you prefer the larger BIGINT.

Use cases for AUTO_INCREMENT:

When you need more control over the column type (e.g., using INT instead of BIGINT to save storage space).
When you need to set the starting value or step for auto-increment (e.g., you can set the first value to 1000 or increment by 5).

Differences Between `SERIAL` and `AUTO_INCREMENT`

Data Type: SERIAL is always BIGINT, while AUTO_INCREMENT can be applied to any integer type (e.g., TINYINT, INT, BIGINT, etc).
Syntax: SERIAL is a shorthand for defining an auto-incrementing BIGINT column with additional constraints, while AUTO_INCREMENT must be explicitly paired with a column type.
Flexibility: AUTO_INCREMENT gives you more flexibility because it can be used with different integer types and allows for customization of starting points and increments.

How Does `SERIAL` in MySQL Compare to Other DBMSs?

Other DBMSs have similar functionality, in that they provide us with an easy way to get an incrementing value. That said, there are differences from DBMS to DBMS. Here’s a quick comparison with a few of the major DBMSs:

PostgreSQL: In PostgreSQL, SERIAL is a true data type, which can be used with SMALLINT, INT, or BIGINT. It implicitly creates a sequence object behind the scenes to handle auto-incrementing behavior. In contrast, MySQL does not create a separate sequence object for SERIAL; it directly uses the AUTO_INCREMENT mechanism. PostgreSQL also offers BIGSERIAL, which behaves like MySQL’s SERIAL, mapping to a BIGINT with a sequence, as well as SMALLSERIAL, which maps to a SMALLINT with a sequence. The default starting value and increment can be customized more easily in PostgreSQL using sequences.
Oracle: Oracle doesn’t have a SERIAL type, but auto-increment behavior can be mimicked using sequences and triggers. From Oracle 12c onward, there is an IDENTITY column feature that functions similarly to AUTO_INCREMENT in MySQL.
SQL Server: SQL Server also lacks a SERIAL type but offers IDENTITY, which provides similar functionality to AUTO_INCREMENT in MySQL.

When Not to Use `SERIAL`

While SERIAL in MySQL provides us with a concise way to define an auto-incrementing column, there are times you might not want to use it. For example:

When you need finer control over the starting value or increment behavior, in which case you might prefer AUTO_INCREMENT.
When storage space is a concern, and you don’t need the full range of BIGINT. Using SERIAL will always default to BIGINT, even if INT would suffice for your use case.

Applying `SERIAL` to Other Integer Types

When I said that SERIAL is limited to just BIGINT, maybe that was only half true. MySQL also offers the SERIAL DEFAULT VALUE syntax which allows us to apply SERIAL attributes to other integer types.

SERIAL DEFAULT VALUE is an alias for NOT NULL AUTO_INCREMENT UNIQUE.

So we could do the following for example:

CREATE TABLE Orders (
    OrderID INT SERIAL DEFAULT VALUE,
    CustomerID INT,
    OrderDate DATE,
    PRIMARY KEY (OrderID)
);

Output:

+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                        |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Orders | CREATE TABLE `orders` (
  `OrderID` int NOT NULL AUTO_INCREMENT,
  `CustomerID` int DEFAULT NULL,
  `OrderDate` date DEFAULT NULL,
  PRIMARY KEY (`OrderID`),
  UNIQUE KEY `OrderID` (`OrderID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can see that the definition for the OrderID column is int NOT NULL AUTO_INCREMENT.

So while it’s not quite as concise as using SERIAL, it does allow us to leverage its functionality for other integer types.

What is the SERIAL Type in MySQL?

Example of SERIAL in MySQL

When to Use SERIAL vs. AUTO_INCREMENT in MySQL?

Differences Between SERIAL and AUTO_INCREMENT

How Does SERIAL in MySQL Compare to Other DBMSs?

When Not to Use SERIAL

Applying SERIAL to Other Integer Types