Как изменить следующий автоматический индекс mysql

I googled and found this question, but the answer I am really looking for fulfils two criteria:

1. using purely MySQL queries
2. reset an existing table auto-increment to max(id) + 1

Since I couldn’t find exactly what I want here, I have cobbled the answer from various answers and sharing it here.

Few things to note:

1. the table in question is InnoDB
2. the table uses the field id with type as int as primary key
3. the only way to do this purely in MySQL is to use stored procedure
4. my images below are using SequelPro as the GUI. You should be able to adapt it based on your preferred MySQL editor
5. I have tested this on MySQL Ver 14.14 Distrib 5.5.61, for debian-linux-gnu

Step 1: Create Stored Procedure

create a stored procedure like this:

DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN

      SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
      PREPARE stmt FROM @get_next_inc;
      EXECUTE stmt;
      SELECT @next_inc AS result;
      DEALLOCATE PREPARE stmt;

      set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
      PREPARE stmt FROM @alter_statement;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Then run it.

Before run, it looks like this when you look under Stored Procedures in your database.

When I run, I simply select the stored procedure and press Run Selection

Note: the delimiters part are crucial. Hence if you copy and paste from the top selected answers in this question, they tend not to work for this reason.

After I run, I should see the stored procedure

If you need to change the stored procedure, you need to delete the stored procedure, then select to run again.

Step 2: Call the stored procedure

This time you can simply use normal MySQL queries.

call reset_autoincrement('products');

Originally from my own SQL queries notes in https://simkimsia.com/reset-mysql-autoincrement-to-max-id-plus-1/ and adapted for Stack Overflow.

I googled and found this question, but the answer I am really looking for fulfils two criteria:

1. using purely MySQL queries
2. reset an existing table auto-increment to max(id) + 1

Since I couldn’t find exactly what I want here, I have cobbled the answer from various answers and sharing it here.

Few things to note:

1. the table in question is InnoDB
2. the table uses the field id with type as int as primary key
3. the only way to do this purely in MySQL is to use stored procedure
4. my images below are using SequelPro as the GUI. You should be able to adapt it based on your preferred MySQL editor
5. I have tested this on MySQL Ver 14.14 Distrib 5.5.61, for debian-linux-gnu

Step 1: Create Stored Procedure

create a stored procedure like this:

DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN

      SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
      PREPARE stmt FROM @get_next_inc;
      EXECUTE stmt;
      SELECT @next_inc AS result;
      DEALLOCATE PREPARE stmt;

      set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
      PREPARE stmt FROM @alter_statement;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Then run it.

Before run, it looks like this when you look under Stored Procedures in your database.

When I run, I simply select the stored procedure and press Run Selection

Note: the delimiters part are crucial. Hence if you copy and paste from the top selected answers in this question, they tend not to work for this reason.

After I run, I should see the stored procedure

If you need to change the stored procedure, you need to delete the stored procedure, then select to run again.

Step 2: Call the stored procedure

This time you can simply use normal MySQL queries.

call reset_autoincrement('products');

Originally from my own SQL queries notes in https://simkimsia.com/reset-mysql-autoincrement-to-max-id-plus-1/ and adapted for Stack Overflow.

Description

AUTO_INCREMENT атрибут может быть использован для создания уникального идентификатора для новых строк. Когда вы вставляете новую запись в таблицу (или при добавлении атрибута AUTO_INCREMENT с оператором ALTER TABLE ), а поле auto_increment имеет значение NULL или DEFAULT (в случае INSERT), значение автоматически увеличивается. Это также относится к 0, если NO_AUTO_VALUE_ON_ZERO SQL_MODE не включен.

AUTO_INCREMENT Столбцы AUTO_INCREMENT по умолчанию начинаются с 1. Автоматически созданное значение никогда не может быть ниже 0.

В каждой таблице может быть только один столбец AUTO_INCREMENT . Он должен быть определен как ключ (не обязательно PRIMARY KEY или UNIQUE ). В некоторых механизмах хранения (включая InnoDB по умолчанию ), если ключ состоит из нескольких столбцов, столбец AUTO_INCREMENT должен быть первым столбцом. Механизмы хранения, которые позволяют размещать столбец в другом месте, — это Aria , MyISAM , MERGE , Spider , TokuDB , BLACKHOLE , FederatedX и Federated .

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'),
    ('fox'),('whale'),('ostrich');

SELECT * FROM animals;
+
| id | name    |
+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | fox     |
|  5 | whale   |
|  6 | ostrich |
+

SERIAL — это псевдоним для BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE .

CREATE TABLE t (id SERIAL, c CHAR(1)) ENGINE=InnoDB;

