Добавить столбец с ULID в существующую таблицу MySQL

Question

Я пытаюсь добавить столбец с ULID в существующую таблицу MySQL. Я знаю, как это сделать с помощью функции и запроса на обновление:

      delimiter //
DROP FUNCTION IF EXISTS ulid//
CREATE FUNCTION ulid () RETURNS CHAR(26) DETERMINISTIC
BEGIN
DECLARE s_hex CHAR(32);
SET s_hex = LPAD(HEX(CONCAT(UNHEX(CONV(ROUND(UNIX_TIMESTAMP(CURTIME(4))*1000), 10, 16)), RANDOM_BYTES(10))), 32, '0');
RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT(LPAD(CONV(SUBSTRING(s_hex, 1, 2), 16, 32), 2, '0'), LPAD(CONV(SUBSTRING(s_hex, 3, 15), 16, 32), 12, '0'), LPAD(CONV(SUBSTRING(s_hex, 18, 15), 16, 32), 12, '0')), 'V', 'Z'), 'U', 'Y'), 'T', 'X'), 'S', 'W'), 'R', 'V'), 'Q', 'T'), 'P', 'S'), 'O', 'R'), 'N', 'Q'), 'M', 'P'), 'L', 'N'), 'K', 'M'), 'J', 'K'), 'I', 'J');
END//
delimiter;

UPDATE mytable SET new_id=(SELECT ulid());

Однако мне нужно было бы сделать это с помощью одного запроса. Это вообще возможно?

Answer 1

Хотя вы используете значение 3 раза в своем окончательном выражении, и даже если оно содержит случайное значение, вы можете буквально повторно использовать формулу дляs_hex3 раза в вашей окончательной формуле:

      UPDATE mytable SET new_id = REPLACE(REPLACE(REPLACE( ...
  LPAD(CONV(SUBSTRING(  LPAD(HEX(..., 32, '0')  , 1, 2), 16, 32), 2, '0'),
  LPAD(CONV(SUBSTRING(  LPAD(HEX(..., 32, '0')  , 3, 15), 16, 32), 12, '0'), 
  LPAD(CONV(SUBSTRING(  LPAD(HEX(..., 32, '0')  , 18, 15), 16, 32), 12, '0')
), 'V', 'Z')..., 'I', 'J');

The random_bytes()-функция будет возвращать разные значения для 3 частей, но, поскольку нет перекрытия и все случайные значения действительны, весь результат остается действительным (например, если подстрока 2 использует первые 2 буквы случайной строкиABCDEFи подстрока 3 использует последние 4 буквы случайной строки123456, это то же самое, как если бы исходная случайная строка былаAB3456).

Окончательное выражение длинное и неприятное на вид, но работает. Если вы хотите (и имеете время), вы можете упростить его, напримерSUBSTRING(s_hex, 1, 2)является01на ближайшие 10 лет и02после этого, поэтому вам не нужно использовать для этого полное выражение.