Re: remove gaps in a table

On 12/19/17 22:03, mireero wrote:
> Hi,
>
> I have a table "orders" which is basically:
>
> create database tmp;
> use tmp
> create table orders (
>     order_id int auto_increment primary key,
>     order_number int,
>     order_on date
> );
>

> Which gives us:
> MariaDB [tmp]> select * from orders;
>
> +----------+--------------+------------+
> | order_id | order_number | order_on   |
> +----------+--------------+------------+
> |        1 |            1 | 2017-10-10 |
> |        2 |            2 | 2017-10-11 |
> |        3 |            3 | 2017-10-20 |
> |        4 |            5 | 2017-10-22 |
> |        5 |            1 | 2017-11-03 |
> |        6 |            2 | 2017-11-10 |
> |        7 |            4 | 2017-11-18 |
> |        8 |            9 | 2017-11-20 |
> |        9 |           10 | 2017-11-21 |
> +----------+--------------+------------+
> 9 rows in set (0.00 sec)

It seems like your order number (I guess the one you present to the customer, which could cause some issues with their book keeping) is reset each month.

It would be tempting to use:

SELECT MAX(order_number)+1 AS next_order_number FROM orders WHERE order_on >= DATE_FORMAT(NOW() ,'%Y-%m-01');

but it has a race condition, if you have two orders done at the same time, then they would get the same order_number.

The better solution is to make a new table which has only one column "order_number" which is auto_increment and primary key.

create table order_number(order_number int auto_increment primary key);

and you make inserts like:
insert into order_number() values();

and use

select last_insert_id();

to get the order_id you just created.

then have a job which truncates the table at month changes with truncate table order_number;

This will of course just see to that you have the incremental value increased properly, if you would delete an order, then there will be a gap.

-- 

 //Aho