A detailed explanation of the MySQL uuid function (goto)

A detailed description of the MySQL UUID function

There are two types of tools that can be used to generate unique value properties in MySQL: the UUID () function and the self-increment sequence, so what is the difference between the two? We compare the respective characteristics and similarities and differences:

L can realize the function of generating unique value;

L UUID is a value that can generate unique values in time and space, and the self-increment sequence can only generate

One value, and it needs to be paired with a unique primary key or unique index;

The implementation is different, the UUID is a random + rule combination, and the self-increment sequence is to control a value gradually increase;

L UUID produces a string type value, fixed length is: 36 characters, and the self-increment sequence produces the integer type value, the length is determined by the field definition property;

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

[Email protected]: (none) 06:09:40> SELECT uuid (), uuid (), LENGTH (UUID ()), Char_length (UUID ()) \g

1. Row ***************************

(Note: Where \g represents the end reference of the previous match built: http://wenku.baidu.com/view/f5e5dcf07c1cfad6195fa711.html)

UUID (): De7ee638-4322-11e0-85ab-842b2b4a7e75

UUID (): De7ee642-4322-11e0-85ab-842b2b4a7e75

LENGTH (UUID ()): 36

Char_length (UUID ()): 36

1 row in Set (0.00 sec)

from the information in the execution results section above, see :

In the same SQL statement, multiple calls to the UUID () function get different values;

l The resulting random value is composed of 5 parts, and the delimiter bit is: Middle Dash;

The following 2 sets of values are the same for multiple invocations or executions, and if the MYSQLD server is shut down and restarted, it will be found that the fourth group and the value before the reboot have changed, and then have not changed, as long as the restart of the MYSQLD service will change. In addition, for the same machine, the fifth set of values will always be unchanged;

L The number of characters is: 36, the number of bytes is: 36 (Note: System default character Set encoding: UTF8);

For the value components generated by the UUID, make the following explanations:

L The first three sets of values are time stamps converted;

The fourth set of values is the uniqueness of the temporary hold timestamp. For example, using daylight saving time;

The fifth set of values is a node identity value of IEE 802, which is spatially unique. If the latter is not available, replace it with a random number. If the host does not have a network card, or we do not know how to get the machine address under a system, then the space uniqueness can not be guaranteed, even if this yang, the probability of duplicate values is very small.

The UUID function supports replication:

The UUID function is a nondeterministic function, which does not support the statement mode of MySQL replication, but supports mixed, row two mode, you can set 2 sets of test mode, 5.1. Series version for example.

Test-based command-line mode replication:

Tx_isolation = Repeatable-read

Binlog_format = STATEMENT

Test-based command-line/mixed-mode replication:

Tx_isolation = Repeatable-read

Binlog_format = MIXED OR ROW

Execute the same SQL statement on the primary server:

INSERT into Test_uuid (username) VALUES (uuid ());

Then, compared to the values stored in the table on the master-slave server, the command-line mode is found: The master is never consistent, based on the row/Mixed mode: The master-slave data is consistent;

Recommendation: in copy mode, the UUID () function is required, so you must use the row/Mixed mode replication method.

Noun Explanation:

For input parameters of the same, and at the same time execution or a number of calls in SQL, and get a different merit function, we call it: nondeterministic function .

Note:

In MySQL 5.1.* and later versions there is a variant of the UUID () function, Name: Uuid_short (), which generates an 64-bit unsigned integer, for example:

[Email protected]: (none) 02:46:42> SELECT uuid_short () \g

1. Row ***************************

Uuid_short (): 6218676250261585921

1 row in Set (0.00 sec)

Subsequent raises:

The UUID () function produces a value that does not fit as the primary key for the InnoDB engine table, and for detailed reasons, read the article InnoDB engine table primary key selection (http://www.mysqlops.com/2011/09/10/innodb-primary.html).

Subsequent additions:

In MySQL, you can use the UUID to generate the primary key, but with the MySQL uuid () function, the generated UUID is 36 bits, which contains 32 characters and 4 separators (-), often this delimiter is useless to us, You can use the Replace function that comes with MySQL to remove the delimiter

Replace (UUID (), '-', ')----> '-' in the UUID (), which is replaced by an empty string;

In addition

Upper (replace (UUID (), '-', ')) is used to convert characters to uppercase

Article Source: http://www.mysqlops.com/2011/03/01/mysql-uuid.html

A detailed explanation of the MySQL uuid function (goto)