MySQL serial vs auto-increment for id column

Question

Disclaimer: I have only novice knowledge of and experience with databases.

I'm following a Laravel course on Laracasts, and in the database video, the instructor sets the ID column to a type of SERIAL. This is different to how I've seen this done in all other database tutorials, where they will usually check the A_I (auto-increment) checkbox, and this automatically makes the column primary, and leaves the type to be something like INT.

Hovering over the SERIAL type in PHPMyAdmin tells me that it's an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, but is there ever a particular reason to prefer it over the settings that checking the A_I checkbox sets up? Does either way offer any advantages or disadvantages?

I did find this for PostgreSQL, indicating SERIAL is old and outdated, but I couldn't find an equivalent for MySQL and I'm unsure if the same applies to it.

Bill_Karwin_20860 · Answer

I'm sure MySQL's SERIAL type was implemented to make it easy for folks who were accustomed to PostgreSQL to have one set of CREATE TABLE statements that would work on both brands of database, and do more or less the same thing.

In an old version of the MySQL manual, it was stated that SERIAL is a compatibility feature (without naming the brand it was intended to be compatible with). The language about compatibility was removed (see https://bugs.mysql.com/bug.php?id=7978).

Now that even PostgreSQL has changed its recommended practice and they use IDENTITY columns instead of SERIAL, the MySQL feature is really unnecessary.

There is no advantage to using SERIAL in MySQL. On the contrary, if you do use it in a CREATE TABLE statement, you will see that the syntax isn't saved. It is just an alias for the BIGINT UNSIGNED AUTO_INCREMENT UNIQUE, as documented.

I find that it's actually wasteful to do this, because I typically declare the auto-increment column as a PRIMARY KEY anyway, and this makes the UNIQUE redundant. So you end up with two unique indexes for no reason.

mysql> create table mytable (id serial primary key);

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`) -- this one is superfluous
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

P.S. This question is almost but not quite a duplicate of What is the difference between SERIAL and AUTO_INCREMENT in mysql

MySQL serial vs auto-increment for id column

1 Answers