Mysql 简明教程
MySQL - UUID
The MySQL UUID function
MySQL UUID() 函数用于根据 RFC 4122 生成“通用唯一标识符”(UUID)。UUID 被设计成在不同的服务器上生成时也是全局唯一的。UUID 是使用当前时间戳、服务器的唯一标识符和随机数组合生成的。
The MySQL UUID() function is used to generate "Universal Unique Identifiers" (UUIDs) in accordance with RFC 4122. UUIDs are designed to be universally unique, even when generated on different servers. The UUID is generated using a combination of the current timestamp, the unique identifier of the server, and a random number.
UUID Format
UUID 值表示为 UTF-8 字符串，并且是一个 128 位的数。UUID 值的格式为十六进制数，它由五个用连字符分隔的段组成。
The UUID value is represented as a UTF-8 string and is a 128-bit number. The format of the UUID value is in hexadecimal number, and it consists of five segments which are separated by hyphens.
UUID 值的一般格式为： aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee ，其中每个段表示一个十六进制值。
The general format of the UUID value is: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee, where each segment represents a hexadecimal value.
Generating a UUID
以下是使用 MySQL 中的 UUID() 函数生成 UUID 的基本示例 -
Following is the basic example to generate a UUID using the UUID() function in MySQL −
SELECT UUID();UUIDs in a Database Table
您可以在数据库表中使用 UUID 作为唯一标识符。以下是如何创建具有 UUID 列的表和插入数据的示例 -
You can use UUIDs as unique identifiers in a database table. Following is an example of how to create a table with a UUID column and insert data −
在这里，我们首先使用以下查询创建一个名为“ORDERS”的表，其中 ORDER_ID 列的类型为 VARCHAR -
Here, we are first creating a table with the name "ORDERS", with an ORDER_ID column of type VARCHAR using the following query −
CREATE TABLE ORDERS(
ID int auto_increment primary key,
NAME varchar(40),
PRODUCT varchar(40),
ORDER_ID varchar(100)
);现在，我们使用 UUID() 函数为 ORDER_ID 列生成唯一值，将数据插入到 ORDERS 表 -
Now, we are inserting data into the ORDERS table, using the UUID() function to generate unique values for the ORDER_ID column −
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Varun", "Headphones", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Priya", "Mouse", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Nikhil", "Monitor", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Sarah", "Keyboard", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Vaidhya", "Printer", UUID());得到的 ORDERS 表如下 -
Following is the ORDERS table obtained −
Modifying UUIDs
您可以在不丢失唯一性的情况下修改 UUID。例如，您可以使用 REPLACE() 和 TO_BASE64() 等函数删除连字符或将其转换为 base64 编码。
You can modify UUIDs without losing their uniqueness. For example, you can remove hyphens or convert them to base64 notation using functions like REPLACE() and TO_BASE64().
Example
在此，我们使用以下查询更新了 ID = 1 的记录的 UUID 值：
Here, we are updating the UUID value for the record where ID = 1 using the following query −
UPDATE ORDERS SET ORDER_ID = UUID() WHERE ID=1;Output
以下是上面代码的输出： -
Following is the output of the above code −
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Verification
要验证修改后的 UUID 值，我们可以使用以下 SELECT 查询：
To verify the modified UUID values, we can use the following SELECT query −
SELECT * FROM ORDERS;正如我们在下面的输出中看到的，每当我们执行 UUD() 函数时，我们得到一个不同的 UUID 值：
As we can see in the output below, every time we execute the UUID() function, we get a different UUID value −
Example
假设先前创建的表，让我们使用 REPLACE() 函数（如下所示）从 ID = 2 的行的 UUID 中删除连字符：
Assume the previously created table and let us remove hyphens from the UUID of the row with ID = 2 using the REPLACE() function as shown below −
UPDATE ORDERS
SET ORDER_ID = REPLACE(UUID(), '-', '')
WHERE ID = 2;Output
上述代码的输出如下：
Output of the above code is as follows −
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Verification
要验证修改后的 UUID 值，我们可以使用以下 SELECT 查询：
To verify the modified UUID value, we can use the following SELECT query −
SELECT * FROM ORDERS;正如我们在下面的输出中看到的，行 = 2 的 UUID 被修改了，而没有破坏其“唯一”部分：
As we can see in the output below, the UUID of row = 2 is modified without disturbing the "unique" part of it −
Example
在以下查询中，我们使用 TO_BASE64() 函数将 ID = 4 的 UUID 转换为 base64 表示法：
In the following query, we are converting the UUID of ID = 4 to base64 notation using the TO_BASE64() function −
UPDATE ORDERS
SET ORDER_ID = TO_BASE64(UNHEX(REPLACE(UUID(),'-','')))
WHERE ID=4;