Perhaps you’re familiar with MySQL’s SERIAL type, which is not actually a data type, but a shortcut for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

One restriction of SERIAL is that it forces us to use the BIGINT data type, which is fine if you think you’ll need the extra space. But what if you want the same attributes for a smaller integer type, like INT or MEDIUMINT for example?

Enter SERIAL DEFAULT VALUE.

What is SERIAL DEFAULT VALUE?

In MySQL, SERIAL DEFAULT VALUE is an alias for NOT NULL AUTO_INCREMENT UNIQUE. We can use it in our column definitions to apply those constraints/attributes without having to type them out.

Here’s a quick explanation of what these do:

• NOT NULL: Ensures that NULL values cannot be inserted into the column (see Understanding the NOT NULL constraint for more info).
• AUTO_INCREMENT: Creates a unique identity for new rows. This is an auto-incrementing value of the data type that we specified in the column definition (see How MySQL AUTO_INCREMENT Works).
• UNIQUE: Ensures only unique values are inserted into the column (see What is a UNIQUE Constraint?).

This means that the column will auto-generate unique values for each new row. Essentially, it’s the same as using SERIAL but with an explicit integer data type of our choosing.

Why Use SERIAL DEFAULT VALUE Instead of Just SERIAL?

When we use SERIAL by itself, the column will use the BITINT data type. That’s because SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. However, SERIAL DEFAULT VALUE allows for the use of an explicit data type, which makes it slightly more flexible for certain use cases.

With SERIAL we cannot specify a datatype, but with SERIAL DEFAULT VALUE we must provide a data type. If we try to assign a data type to SERIAL we’ll get an error. And if we try to omit the data type from SERIAL DEFAULT VALUE we get an error.

So if you want to implement SERIAL-like functionality, but on an an INT type (or other integer type), then SERIAL DEFAULT VALUE is what you need.

Here’s an example of two tables, one with SERIAL and one with SERIAL DEFAULT VALUE:

CREATE TABLE t1 (
    c1 SERIAL
);

CREATE TABLE t2 (
    c1 INT UNSIGNED SERIAL DEFAULT VALUE
);

SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;

Output:

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

• We can see that with t1 we didn’t need to provide any column definition other than SERIAL. When we used SHOW CREATE TABLE against this table, we can see that it was translated into the full column definition, including the BIGINT data type.
• With t2, we had to provide the integer type (we provided an unsigned INT in this case), along with SERIAL DEFAULT VALUE. The SHOW CREATE TABLE statement shows that the other SERIAL attributes were applied, but the BIGINT type was not used (because we’d specified our own type instead).

Why Use SERIAL DEFAULT VALUE Instead of Defining NOT NULL AUTO_INCREMENT UNIQUE Manually?

While we can explicitly define a column with NOT NULL AUTO_INCREMENT UNIQUE, using SERIAL DEFAULT VALUE or SERIAL simplifies the syntax and reduces redundancy. These shorthand options make schema definitions more concise and consistent.

Defining NOT NULL AUTO_INCREMENT UNIQUE manually:

CREATE TABLE t1 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY
);

Using SERIAL DEFAULT VALUE:

CREATE TABLE t2 (
    id INT UNSIGNED SERIAL DEFAULT VALUE PRIMARY KEY
);

The SERIAL DEFAULT VALUE option reduces verbosity while achieving the same outcome. You may find it more readable and easier to maintain in a large schema.

Summary of Key Differences

• SERIAL: A shorthand for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
• SERIAL DEFAULT VALUE: Provides the functionality of SERIAL while allowing (requiring) us to specify the data type.
• NOT NULL AUTO_INCREMENT UNIQUE (Manual definition): Provides complete control but is more verbose.

Choosing between these really just comes down to whether you prefer concise or explicit column definitions.