Magento 2. After a fresh install, the new Order id will be 100000001, 100000002, and so on.

What is the correct way to change that increment id so it would be a higher number? For example 155555552, 155555553, and so on. When changed it will look much better.

In Magento 1.9 To edit the default Id for Orders, Invoice or Shipment we simply had to change values in the database in "eav_entity_store".

Magento 2 looks so different...

I know there are some plugins that offer to do this, but I want to know how to do it manually.


14 Answers 14

  1. Open your PHP Admin database (I tested on php 7)
  2. Find & click on the table called: sequence_order_1 (it is located in database itself not in the information_schema )
  3. Click on "Operations" tab & under "AUTO_INCREMENT" edit to desired order number.
  • I put the number 100500400 in the field AUTO_INCREMENT, click "Continue". Then I made a test purchases but the order id's are still 000000013, 00000014... Aug 19, 2016 at 17:38
  • 2
    i found sequence_order_1 and in operation tab there is Auto_increment the value is 5, so what is the correct value? Mar 12, 2017 at 17:53
  • 1) back up your database before making any changes. 2) instead of 5 you set the value you want for your order id, for example 1035327500. Your next order is should be 1035327501
    – Mark
    Mar 13, 2017 at 20:18
  • which magento version are we talking about?
    – Felix
    Dec 6, 2017 at 16:30
  • 2
    What works for me is: ALTER TABLE sequence_order_1 AUTO_INCREMENT=200000399; Jun 16, 2018 at 16:05

Go to your database from phpmyadmin,

Here _1 is used for store id after tablename.

Default Frontend store id is 1. if you have multi store then you have to set query for each store with table name like sequence_order_2 upto sequence_order_.*

Enter below query for table sequence_order_1 is used for default store. If you have multiple store you have to set tablename as per store id in below query.

This is only used for order placed from frontend.

sequence_order_1 is used for order id management in magento 2.

ALTER TABLE sequence_order_1 AUTO_INCREMENT=155555551;

Next order id is start from 155555551.

Below Query is defined for INVOICE, if you want to change invoice id

ALTER TABLE sequence_invoice_1 AUTO_INCREMENT=155555551;

For Shipment ALTER TABLE sequence_shipment_1 AUTO_INCREMENT=155555551;

  • This does not work form me. After doing some test buys, the order id's are still 000000013, 00000014... Aug 19, 2016 at 16:47
  • excellent answer!
    – huzefam
    Jan 11, 2017 at 13:29
  • it may help - magento.stackexchange.com/questions/156075/… -
    – sandip
    Jan 23, 2017 at 18:50
  • could you state the magento version for which this is correct?
    – Felix
    Dec 6, 2017 at 16:29
  • how to change first digit of the new order id Jun 15, 2019 at 11:38

What was previously in eav_entity_store is now covered in sales_sequence_profile and sales_sequence_meta. The table sequence_order_1 gets populated when orders are placed.

  • Could you roughly state the magento version numbers for which this is true and works?
    – Felix
    Dec 6, 2017 at 16:30
  • I haven't come across any changes in behaviour on this I think this applies from 2.0.0 up to current release 2.2.1 Dec 6, 2017 at 21:39
  • Still the same on 2.2.4, just tested it. Aug 28, 2018 at 7:47

Though all above answers are correct fully/partially, I thought to write down the answer as this would be helpful for future readers. This answer will address two questions:

1) How to change the order sequence number?

2) How to add Prefix or Suffix to an order number?

Before making any blind changes in the database, you should understand which sequence table is associated to what. You can check this from table sales_sequence_meta

Run the query select * from sales_sequence_meta; each store will have sequence table associated with that store. Note the table name viz: Order, Invoice, creditmemo or shipment.

Once that is done, go the table for example if you want to edit order number goto table sequence_order_1 and change the AUTO_INCREMENT value

alter table sequence_order_1 AUTO_INCREMENT=617;

This is all what is required to jump order sequence.

Now, moving further if you dig more and have a requirement where you want to add Prefix/Suffix to order follow below steps:

Goto table sales_sequence_profile and check the data

select * from sales_sequence_profile;

You will observe column prefix and 'suffix'. Initially it would be NULL. You can add the value in there of prefix and suffix for relevant meta_id which you can map from table sales_sequence_meta

This will change order prefix/suffix for all orders.

Hope this helps all !! Cheers !!

  • Thanks. it's solved my query Sep 2, 2020 at 13:46
  • This is the only solution that worked for me on 2.4.6-p2. Thanks from 5 years in the future Sep 11, 2023 at 10:06

Format for new order id is defined by default by constant in

Magento\SalesSequence\Model\Sequence :

