Introducing PopSQL + dbt. The first SQL editor with built-in dbt support.Read more →

How to Alter Sequence in MySQL

Auto incrementing columns in tables start at 1 by default, but sometimes you may want them to start at a different number. These numbers are known as "sequences" in other databases but are implemented differently in MySQL. An auto incrementing column is part of table definition and is modified using the ALTER TABLE command.

First, you can check for the next value using the SHOW CREATE TABLE COMMAND:

mysql> SHOW CREATE TABLE users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
   <<other lines omitted for brevity>>
  PRIMARY KEY (`user_id`)

To change it:


Ready for a modern SQL editor?