MySQLUUID function description _ MySQL

MySQLUUID function description bitsCN.com

MySQL UUID function details

MySQL has two tools for generating unique values: UUID () function and auto-incrementing sequence. what is the difference between them? In this regard, we will compare their respective features and similarities and differences:

1. you can all generate unique values.

2. UUID is a unique value that can be generated in time and space. the auto-incrementing sequence can only generate unique values in the table and must be used together to make it a unique primary key or unique index.

3. the implementation method is different. UUID is a combination of random + rules, while the auto-incrementing sequence controls the gradual increase of a value.

4. UUID generates a string value with a fixed length of 36 characters, while the auto-increment sequence generates an integer value. The length is determined by the field-defined attribute.

Next, we will explain in detail the values produced by the UUID () function:

root@localhost > SELECT UUID(),UUID(),LENGTH(UUID()),CHAR_LENGTH(UUID())/G;*************************** 1. row ***************************UUID(): de7ee638-4322-11e0-85ab-842b2b4a7e75UUID(): de7ee642-4322-11e0-85ab-842b2b4a7e75LENGTH(UUID()): 36CHAR_LENGTH(UUID()): 361 row in set (0.00 sec)

As shown in the preceding execution result:

1. in the same SQL statement, the values obtained by calling the UUID () function in multiple places are different;

2. the random value is composed of five parts and the separator is a hyphen;

3. the values of the last two groups obtained after multiple calls or executions are the same. if the mysqld server is disabled and restarted, the group in the fourth group and the value before the restart are changed, then it remains unchanged. as long as you restart the mysqld service, it will change. In addition, for the same machine, the fifth group of values will never change;

4. the number of characters is 36, and the number of characters is 36 (note: the default character set encoding: utf8 );

The following describes the components of UUID values:

1. the first three sets of values are converted from timestamps;

2. The fourth group of values is to temporarily preserve the uniqueness of the timestamp. For example;

3. the fifth group of values is an IEE 802 node ID value, which is unique in space. If the latter is not available, replace it with a random number. If the host does not have a Nic, or we do not know how to obtain the machine address in a system, the uniqueness of the space cannot be guaranteed. even if this problem occurs, the chance of duplicate values is very small.

UUID functions support replication:

The UUID function is an uncertain function. Therefore, the STATEMENT mode of MySQL replication is not supported, but the MIXED and ROW modes are supported. you can set two groups of test modes to 5. 1. the series version is used as an example.

Test Command line mode replication: tx_isolation = REPEATABLE-READbinlog_format = STATEMENT test command line/MIXED mode replication: tx_isolation = REPEATABLE-READbinlog_format = MIXED OR ROW

Execute the same SQL statement on the master server:

INSERT INTO test_uuid(username) VALUES(UUID());

Then compare the values stored in the preceding table on the master and slave servers, and you will find that the master and slave data are consistent in the command line mode and in the row/hybrid mode;

Suggestion: in the replication mode, if you need the UUID () function, you must use the row/hybrid replication mode.

Glossary:

When the input parameters are the same and executed at the same time or multiple calls in an SQL statement, different worthwhile functions are called as undefined functions.

Note:

In MySQL. * and later versions, there is a variant UUID () function named UUID_SHORT (), which can generate a 17-64 unsigned integer.

For example:

root@localhost > SELECT UUID_SHORT()/G;*************************** 1. row ***************************UUID_SHORT(): 62186762502615859211 row in set (0.00 sec)

BitsCN.com