MySQL – Change Column Type Using Expression

alter-tableMySQL

In a MySQL database, I need to convert some UUID columns (BINARY(16)) into VARCHAR. I want to convert them in the canonical form, not as bytes. For clarity, in lowercase and with hyphens, and not in the 0xHEXADECIMAL format.

In Postgres, this is enough:

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

The MySQL equivalent returns an error:

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

And even if it worked, the result wouldn't really be in canonical form.

I know that in Postgres I can resolve this with 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);

And it does what you'd expect.

I can't find a way to do something similar in MySQL.

Best Answer

Testing out $tompave's Comment; it seems to work:

First, create and populate:

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)

Now to test:

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>
Related Question