MySQL sub-table self-increment ID solution

When we do a table operation on MySQL, we will not be able to rely on MySQL's autoincrement to produce a unique ID because the data is scattered across multiple tables.

Should try to avoid the use of self-increasing IP to do the primary key, for the database table operation brings great inconvenience.

There is a special feature---sequence in PostgreSQL, Oracle, and DB2 databases. At any time, the database can obtain the next number of records in the table based on the number of records in the current table and the size of the steps. However, MySQL does not have this sequence object.

The sequence attribute can be implemented by the following method to produce a unique ID:
1. Generate ID from MySQL table
In the research on MySQL sub-table operation, a method is mentioned:
For insert, which is the insert operation, the first is to get a unique ID, you need a table to create an ID specifically, insert a record, and get the last inserted ID. The code is as follows:

CREATE TABLE ' ttlsa_com '. ' create_id ' (' id ' BIGINT () notnullauto_increment PRIMARY KEY) ENGINE =myisam

1

That is, when we need to insert data, it must be generated by this table ID value, my PHP code method is as follows:

<? phpfunctionget_ai_id () {    $sql="insert INTO create_id (ID) VALUES (')";    $this->db->query ($sql);     return$this->db->insertid ();}? >

<textarea class="crayon-plain print-no" style="-moz-tab-size: 4; font-size: 12px ! important; line-height: 15px ! important; z-index: 0; opacity: 0; overflow: hidden;" readonly="" data-settings="dblclick"><?php function get_ai_id () {$sql = "insert into create_id (ID) VALUES (')"; $this->db->query ($sql); return $this->db->insertid (); }?></textarea>

This works well, but in high concurrency, MySQL's auto_increment will cause the entire database to be slow. If there is a self-increment field, MySQL will maintain a self-increment lock, InnoDB will save a counter in memory to record the auto_increment value, when inserting a new row of data, a table lock will be used to lock the counter until the end of the insertion. If there is no problem with a row of inserts, but in high concurrency, it is sad, table locks can cause SQL blocking, greatly affect performance, and may reach the max_connections value.
Innodb_autoinc_lock_mode: 3 values can be set: 0, 1, 2
0:traditonal (each time a table lock is generated)
1:consecutive (default, a new way to pre-qualify rows, a table lock is not available, and a batch lock for simple insert to ensure continuous insertion)
2:interleaved (will not lock the table, to one processing one, the highest concurrency)
For the MyISAM table engine is traditional, a table lock is performed every time.

2. Generate IDs from Redis

The following code is excerpted from netizens.

Functionget_next_autoincrement_waitlock ($timeout = -) {$count= $timeout >0? $timeout: -;  while($rGet("Serial:lock") {$count++; Sleep (1);if($count >Ten) Returnfalse;} Returntrue;} Functionget_next_autoincrement ($timeout= -){//First Check if we are locked ...if(Get_next_autoincrement_waitlock ($timeout) = =false) Return0; $id= $r->INCR ("Serial"); if($id >1)return$id;//if ID = = 1, we assume we don't have "serial" Key ...//First we need to get lock.if($r->setnx ("Serial:lock"),1) {$r->expire ("Serial:lock", -*5); //get Max (ID) from database.$id =select_db_query ("select Max (ID) from user_posts");//or alternatively://Select ID from user_posts ORDER BY id DESC LIMIT 1//Increase It$id + +; //Update Redis Key$rSet("Serial", $id); //Release the lock$r->del ("Serial:lock"); return$id;} //can not get lock.Return0;} $r=Newredis (); $r->connect ("127.0.0.1","6379"); $id=get_next_autoincrement ();if($id) {$sql="INSERT INTO user_posts (id,user,message) VALUES ($id, ' $user ', ' $message ')"$data=exec_db_query ($sql);}

function Get_next_autoincrement_waitlock ($timeout =) {$count = $timeout > 0? $timeout: 60;while ($r->get ("Serial : Lock ")) {$count ++;sleep (1); if ($count >) return false;} return true;} function get_next_autoincrement ($timeout =) {//First check if we are Locked...if (Get_next_autoincrement_waitlock ($ Timeout) = = false) return 0, $id = $r->incr ("Serial"), if ($id > 1) return $id;/if id = = 1, we assume we do not have "Serial" key...//first we need to get Lock.if ($r->setnx ("Serial:lock"), 1) {$r->expire ("Serial:lock", 5);//G ET max (ID) from the database. $id = Select_db_query ("SELECT Max (ID) from user_posts");//or alternatively://select ID from use R_posts ORDER BY id desc limit 1//increase it$id++;//update Redis key$r->set ("Serial", $id);//Release The LOCK$R-&GT ;d El ("Serial:lock"); return $id;} Can not get Lock.return 0;} $r = new Redis (), $r->connect ("127.0.0.1", "6379"), $id = Get_next_autoincrement (), if ($id) {$sql = "INSERT INTO User_ Posts (id,user,meSsage) VALUES ($id, ' $user ', ' $message ') "$data = Exec_db_query ($sql);}

3. Queue mode
Using the queue service, such as Redis, Memcacheq, and so on, a quantitative ID is pre-allocated in a queue, each insert operation, first get an ID from the queue, if the insertion fails, add the ID to the queue again, while monitoring the number of queues, when less than the threshold, automatically add elements to the queue.

This way you can have a plan to allocate the ID, but also bring economic effects, such as QQ number, all kinds of beautiful, marked. such as the userid of the site, allow UID Landing, the introduction of a variety of beautiful, marked, for the ordinary ID scrambled and then randomly assigned.

<?PHP classcommon{Private$r; Functionconstruct () {$ This-__construct (); } publicfunction__construct () {$ This->r=Newredis (); $ This->r->connect ('127.0.0.1',6379); } functionset_queue_id ($ids) {if(Is_array ($ids) &&isset ($ids)) {     foreach($ids as$id) {     $ This->r->lpush ('next_autoincrement', $id); }}} functionget_next_autoincrement () {return$ This->r->lpop ('next_autoincrement'); }} $createid=Array (); while(Count ($createid) < -) {$num=rand ( +,4000); if(!In_array ($num, $createid)) $createid []=$num;} $id=Newcommon (); $id-set_queue_id ($createid); Var_dump ($id->get_next_autoincrement ());

<textarea class= "Crayon-plain print-no" style= "-moz-tab-size:4"; Font-size:12px! Important line-height:15px! Important z-index:0; opacity:0; Overflow:hidden; "ReadOnly data-settings=" DblClick "><?phpclass common {private $r; function construct () {$this->__construct (); } public Function __construct () {$this->r=new Redis (); $this->r->connect (' 127.0.0.1 ', 6379); } function set_queue_id ($ids) {if (Is_array ($ids) && isset ($ids)) {foreach ($ids as $id) {$this->r-& Gt Lpush (' next_autoincrement ', $id); }}} function Get_next_autoincrement () {return $this->r->lpop (' next_autoincrement '); }} $createid =array (); while (count ($createid) <20) {$num =rand (1000,4000); if (!in_array ($num, $createid)) $createid []= $num;} $id =new Common (), $id->set_queue_id ($createid); Var_dump ($id->get_next_autoincrement ()); </textarea>Monitor the number of queues and automatically replenish the queue and fetch the ID but not used, the relevant code is not posted

MySQL sub-table self-increment ID solution (GO)