MYSQL: increment BINARY variable

Question

I need to create some test data in the table. For transparency I want to manually set UUID primary keys to consecutive values by incrementing them.

Here is my code:

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();

But I receive an error:

[22001][1292] Data truncation: Truncated incorrect DOUBLE value: '\xFE\x9D\xCC\xB1\x09\x15\xB0\xFF\x11\xED\xCF\x00\x00\x00\x00\x00'

Please tell me how I can fix it.

PS I don't want to use AUTO_INCREMENT

PPS My table structure

CREATE TABLE `experimental_held_orders` (
  `OrderId` binary(16) DEFAULT NULL,
  `HeldUntilUtc` datetime DEFAULT NULL
)

UPDATE Following Andrew's advice I changed my initial value and my code to:

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();

But this is what I get:

While I expect

0x00000000000000000000000000000000
0x00000000000000000000000000000001
0x00000000000000000000000000000002

etc

because 0x00000000000000000000000000000003 to decimal is 3.

Answer 1

You could convert hex variable to decimal, increment it and convert back.

SET orderId_dec = CONV(orderId, 16, 10);
SET orderId_dec = orderId_dec + 1;
SET orderId = CONV(orderId_dec, 10, 16);

But your initial value 0xFE9DCCB10915B0FF11EDCF0000000000 is bigger than MySQL BIGINT UNSIGNED, so it won't work.

Consider to generate UUID by builtin MySQL UUDI() function and use it as a key.

SET orderId = UUID();