How to Start Auto Increment from a Specific Point

How do I start auto increment from a specific point?

ALTER TABLE batchinfo AUTO_INCREMENT = 20000;

See also Autoincrement

How to set initial value and auto increment in MySQL?

Use this:

ALTER TABLE users AUTO_INCREMENT=1001;

or if you haven't already added an id column, also add it

ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD INDEX (id);

Change auto increment starting number?

You can use ALTER TABLE to change the auto_increment initial value:

ALTER TABLE tbl AUTO_INCREMENT = 5;

See the MySQL reference for more details.

Mysql auto-increment a column with one specific primary key

In order to achieve what you're looking for, you have to use triggers. There's no other direct way to accomplish this task (I guess).

I did try a fast demo now:

Create Table SoQuestion (
  UserId int,
  PostId int,
  PostNumber int null
 );

 CREATE TRIGGER inc_post_num 
 BEFORE INSERT ON SoQuestion
 FOR EACH ROW
 set New.PostNumber = (select num 
                       From (select count(*) as num 
                             from SoQuestion 
                             where UserId = New.UserId) as b) 
                     + 1;

insert into SoQuestion (UserId, PostId) Values (1,1);
insert into SoQuestion (UserId, PostId) Values (1,10);
insert into SoQuestion (UserId, PostId) Values (1,20);
insert into SoQuestion (UserId, PostId) Values (2,1);
insert into SoQuestion (UserId, PostId) Values (2,10);
insert into SoQuestion (UserId, PostId) Values (3,1);
insert into SoQuestion (UserId, PostId) Values (4,1);

select * FROM SoQuestion;

And here's the output that I got:

UserId | PostId | PostNumber |
==============================  
1      | 1      | 1          |
1      | 10     | 2          |
1      | 20     | 3          |
2      | 1      | 1          |
2      | 10     | 2          |
3      | 1      | 1          |
4      | 1      | 1          |

Here's the demo.

---

After going through the Auto_Increment documentation, I found another way to achieve this without using triggers. The idea is about creating an Auto_Increment column and add it with another column as PRIMARY KEY. In our case it would be UserId and the AUTO_INCREMENT would be PostNumber and they both form the primary key. This is how:

Create Table SoQuestion (
  UserId int,
  PostId int,
  PostNumber int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (UserId, PostNumber)
 );

insert into SoQuestion (UserId, PostId) Values (1,1);
insert into SoQuestion (UserId, PostId) Values (1,10);
insert into SoQuestion (UserId, PostId) Values (1,20);
insert into SoQuestion (UserId, PostId) Values (2,1);
insert into SoQuestion (UserId, PostId) Values (2,10);
insert into SoQuestion (UserId, PostId) Values (3,1);
insert into SoQuestion (UserId, PostId) Values (4,1);

select * FROM SoQuestion;

This would give us the same output that the first way gave:

UserId | PostId | PostNumber |
==============================  
1      | 1      | 1          |
1      | 10     | 2          |
1      | 20     | 3          |
2      | 1      | 1          |
2      | 10     | 2          |
3      | 1      | 1          |
4      | 1      | 1          |

And here's the demo for the second way.

Insert Auto Increment Column number explicitly from specific Start

first set the increment to 687477

ALTER TABLE switch_person AUTO_INCREMENT=687477;

then run your insert, without specifying the pk it will use the autoincrement and increment it for next insert.

INSERT INTO `switch_person` (`PTPK`, `EmployeeNamePK`, `SwitchTime`, `Half`, `date`, `Month`, `Year`, `SwitchDate`, `hoursWorked`, `LeadBy`, `Info`, `EmpDaysStatusId`, `TaskPK`, `TaskAssignCompletionId`, `SpendDays`, `LeaveId`, `Comments`)
select `PTPK`, `EmployeeNamePK`, `SwitchTime`, 1, `date`, `Month`, `Year`, `SwitchDate`, `hoursWorked`, `LeadBy`, `Info`, `EmpDaysStatusId`, `TaskPK`, `TaskAssignCompletionId`, `SpendDays`, `LeaveId`, `Comments`
from switch_person
where SwitchDate = '2021-08-17'
and Half = 2

How to start a mysql column with a specific increment value?

You can just do something like this:

ALTER TABLE invoice AUTO_INCREMENT = 9000;

For more info look here.

Make MySQL auto-increment id (re) start from 1

Can't be done using MySQL's autoincrement feature. You could roll your own solution, e.g. a mix between application logic and database triggers. BUT, seriosly, your design is heavily broken if it requires you to recycle UNIQUE IDs.

Couldn't you just create another table where you'd save references like that (this could be done by querying the minimum) and let your main table point to that auxilliary table?

EDIT
Here's a blog I've googled that deals with your problem: see here.

mysql add an autoincrement column with start value

No, it works to add an AI column with a starting position. But you almost got the syntax right. Here's a demo:

mysql> CREATE TABLE foo (v varchar(10));

mysql> INSERT INTO foo VALUES ('one'), ('two'), ('three');

Then comes the tricky syntax. You have to declare the column as AUTO_INCREMENT, but then also give the table option for the AUTO_INCREMENT starting value. And you need a comma to separate the ADD COLUMN from the table option.

mysql> ALTER TABLE foo ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY,
    AUTO_INCREMENT=999999;

mysql> select * from foo;
+-------+---------+
| v     | id      |
+-------+---------+
| one   |  999999 |
| two   | 1000000 |
| three | 1000001 |
+-------+---------+

Mysql - how to set auto-increment to start from zero

SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.)

Reference

Reference 2

Set start value for column with autoincrement

From Resetting SQL Server Identity Columns:

Retrieving the identity for the table Employees:

DBCC checkident ('Employees')

Repairing the identity seed (if for some reason the database is inserting duplicate identities):

DBCC checkident ('Employees', reseed)

Changing the identity seed for the table Employees to 1000:

DBCC checkident ('Employees', reseed, 1000)

The next row inserted will begin at 1001.

---

Related Topics