Set order ID back to 000000001

Question

To remove the orders from the database, I use the following queries:

$connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
$connection->rawQuery('SET FOREIGN_KEY_CHECKS = 0');

$connection->rawQuery('TRUNCATE TABLE `gift_message`;');
$connection->rawQuery('TRUNCATE TABLE `quote`;');
$connection->rawQuery('TRUNCATE TABLE `quote_address`;');
$connection->rawQuery('TRUNCATE TABLE `quote_address_item`;');
$connection->rawQuery('TRUNCATE TABLE `quote_id_mask`;');
$connection->rawQuery('TRUNCATE TABLE `quote_item`;');
$connection->rawQuery('TRUNCATE TABLE `quote_item_option`;');
$connection->rawQuery('TRUNCATE TABLE `quote_payment`;');
$connection->rawQuery('TRUNCATE TABLE `quote_shipping_rate`;');
$connection->rawQuery('TRUNCATE TABLE `reporting_orders`;');
$connection->rawQuery('TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;');
$connection->rawQuery('TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;');
$connection->rawQuery('TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;');
$connection->rawQuery('TRUNCATE TABLE `sales_creditmemo`;');
$connection->rawQuery('TRUNCATE TABLE `sales_creditmemo_comment`;');
$connection->rawQuery('TRUNCATE TABLE `sales_creditmemo_grid`;');
$connection->rawQuery('TRUNCATE TABLE `sales_creditmemo_item`;');
$connection->rawQuery('TRUNCATE TABLE `sales_invoice`;');
$connection->rawQuery('TRUNCATE TABLE `sales_invoiced_aggregated`;');
$connection->rawQuery('TRUNCATE TABLE `sales_invoiced_aggregated_order`;');
$connection->rawQuery('TRUNCATE TABLE `sales_invoice_comment`;');
$connection->rawQuery('TRUNCATE TABLE `sales_invoice_grid`;');
$connection->rawQuery('TRUNCATE TABLE `sales_invoice_item`;');
$connection->rawQuery('TRUNCATE TABLE `sales_order`;');
$connection->rawQuery('TRUNCATE TABLE `sales_order_address`;');
$connection->rawQuery('TRUNCATE TABLE `sales_order_aggregated_created`;');
$connection->rawQuery('TRUNCATE TABLE `sales_order_aggregated_updated`;');
$connection->rawQuery('TRUNCATE TABLE `sales_order_grid`;');
$connection->rawQuery('TRUNCATE TABLE `sales_order_item`;');
$connection->rawQuery('TRUNCATE TABLE `sales_order_payment`;');
$connection->rawQuery('TRUNCATE TABLE `sales_order_status_history`;');
$connection->rawQuery('TRUNCATE TABLE `sales_order_tax`;');
$connection->rawQuery('TRUNCATE TABLE `sales_order_tax_item`;');
$connection->rawQuery('TRUNCATE TABLE `sales_payment_transaction`;');
$connection->rawQuery('TRUNCATE TABLE `sales_refunded_aggregated`;');
$connection->rawQuery('TRUNCATE TABLE `sales_refunded_aggregated_order`;');
$connection->rawQuery('TRUNCATE TABLE `sales_shipment`;');
$connection->rawQuery('TRUNCATE TABLE `sales_shipment_comment`;');
$connection->rawQuery('TRUNCATE TABLE `sales_shipment_grid`;');
$connection->rawQuery('TRUNCATE TABLE `sales_shipment_item`;');
$connection->rawQuery('TRUNCATE TABLE `sales_shipment_track`;');
$connection->rawQuery('TRUNCATE TABLE `sales_shipping_aggregated`;');
$connection->rawQuery('TRUNCATE TABLE `sales_shipping_aggregated_order`;');
$connection->rawQuery('TRUNCATE TABLE `tax_order_aggregated_created`;');
$connection->rawQuery('TRUNCATE TABLE `tax_order_aggregated_updated`;');

$connection->rawQuery('SET FOREIGN_KEY_CHECKS = 1');

This successfully deletes all orders from the DB.

My question is:

How to reset the order ID back to 000000001 so the first new order will start with that id?

I've been searching for this, but most answers require you to do this by hand, but I want to achieve this by running a query from a PHP file.

-- EDIT -- Running the following command:

SELECT * FROM `sequence_order_1`

Directly in phpmyadmin returns:

Also runnning:

ALTER TABLE `sequence_order_1` AUTO_INCREMENT = 000000001;

in phpmyadmin does nothing.

Answer 1

Please update your sequence_order_1 table in database.

sequence_order_1 is for 1st store view.

If you change your series id, you can use a prefix,

ALTER TABLE sequence_order_1 AUTO_INCREMENT = 700000001;

Take note that the prefix 7 has been added. This number has to be higher than the previous prefix. So if your first sequence was like 000000001, then new sequence order has to be at least 100000001.

Magento default provides 9 digits for Id. and execute it in the database.

This will help you more.