The solution of the problem of Mysql table self-increasing ID _mysql

When we do a table-by-MySQL operation, we will not be able to rely on MySQL's autoincrement to produce a unique ID because the data has been dispersed across multiple tables.
Should try to avoid using the self-added IP as 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 get the number of records in the table according to the number of records in the current table and the step size. However, MySQL does not have this sequence of objects.
You can implement the sequence attribute to produce a unique ID by using the following methods:

1. Generate ID from MySQL table
for inserts, insert operations, the first is to get a unique ID, you need a table to create the ID, insert a record, and get the last inserted ID. The code is as follows:

CREATE TABLE ' ttlsa_com '. ' create_id ' ( 
' id ' BIGINT () not NULL auto_increment PRIMARY KEY
) ENGINE = MYISAM 

That is, when we need to insert data, we have to generate ID values from this table, and my PHP code has the following method:

<?php 
function get_ai_id () { 
 $sql = "INSERT into create_id (ID) VALUES (')"; 
 $this->db->query ($sql); 
 return $this->db->insertid (); 
> 

This works fine, but in high concurrency, MySQL's auto_increment will cause the entire database to slow down. If there is a self added field, MySQL maintains a self-locking, InnoDB will hold a counter in memory to record the auto_increment value, and when inserting a new row of data, a table lock is used to lock the counter until the insertion ends. If a row of inserts is not a problem, but in the case of high concurrency, that is sad, table locks can cause SQL blocking, greatly affect performance, but also may reach the max_connections value.
Innodb_autoinc_lock_mode: can set 3 values: 0, 1, 2
0:traditonal (table locks are generated each time)
1:consecutive (default, the new way to pre-estimate the number of rows, use table locks when not used, for simple insert will get a lot of locks, ensure continuous insertion)
2:interleaved (will not lock the table, come one to handle one, concurrency highest)
For the MyISAM table engine is traditional, table locks are performed each time.

2. Generate IDs via Redis

function Get_next_autoincrement_waitlock ($timeout = m) {$count = $timeout > 0? $timeout: 60;
 while ($r->get ("Serial:lock")) {$count + +;
 Sleep (1);
 if ($count >) return false;
return true; } function get_next_autoincrement ($timeout =) {//The ARE if we locked ... if (get_next_autoincrement_waitlo
 
 CK ($timeout) = = false) return 0;
 
 $id = $r->incr ("Serial");
 
 if ($id > 1) return $id;
 If ID = 1, we assume we do not have "serial" key ...//We need to get lock.
 
 if ($r->setnx ("Serial:lock"), 1) {$r->expire ("Serial:lock", 60 * 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 $r->set ("Serial", $id);
 
 Release the lock $r->del ("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
  In fact, this is a commentary above
  use queue services, such as Redis, Memcacheq, and so on, to allocate a certain amount of IDs in a queue, each time inserting an operation, First get an ID from the queue, if the insertion fails, add the ID to the queue again, monitor the number of queues, and automatically add elements to the queue when less than the threshold.
  This way can be planned for the allocation of IDs, but also bring economic effects, such as QQ number, all kinds of Liang, price. such as the site of the UserID, allows the UID landing, the introduction of a variety of Liang, price tag, for ordinary ID upset and then randomly allocated.

<?php
 
class 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->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 ()); 

Monitor the number of queues and automatically replenish queues and fetch IDs without using

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.