const DEFAULT_PATTERN = "%s%'.09d%s";

It's pattern for sprintf() function that creates new id. To remove leading zeros you have to pass your pattern to constructor like this:

<type name="Magento\SalesSequence\Model\Sequence">
        <argument name="pattern" xsi:type="string">%s%s%s</argument>

or extend Sequence class and change as you need


Fresh Install:
If your on Magento 2.2.0+ and your running a fresh install with multi-store enabled (this is enabled by default) you can simply run the following queries before you place any orders through the site:

ALTER TABLE `sequence_creditmemo_0` AUTO_INCREMENT=303;
ALTER TABLE `sequence_creditmemo_1` AUTO_INCREMENT=303;
ALTER TABLE `sequence_invoice_0` AUTO_INCREMENT=303;
ALTER TABLE `sequence_invoice_1` AUTO_INCREMENT=303;
ALTER TABLE `sequence_order_0` AUTO_INCREMENT=303;
ALTER TABLE `sequence_order_1` AUTO_INCREMENT=303;
ALTER TABLE `sequence_shipment_0` AUTO_INCREMENT=303;
ALTER TABLE `sequence_shipment_1` AUTO_INCREMENT=303;

Please note that 303 is just the number I choose for our site because our old platform left off at order number 300 and I wanted some kind of consistency. Also note that you do not need any of the queries that end with _1 if you enabled single store mode.

Existing Install:
If your already using Magento 2.2.0+ (or upgraded to it) and simply want to bump up the numbers, not alter the padding or add characters, simply check the current AUTO_INCREMENT value on the following tables (assuming multi-site):

  • sequence_creditmemo_0
  • sequence_creditmemo_1
  • sequence_invoice_0
  • sequence_invoice_1
  • sequence_order_0
  • sequence_order_1
  • sequence_shipment_0
  • sequence_shipment_1

And change them using the alter queries shown above but make sure the new value you use is greater than the value you get back from checking the current AUTO_INCREMENT value.

If you are going to mess around with padding or add a custom pattern you need to see the other answers here and review the following tables:

  • sales_sequence_meta
  • sales_sequence_profile

Update: Mult-Stores?
For those not familiar with multi-stores this is a feature that allows you to run completely separate stores from the same Magento install. A common reason to do this is to support other languages. In the back end this is why you can choose the store view your looking at (if multi-store is turned on) when updating the configuration and doing other things for example.

For this reason Magento will add an underscore and a number at the end of certain tables to keep track of the different store views. You'll notice how the queries I provided seem to be duplicate tables but they end with different numbers, 0 or 1 in this case. If you had several stores/ views these numbers would just keep incrementing, _2 or _3 for example. This allows you to manage the id's of each store differently. Your change to the id system in one view (store) does not have to be the same in another.

It is out of scope for this post but this same numbering system applies to other tables in the database allowing you to truly treat and affect each store view differently. You could have a completely different theme per store for example.

  • Why do you keep mentioning about multi stores? What would be different if you had single-store mode enabled?
    – Condor
    Apr 9, 2018 at 9:16
  • 1
    I answered this is the post: "...note that you do not need any of the queries that end with _1 if you enabled single store mode." Notice that the queries I provided call what looks like duplicate tables except the end has a number, 0 or 1 in this case. If you disabled multi-store you would only see _0 nothing with _1 or _2 or _3 and so on. I added an update to clarify this. Apr 10, 2018 at 14:45

To permantly get rid of (or change) the extra zeros you must not override core files. In your app/etc/di.xml add the following and change the "%s%'.09d%s" value accordingly (in this example two 00 are padded):

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Framework\DB\Sequence\SequenceInterface">
            <argument name="pattern" xsi:type="string">%s%'.02d%s</argument>

The database edits for order and invoice numbers are perfectly described in Rakesh Jesadiya's answer.

  • Is there anything else to do except for adding the above code in di.xml? Nov 12, 2020 at 4:12
  • Not for the di.xml. You need to have module.xml and registration.php files, however, for you module to run.
    – TheFrakes
    Nov 18, 2020 at 10:08
  • I already have them in my custom module. Nov 18, 2020 at 10:15
  • Another question is can this change of order increment id be done now as the store has been running for nearly a year now? Nov 18, 2020 at 10:16
  • I changed the order also during production, the shop was running for some time. Why should it not be able to do? Legally, you don't have to have an increment ID.
    – TheFrakes
    Nov 18, 2020 at 10:37

Change The Sequence To change the order number padding edit Sequence.php (I believe there is a better way via a di.xml)


Edit this line

const DEFAULT_PATTERN  = "%s%'.09d%s";

