MySQL:使用表达式更改列类型

Question

在MySQL数据库中，我需要将一些UUID列(BINARY(16))转换为VARCHAR。我想以规范的形式，而不是以字节的形式来转换它们。为了清晰起见，使用小写和连字符，而不是0xHEXADECIMAL格式。

在Postgres，这就足够了：

ALTER TABLE my_table
  ALTER COLUMN my_uuid_column
  TYPE VARCHAR(36);

MySQL等效值返回一个错误：

ALTER TABLE my_table
  MODIFY COLUMN my_uuid_column
  TYPE VARCHAR(36);

ERROR 1366 (HY000): Incorrect string value: '\x.....' for column 'my_uuid_column' at row 1

即使它成功了，结果也不会是规范形式的。

我知道，在Postgres中，我可以使用USING expression来解决这个问题：

CREATE FUNCTION foobar(foo uuid) returns VARCHAR
    LANGUAGE SQL IMMUTABLE STRICT AS $
  SELECT 'foobar' -- format the UUID using some helpers.
$;

ALTER TABLE my_table
  ALTER COLUMN my_uuid_column
  TYPE VARCHAR(36)
  USING foobar(my_uuid_column);

它能达到你的预期。

在MySQL中，我找不到一种类似的方法。

Answer 1

测试$ Testing的评论；这似乎是可行的：

首先，创建并填充：

mysql> CREATE TABLE test_uuid (
    ->     my_uuid BINARY(16)
    ->     );
Query OK, 0 rows affected (0.03 sec)

mysql>    
mysql> INSERT INTO test_uuid (my_uuid) VALUES (UUID_TO_BIN(UUID()));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_uuid (my_uuid) VALUES (UUID_TO_BIN(UUID()));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_uuid (my_uuid) VALUES (UUID_TO_BIN(UUID()));
Query OK, 1 row affected (0.00 sec)

mysql>     
mysql> SELECT HEX(my_uuid) FROM test_uuid;
+----------------------------------+
| HEX(my_uuid)                     |
+----------------------------------+
| 5076425CA52D11EA90780242AC11000A |
| 5076D2CBA52D11EA90780242AC11000A |
| 5077762AA52D11EA90780242AC11000A |
+----------------------------------+
3 rows in set (0.00 sec)

现在来测试：

mysql> ALTER TABLE test_uuid
    ->     ADD COLUMN uhex CHAR(36) COLLATE ascii_general_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE test_uuid
    ->     SET uhex = BIN_TO_UUID(my_uuid);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql>     
mysql> SELECT HEX(my_uuid), uhex FROM test_uuid;
+----------------------------------+--------------------------------------+
| HEX(my_uuid)                     | uhex                                 |
+----------------------------------+--------------------------------------+
| 5076425CA52D11EA90780242AC11000A | 5076425c-a52d-11ea-9078-0242ac11000a |
| 5076D2CBA52D11EA90780242AC11000A | 5076d2cb-a52d-11ea-9078-0242ac11000a |
| 5077762AA52D11EA90780242AC11000A | 5077762a-a52d-11ea-9078-0242ac11000a |
+----------------------------------+--------------------------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> ALTER TABLE test_uuid
    ->     DROP COLUMN my_uuid,
    ->     RENAME COLUMN uhex TO my_uuid;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_uuid;
+--------------------------------------+
| my_uuid                              |
+--------------------------------------+
| 5076425c-a52d-11ea-9078-0242ac11000a |
| 5076d2cb-a52d-11ea-9078-0242ac11000a |
| 5077762a-a52d-11ea-9078-0242ac11000a |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> SHOW CREATE TABLE test_uuid\G
*************************** 1. row ***************************
       Table: test_uuid
Create Table: CREATE TABLE `test_uuid` (
  `my_uuid` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> 

Answer 2

正确的语法是

ALTER TABLE my_table1 MODIFY COLUMN my_uuid_column VARCHAR(36) GENERATED ALWAYS AS (1 +1)  STORED;

但是您不能将许多函数添加到生成的列中，例如，uuid()不能工作，所以您必须在代码中这样做。生成插入的位置。

• 不允许存储函数和用户定义函数。
• 不允许存储过程和函数参数。
• 不允许变量(系统变量、用户定义变量和存储的程序局部变量).
• 子查询是不允许的。

见官方文档