AUTO_INCREMENT profile

MySQL’s AUTO_INCREMENT property can be used to increment the primary key when inserting a new record.

For example execute the following SQL:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id));INSERT INTO animals (name) VALUES
    ('dog'), ('cat'), ('penguin'),
    ('lax'), ('whale'), ('ostrich');

SELECT * FROM animals;
Copy the code

The result is as follows:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
Copy the code

AUTO_INCREMENT summary

  1. If the id column is NULL, or the ID column is 0 (SQL Mode is NOT NO_AUTO_VALUE_ON_ZERO), or the ID column is NULL (the ID column is NOT NULL). Then the ID will grow automatically.

  2. Insert a larger value into the AUTO_INCREMENT column with the INSERT statement, and the sequence is reset to the maximum value incremented.

  3. When using MySQL, if the table contains (auto_increment type), on the field in the table to insert a record, you can call 1444549 for recent insert the rows on the field values.

  4. An interview question

Mysql > alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table ALTER table ALTER table ALTER table ALTER table alter table alter table alter table alter table alter table alter table alter table alter table alter table

A: If the storage engine of the table is MyISAM, the id is 18, because MyISAM records the maximum ID in the file; If the storage engine of the table is InnoDB, the id is 15, because InnoDB records the maximum ID in memory, which will be lost after restart.

Links

  • 【 dev.mysql.com/doc/refman/…
  • 【 blog.csdn.net/stevejobson…
  • 【 github.com/jaywcjlove/…
  • 【 blog.csdn.net/jibing57/ar…
  • 【 mp.weixin.qq.com/s/XqtvyeUeu…
  • 【 dev.mysql.com/doc/refman/…
  • 【 dev.mysql.com/doc/refman/…
  • 【 www.jianshu.com/p/8773795dd…