Change the "9" to the desired padding


const DEFAULT_PATTERN  = "%s%'.05d%s";

Change the Prefix

In admin > Stores > Sales > Order Number > 
Enter Desired prefix

To keep everything easy add Fooman same order invoice number

  • 1
    It's a bad idea to edit the padding pattern in the core files, you should use 'my display name's method to change the padding pattern. Jun 28, 2017 at 9:22
  • 2
    What magento is this? (admin > Stores > Sales > Order Number ) I use MAG 2.2.2 but the menu order seems to be changed. Feb 18, 2018 at 3:56

STEP – 1 First of all, you need to register a new module by creating a registration.php file in the app/code/MageDelight/Testgrid/ folder and paste the following code.







STEP – 2 After that, you need to create a module.xml file to define installation and update functions in the app/code/MageDelight/Testgrid/etc folder and paste the following code.

<?xml version="1.0"?>

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"


     <module name="MageDelight_Testgrid" setup_version="1.0.0">



STEP – 3 Next, you need to create a di.xml file in the app/code/MageDelight/Testgrid/etc/ folder and paste the following code.

<?xml version="1.0"?>

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"


     <type name="Magento\Framework\DB\Sequence\SequenceInterface">


             <argument name="pattern" xsi:type="string">ABC%s%'.06d%sDFG</argument>



     <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">


             <argument name="collections" xsi:type="array">

                 <item name="sales_order_grid_data_source" 





     <type name="MageDelight\Testgrid\Model\ResourceModel\Order\Grid\Collection">


             <argument name="mainTable" xsi:type="string">sales_order_grid</argument>

             <argument name="resourceModel"





STEP – 4 Lastly, you need to change the pattern from %s%’.09d%s to ABC%s%’.06d%sDFG, and it will change the default Magento 2 order number.


To Adjust Invoice numbers Via PHPMYADMIN

INSERT INTO sequence_order_1 (sequence_value) VALUES ('2000');

2000 will be the new start number


Change the Order Increment Id

  1. Open your PHP Admin
  2. Find & click on the table called: sequence_order_1
  3. Click on "Operations" tab & under "AUTO_INCREMENT" edit to the desired order number.

For another Store same step follow

  1. Find & click on the table called: sequence_order_2
  2. Click on "Operations" tab & under "AUTO_INCREMENT" edit to the desired order number.

This will change the order number for the multiple stores

  1. Access the database to be able to perform SQL queries (via for instance mysql-client or phpMyAdmin).
  2. Then select your Magento 2 database.
  3. To change prefix to 913 for order-numbers execute:
UPDATE `sales_sequence_profile` SET `prefix` = '913' WHERE `profile_id` 
IN (SELECT `meta_id` FROM `sales_sequence_meta` WHERE `entity_type` = "order")

In Magento 2, after a fresh install, the new order ID starts from 100000001, 100000002, and so on. If you want to change the increment ID to a higher number, you can use the following methods:

Using the Magento 2 Admin Panel:

  1. Go to Stores > Settings > Configuration > Sales > Sales > Order Settings
  2. Scroll down to the "Order Increment ID" field, and enter the desired number.
  3. Save the configuration and clear the cache.

Using SQL:

  1. Log in to your database and run the following SQL query:
UPDATE sequence_sales_order SET sequence_value = <new_increment_id> WHERE sequence_name = 'sales_order';
  1. Replace <new_increment_id> with the desired value.

  2. Clear the cache and test that the new order ID is being used.

Using PhpMyAdmin:

  1. Open PhpMyAdmin and select your Magento database.
  2. Go to the table "sequence_sales_order"
  3. find the record with sequence_name = 'sales_order'
  4. change the value in the "sequence_value" field to your desired number
  5. save and clear the cache.

It's important to note that changing the order ID can have an impact on the order management, reporting, and other functionalities in Magento that rely on the increment ID of the order, so it's important to test the changes and make sure that everything is working as expected before going live.

Also, it's worth mentioning that if you already have orders in the store, and you change the order ID, it could cause confusion in the order management, so it's better to make these changes before starting to take orders.


You can make changes to Order Number Directly in Database Magento 2 by this tip

First of all, you need to open your PHP Admin database. Then find and open the table “sales_sequence_profile”.

After that, you can make changes to default order number in your Magento 2 website:

a. Change Order Increment ID

b. Change Order Number Prefix

c. Change Order Number Suffix

d. Change Order Number Start-value

e. Change Pad-length

I see the full tutorial in this article: Complete Tutorial Guide to Change Order Number in Magento 2

  • The name of the table is incorrect. it should be "sales_sequence_profile" Jul 24, 2018 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.