Design a global order production counter using MySQL

August 10, 2018 08:53:50

How the general production order number is

1, use time + random number 1+ random number 2

Disadvantage, it is possible to repeat in the concurrency, the solution is to add a unique index, when the data inserted into the query has been used

2, use time + count the current order number, by order number +1, many systems use this

Shortcomings, if there is a need to delete data, of course, the needs of the brain, but there will be, again, the order will appear repeat orders, this very bad change

Reference 1: Kingdee K3, there will be a globally used ID, which grows through the trigger

Reference 2: Experience, global counters solve 2 fundamental problems, order number is not duplicated, use a request once, return the most recent calculation results, 2 will not be because the deletion of orders duplicate order number

Database

--counter Table CREATE TABLE ' counter ' (' id ' int (One) unsigned notNULLAuto_increment,' name ' varchar (100)DEFAULT NULLCOMMENT ' name ',' value ' bigint (unsigned)DEFAULT' 1 ' COMMENT ' stored value ',' create_time ' datetime notNULL DEFAULT' 0000-00-00 00:00:00 ' COMMENT ' creation time ',' update_time ' datetime notNULL DEFAULT' 0000-00-00 00:00:00 ' COMMENT ' Update Time ',' tag ' varchar (50)DEFAULT NULLCOMMENT ' label ',' type ' tinyint (1) notNULL DEFAULT' COMMENT ' type 10 purchase order 20 Sales order 30 self-logistics number ',PRIMARYKEY(' id ')) ENGINE=innodbDEFAULTCHARSET=UTF8MB4 comment= ' counter table ';

Note that the value I store is bigint 20, big enough

 //counter     Public Static functionCounter$type=NULL) {        if(Empty($type)) {            Throw New\Exception(' tag tag cannot be empty '); }        $now= Time(); $time=Date(' Ymd ',$now); $time 1=Date(' Ym ',$now); $tag= Self::counter_array ($type); $name=$time. ‘_‘ .$tag; DB::BeginTransaction (); Try {            $Counter= Counter::where (' name ',$name)->lockforupdate ()->first ([' Value ', ' ID ']); if(Empty($Counter)) {                //No, insert it .                $Counter=NewCounter; $Counter->name =$name; $Counter->type =$type; $Counter->tag =$tag; $Counter-Save (); $Counter= Counter::where (' name ',$name)->first ([' Value ', ' ID ']); }            $Counter=$Counter-ToArray (); //lock prevents an error in the generated order number, not indexed on nameCounter::where (' id ',$Counter[' ID ']) ->lockforupdate ()First (); if($type= = 40) {                $new _count= (float)$Counter[' value '] + 1; Counter:: Where (' name ',$name)->update ([' value ' = =$new _count]); $string=$time 1.Str_pad($new _count, 6, ' 0 ',str_pad_left); } Else {                $new _count= (float)$Counter[' Value '] + 3; Counter:: Where (' name ',$name)->update ([' value ' = =$new _count]); $string=$time.Str_pad($new _count, 12, ' 0 ',str_pad_left); } DB::commit (); return $string; } Catch(\Exception $e) {DB::RollBack (); Throw New\Exception($e-getMessage ()); }    }    //Order counter Map array     Public Static functionCounter_array ($type=NULL) {        //Purchase order number        $array[' ten '] = ' sales_order_number '; //Sales Order Number        $array[' + '] = ' purchase_order_number '; //Self-Logistics order number        $array[' + '] = ' logistics_order_number '; //Inbound Order Number        $array[' + '] = ' inbound_order_number '; //Out-of- stock order number        $array[' ['] = ' outbound_order_number '; if(Empty($type)) {            return $array; }        return $array[$type]; }

Explain why the global order is not +1, but +3, to prevent special orders to be inserted, while keeping the counter numbers from growing too fast

There must be a lock, although there are transactions, but still to lock, the order length of their own choice of padding length, I define 20 lengths, enough to 14 bits length divided by 3 length, enough

Some people will worry about the new can, I can only say you think more, this pressure can not carry, impossible, see at any time to write MySQL, read the operation at any time? Such a rough logic code MySQL can meet

Design a global order production counter using MySQL