英文:
Append a column with an ULID to an existing MySQL table
问题
我正在尝试向现有的MySQL表中附加一个具有ULID的列。我知道如何使用函数和更新查询来实现：
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());
然而，我需要使用单个查询来执行此操作。这是否可能？
翻译后的内容如下：
我正在尝试向现有的MySQL表中附加一个具有ULID的列。我知道如何使用函数和更新查询来实现：
```sql
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());
然而，我需要使用单个查询来执行此操作。这是否可能？
<details>
<summary>英文:</summary>
I'm trying to append a column with an ULID to an existing MySQL table. I know how to do it with a function and an update query:
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());
However, I'd need to do it with a single query. Is it even possible?
</details>
# 答案1
**得分**: 1
虽然您在最终表达式中三次使用了`s_hex`的值，即使它包含随机值，您仍然可以在最终公式中直接重复使用`s_hex`的公式三次：
```sql
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');
random_bytes()函数将返回三个部分的不同值，但由于它们没有重叠，且所有随机值都有效，因此整个结果仍然有效（例如，如果子字符串2使用随机字符串ABCDEF的前2个字母，子字符串3使用随机字符串123456的最后4个字母，这与原始随机字符串AB3456是相同的）。
最终的表达式很长，不容易阅读，但是有效。如果您愿意（并且有时间），可以进一步简化它，例如，SUBSTRING(s_hex, 1, 2)在接下来的10年内是01，之后是02，因此您不需要使用完整的表达式来表示它。
英文:
Although you are using the value for s_hex 3 times in your final expression, and even if it contains a random value, you can just literally reuse the formula for s_hex 3 times in your final formula:
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()-function will return different values for the 3 parts, but since there is no overlap, and all random values are valid, the whole result is still valid (e.g. if substring 2 uses the first 2 letters of a random string ABCDEF and substring 3 uses the last 4 letters of a random string 123456, it is the same as if the original random string had been AB3456).
The final expression is long and not nice to look at, but works. If you want (and have the time), you can simplify it further, e.g. SUBSTRING(s_hex, 1, 2) is 01 for the next 10 years, and 02 after that, so you do not need to use the full expression for that.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论