MySQL: change column type using expression 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.
The correct syntax would be 

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


But you can't add many functions to a generated column, for example uuid() doesn't work, so you must do this in your code. where you generate the insert


Stored functions and user-defined functions are not permitted.
Stored procedure and function parameters are not permitted.
Variables (system variables, user-defined variables, and stored program local variables) are not permitted.
Subqueries are not permitted. 


See official Documentation