Change the default value of a string column to real uuid()

Question

When I use show create table Posts I see this column:

`Slug` varchar(400) NOT NULL DEFAULT 'uuid()'

This means that the default value is a string. I want the default string to be a real uuid() function.

I want it to become:

`Slug` varchar(400) NOT NULL DEFAULT uuid()

How can I do that? What query should I write for this? No matter what I do inside the phpMyAdmin or Adminer it does not become what I want.

---

I have a column that has 'uuid()' as its default value. I want to change to so that it becomes uuid(). I don't want to create a new column. I don't want to drop the column and recreate it. I want to modify it.

Answer 1

You have to define the datatype to be UUID (available since MariaDB 10.7.0, see documentation). I don't know if PHPMyAdmin or Adminer support that, but you can definitely use the mariadb command-line client:

CREATE TABLE Posts (
  Slug UUID NOT NULL DEFAULT uuid()
);

Then insert a row with no value for the column, and it will create a UUID value for you using the uuid function, as we defined for the default:

INSERT INTO Posts() VALUES ();

SELECT * FROM Posts;
+--------------------------------------+
| Slug                                 |
+--------------------------------------+
| 11a814f3-e42a-11ed-89f9-721e764263d8 |
+--------------------------------------+
1 row in set (0.001 sec)

To change an existing column, use an ALTER TABLE statement:

ALTER TABLE Posts
  Slug UUID NOT NULL DEFAULT uuid();

(Caution: You may want to test that the above statement doesn't destroy your existing data if you have important/production data in the table already.)

Answer 2

With MySQL 8, I use:

uuid_bin1 BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1))

if that column is the Primary Key.

Answer 3

You cannot do this, but you can use a trigger to perform that action, like this:

CREATE TRIGGER before_insert_Posts
  BEFORE INSERT ON Posts 
  FOR EACH ROW
  SET new.Slug = uuid();

See Can I use a function for a default value in MySql? on Stack Overflow.

Answer 4

In Mysql 8.0, indicate that you have an "expression" by parenthesizing it:

Slug UUID NOT NULL DEFAULT (uuid())