Mysql obtains the globally unique value.

When it comes to database data storage, it is often the only value problem. Some are restrictions imposed by primary keys, and some are business needs.

The following describes how to obtain or produce unique values:

Create a test table tbl_user with three fields: Id, Name, and Age. Id is the primary key.

   1:  drop table if exists `tbl_user`;

   2:  create table 

   3:  `tbl_user` (

   4:      `Id` int(10),

   5:      `Name` varchar(20),

   6:      `Age` int(10),

   7:      PRIMARY KEY  (`Id`)

   8:  )DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Insert several data records

1: insert into tbl_user values (1000, "kitten", 22 );

2: insert into tbl_user values (1001, "Puppy", 22 );

3: insert into tbl_user values (1002, "hedgehog", 22 );

   4:   

   5:  select * from tbl_user;

Query results:

　　1. Generally, "" or other UUID algorithms are used to achieve this. If a program distributed on multiple machines accesses the table of the unified database, you can take the Ip address, network card number and other information into the test to solve the problem (of course, it can not be a simple splicing, you can use a certain algorithm to obtain the appropriate number of digits as needed ).
2. First query the maximum value of select max (id) in the table, and then add 1 as the new value. Stupid method.
   1:  select max(Id) from tbl_user;
2: The maximum queried Id is 1002.
   3:   
4: insert 1003
   5:   
6: insert into tbl_user values (1003, "bear", 22 );
   7:   
The data in the table is
. In this way, you do not have to worry about generating this unique value that cannot be repeated. However, generally, an application requires this unique value. At this time, you have to query it to obtain the Id generated by the auto-increment database. For example, when a user logs on, you need to generate a login session Id or Token. These programs generally need to get this value instead of simply exist in the database. The generated value. 1. Yes. query the inserted data based on the conditions. 2. You can directly obtain the ID automatically generated during the last insert record (note that it is in the same connection (session) of the database), which is used after the insert.
For example, first set the Id field in the table to auto-increment, and then insert a data record (do not insert the Id value so that the database can get the value from auto-increment), select @ IDENTITY query, finally, let's take a look at the verification.
  tbl_user CHANGE Id Id     tbl_user auto_increment  tbl_user    tbl_user  Id    auto_increment   tbl_user  Name,Age 
 
Verify: select * from tbl_user; the current table record is
　　   
4. Use mysql functions. The preceding auto-increment field (auto_increment) can only generate unique values in the "table" and must be used together to make it a "unique primary key or unique index". Its values increase gradually. UUID is a string value with a fixed length of 36 characters. UUID is a unique value in time and space. It is a combination of "random + Rules.
6d4711e3ba6e6d4711e3ba6e7446a08ee8ec
As you can see, the values obtained by calling the UUID () function multiple times are different. It consists of five parts and is separated by hyphens (-), with a total of 36 characters. Where:
The first three sets of values are converted from the timestamp, which is unique in time ";
The 4th set of values temporarily preserve the uniqueness of the timestamp, and will be changed only when mysql is restarted;
The 5th Group is converted from the mac value, which helps solve the problem of "unique space". The same machine generally has the same multi-instance. If the mac value cannot be obtained, it is a random value.
These can ensure that the obtained values are unique in time and space. Of course, you can also remove the hyphen: select replace (uuid (),'-','').
There is a variant UUID () function in MySQL 5.1. * and later,You can generate a 17-64-bit unsigned integer. Note that it is the generated integer, and the previous UUID () is the string. The value first executed after MySQL is started is initialized through Timestamp and so on, and 1 is added during the next call in this operation. This value is generally relatively large. You can call right (UUID_SHORT (), 9) to obtain the following digits. Alternatively, you can write a UDF to generate the value as needed. For example:
# DEFINER 'root' @ '''getuidtest' (SysId) (tmpID UUID_SHORT () concat (SysId, (UUID_SHORT (),)). call the custom function GetUuidTest (getuidtest (# uid_short () the last 8 digits (# uuid_short () the last 8 bits (# uuid_short () the last 8 bits (. in this example, call the custom function GetUuidTest (tbl_user idgetuidtest (), Name, Age tbl_user idgetuidtest (), Name, Age;
In this example, select * from tbl_user; and all the records are