我需要在表中创建一些测试数据。为了透明度,我想手动设置UUID主键为连续值,通过递增它们来实现。
这是我的代码:
DROP PROCEDURE IF EXISTS doiterate;
CREATE PROCEDURE doiterate()
BEGIN
DECLARE v_max int UNSIGNED DEFAULT 10;
DECLARE v_counter int UNSIGNED DEFAULT 0;
DECLARE orderId BINARY(16) DEFAULT 0xFE9DCCB10915B0FF11EDCF0000000000;
DECLARE heldUntil DATETIME DEFAULT '2023-03-31 08:36:35';
WHILE v_counter <= v_max DO
SET v_counter = v_counter + 1;
INSERT INTO experimental_held_orders
VALUES (orderId, heldUntil);
SET orderId = orderId + 1;
SET heldUntil = heldUntil + INTERVAL 1 MINUTE ;
END WHILE;
END;
CALL doiterate();
但是我收到了一个错误:
[22001][1292] Data truncation: Truncated incorrect DOUBLE value: '\xFE\x9D\xCC\xB1\x09\x15\xB0\xFF\x11\xED\xCF\x00\x00\x00\x00\x00'
请告诉我如何修复它。
附言:我不想使用AUTO_INCREMENT。
附言2:我的表结构。
CREATE TABLE `experimental_held_orders` (
`OrderId` binary(16) DEFAULT NULL,
`HeldUntilUtc` datetime DEFAULT NULL
)
更新 根据安德鲁的建议,我更改了我的初始值和代码:
DROP PROCEDURE IF EXISTS doiterate;
CREATE PROCEDURE doiterate()
BEGIN
DECLARE v_max int UNSIGNED DEFAULT 10;
DECLARE v_counter int UNSIGNED DEFAULT 0;
DECLARE orderId BINARY(16) DEFAULT 0x00000000000000000000000000000000;
DECLARE heldUntil DATETIME DEFAULT '2023-03-31 08:36:35';
DECLARE orderId_dec BIGINT;
WHILE v_counter <= v_max DO
SET v_counter = v_counter + 1;
INSERT INTO experimental_held_orders
VALUES (orderId, heldUntil);
SET orderId_dec = CONV(orderId, 16, 10);
SET orderId_dec = orderId_dec + 1;
SET orderId = CONV(orderId_dec, 10, 16);
SET heldUntil = heldUntil + INTERVAL 1 MINUTE ;
END WHILE;
END;
CALL doiterate();
但是这就是我得到的结果:
而我期望的是
0x00000000000000000000000000000000
0x00000000000000000000000000000001
0x00000000000000000000000000000002
等于0x00000000000000000000000000000003的十进制数是3。