Auto increment in mysql by more than 1-CodePudding

I have an invoicing system that runs off an sql db. Every time I create a new invoice, the invoice number increases by 1.

So invoice number 5000, the next is 5001, then 5002 and so on. I want so that the next invoice number increased by a different number, Say 15.

So invoice number 5000, then 5015, then 5030 etc.

Is it possible to change something in phpmyadmin to achieve this.

TIA

CodePudding user response：

Try this, do note that this is global, and not just one table. If you are going to just have this happen on one table, create an stored procedure to set the id instead of auto increment.

SET @@auto_increment_increment=2;
SET @@auto_increment_offset=2;

Documentation: https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_auto_increment_increment

You can also just have a subquery decide what the invoice no is supposed to be and not have it autoincremented. I would suspect that this code should be written in the invoicing software itself (Which might not be possible, in your case, at which point my top example is the only way to go) but if you can edit the software, you simply need to select the highest invoice that exists, and then 2 - then store that in the column you present.

CodePudding user response：

I know that you can modify the step in mySQL, but it is a global change which would affect all tables. I suggest that you leave the data as is and use a view to multiply the values by 15.
Obviously you will replace the column description with a number of columns with the real information, date, customer etc. etc.

create table invoice_data(
id int primary key AUTO_INCREMENT,
description varchar(100)
);

create view invoices as
select
15 * id as invoice_number,
description
from invoice_data;

insert into invoice_data (description) values
('invoice 1 information'),('invoice 2 information');

select * from invoices;

invoice_number | description          
-------------: | :--------------------
            15 | invoice 1 information
            30 | invoice 2 information

db<>fiddle here