If you’ve ever encountered a SERIAL column in a MySQL database, you might’ve wondered how it differs from an AUTO_INCREMENT column. After all, they both generate values that automatically increment?

Read on to find out what the difference is between SERIAL and AUTO_INCREMENT in MySQL.

What is AUTO_INCREMENT?

In MySQL, the AUTO_INCREMENT attribute can be used to generate a unique identity for new rows. When we define a column, we can use AUTO_INCREMENT to create an automatically incrementing column.

In other words, whenever we insert a new row into the table, MySQL will automatically generate a value for that column.

What is SERIAL?

The SERIAL type is actually an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

Therefore, we can either define our column as BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, or we can simply use SERIAL.

So we can see that the SERIAL alias includes AUTO_INCREMENT in its specification. The SERIAL alias is a more specific implementation of AUTO_INCREMENT. Not only does it include AUTO_INCREMENT, but it also specifies the data type (BIGINT), whether it’s signed or unsigned (UNSIGNED), its nullability (NOT NULL), and it also applies a UNIQUE constraint against the column.

Example

Here’s an example of creating a table with a SERIAL column:

CREATE TABLE t1 (
    Id serial
);

Now let’s use SHOW CREATE TABLE to return the definition of that table:

SHOW CREATE TABLE t1;

Result:

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `Id` bigint unsigned NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can see that the column has been defined as bigint unsigned NOT NULL AUTO_INCREMENT, with a UNIQUE KEY constraint applied against it. The UNIQUE constraint ensures that all values are distinct (i.e. no two values can be the same).

Given SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, we could have achieved the same result by doing this:

CREATE TABLE t2 (
    Id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
);
SHOW CREATE TABLE t2;

Result:

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `Id` bigint unsigned NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+

In this example I created a table called t2 with the same definition as the previous table. But instead of using the SERIAL keyword, I used its expanded form.

Another Use for SERIAL

When defining an integer column, we can use SERIAL DEFAULT VALUE as an alias for NOT NULL AUTO_INCREMENT UNIQUE.

Therefore, we can do this:

CREATE TABLE t3 (
    Id int SERIAL DEFAULT VALUE
);
SHOW CREATE TABLE t3;

Result:

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `Id` int NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+

In this example, I defined an INT column (instead of BIGINT), and it doesn’t have the UNSIGNED keyword. But it does have NOT NULL, AUTO_INCREMENT, as well as the UNIQUE constraint.