Example analysis of mysql non-primary key self-increment usage

This article uses an example to illustrate the usage of MySQL non-primary key self-increment. Share with you for your reference, the details are as follows:

In MySQL, not only the primary key can be set to auto-increment, but the column set as the key can be set to auto-increment. as follows:

CREATE TABLE t1 (
  id INT,
  col1 INT auto_increment NOT NULL
);

The results are as follows:

If you set the col1 column as the key, you can create an auto-increment column.

CREATE TABLE t1 (
  id INT,
  col1 INT auto_increment NOT NULL,
  key(col1)
);

The results are as follows:

If we set id as the primary key, we can still create it successfully.

CREATE TABLE t2 (
  id INT PRIMARY KEY,
  col1 INT auto_increment NOT NULL,
  key(col1)
);

The results are as follows:

Therefore, the auto-increment column must be a key, but it does not necessarily have to be a primary key. But can a table have multiple auto-increment columns?

Answer: A table can only have one auto-increment column.

CREATE TABLE t3 (
  id INT PRIMARY KEY auto_increment,
  col1 INT auto_increment NOT NULL,
  key(col1)
);

The results are as follows:

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.