SHOW CREATE TABLE t G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c` char(1) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Установка или изменение значения Auto_Increment

Вы можете использовать оператор ALTER TABLE , чтобы присвоить новое значение параметру таблицы auto_increment , или установить системную переменную сервера insert_id , чтобы изменить следующее значение AUTO_INCREMENT , вставленное текущим сеансом.

LAST_INSERT_ID() может использоваться для просмотра последнегозначения AUTO_INCREMENT , вставленного текущим сеансом.

ALTER TABLE animals AUTO_INCREMENT=8;

INSERT INTO animals (name) VALUES ('aardvark');

SELECT * FROM animals;
+
| id | name      |
+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | fox       |
|  5 | whale     |
|  6 | ostrich   |
|  8 | aardvark  |
+

SET insert_id=12;

INSERT INTO animals (name) VALUES ('gorilla');

SELECT * FROM animals;
+
| id | name      |
+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | fox       |
|  5 | whale     |
|  6 | ostrich   |
|  8 | aardvark  |
| 12 | gorilla   |
+

InnoDB

До MariaDB 10.2.3 InnoDB использовал счетчик автоинкремента, который хранится в памяти. Когда сервер перезагружается, счетчик повторно инициализируется на максимальное значение, используемое в таблице, что отменяет действие любой опции AUTO_INCREMENT = N в операторах таблицы.

Начиная с MariaDB 10.2.4 , это ограничение было снято, и AUTO_INCREMENT остается постоянным.

См. Также AUTO_INCREMENT Обработка в InnoDB .

Установка эксплицитных значений

Можно указать значение для столбца AUTO_INCREMENT . Если ключ является первичным или уникальным, значение не должно еще существовать в ключе.

Если новое значение выше текущего максимального значения, значение AUTO_INCREMENT обновляется, поэтому следующее значение будет выше. Если новое значение меньше текущего максимального значения, значение AUTO_INCREMENT остается неизменным.

Следующий пример демонстрирует это поведение:

CREATE TABLE t (id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;

INSERT INTO t VALUES (NULL);
SELECT id FROM t;
+
| id |
+
|  1 |
+

INSERT INTO t VALUES (10); 
SELECT id FROM t;
+
| id |
+
|  1 |
| 10 |
+

INSERT INTO t VALUES (2); 
INSERT INTO t VALUES (NULL); 
SELECT id FROM t;
+
| id |
+
|  1 |
|  2 |
| 10 |
| 11 |
+

Механизм хранения ARCHIVE не позволяет вставлять значение ниже текущего максимума.

Missing Values

Столбец AUTO_INCREMENT обычно имеет пропущенные значения.Это происходит потому,что если строка удалена,или значение AUTO_INCREMENT явно обновлено,старые значения никогда не используются повторно.Оператор REPLACE также удаляет строку,и ее значение теряется.С помощью InnoDB значения могут быть зарезервированы транзакцией;но если транзакция не удастся (например,из-за ROLLBACK),зарезервированное значение будет потеряно.

Таким образом,значения AUTO_INCREMENT можно использовать для сортировки результатов в хронологическом порядке,а не для создания числовой последовательности.

Replication

Чтобы сделать master-master или Galera безопасными для использования AUTO_INCREMENT , следует использовать системные переменные auto_increment_increment и auto_increment_offset для генерации уникальных значений для каждого сервера.

ЗАДЕРЖКИ,ЗАКОНОДАТЕЛЬНЫЕ ЦЕННОСТИ и виртуальные столбцы

Генерация значений автоинкремента при добавлении атрибута

CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (0),(0),(0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+
| a |
+
| 1 |
| 2 |
| 3 |
+

CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (5),(0),(8),(0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+
| a |
+
| 5 |
| 6 |
| 8 |
| 9 |
+

Если NO_AUTO_VALUE_ON_ZERO SQL_MODE установлен, нулевые значения не будут увеличиваться автоматически:

SET SQL_MODE='no_auto_value_on_zero';
CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (3), (0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+
| a |
+
| 0 |
| 3 |
+

See Also

• Начало работы с индексами
• Последовательности — альтернатива auto_increment, доступная в MariaDB 10.3
• AUTO_INCREMENT FAQ
• LAST_INSERT_ID()
• Обработка AUTO_INCREMENT в InnoDB
• ЧЕРНОГОЛОВЫЙ и АВТО_ИНКРЕМЕНТ
• UUID_SHORT () — генерирует уникальные идентификаторы
• Генерация идентификаторов-от AUTO_INCREMENT к последовательности (percona.com)

The auto_increment field must be an index, or be the first column of one. If you try to just drop the primary key, MySQL will complain. You have to delete the auto_increment property before moving it:

mysql> ALTER TABLE mytable MODIFY id int, DROP PRIMARY KEY, ADD PRIMARY KEY (ticker);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| id                | int(11)      | YES  |     | NULL    |       |
| exchange_id       | int(11)      | YES  | MUL | NULL    |       |
| ticker            | varchar(32)  | NO   | PRI | NULL    |       |
| instrument        | varchar(64)  | NO   |     | NULL    |       |
| name              | varchar(255) | YES  |     | NULL    |       |
| sector            | varchar(255) | YES  |     | NULL    |       |
| currency          | varchar(32)  | YES  |     | NULL    |       |
| created_date      | datetime     | NO   |     | NULL    |       |
| last_updated_date | datetime     | NO   |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

If you want id to continue being auto_increment, you have to do also:

mysql> ALTER TABLE mytable ADD INDEX `id` (id), MODIFY id int auto_increment;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | MUL | NULL    | auto_increment |
| exchange_id       | int(11)      | YES  | MUL | NULL    |                |
| ticker            | varchar(32)  | NO   | PRI | NULL    |                |
| instrument        | varchar(64)  | NO   |     | NULL    |                |
| name              | varchar(255) | YES  |     | NULL    |                |
| sector            | varchar(255) | YES  |     | NULL    |                |
| currency          | varchar(32)  | YES  |     | NULL    |                |
| created_date      | datetime     | NO   |     | NULL    |                |
| last_updated_date | datetime     | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

Please make sure to create if with the right index (multiple, unique) and nullability (null, not null).

Please also note that, depending on the engine used and/or the queries to be performed, a character field for a primary key may be suboptimal.