Default Value of Guid in for a Column in MySQL

default value of GUID in for a column in mysql

Being that UUID() isn't accepted as a DEFAULT constraint, you need to use a trigger.

This one sets the value for the NEW_TABLE.uuid column:

delimiter $$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `example`.`newid`
BEFORE INSERT ON `example`.`new_table`
FOR EACH ROW
BEGIN
  SET NEW.`uuid` = UUID();
END
$$

Set some default guid value in uniqueidentifier in sql

Here's your script.

ALTER TABLE TestTable drop COLUMN TestID 

ALTER TABLE TestTable ADD TestID uniqueidentifier DEFAULT cast('DA74F684-B228-48D5-9692-69465BE6D720' as uniqueidentifier);

then, update exsting records

UPDATE TestTable  set TestID = 'DA74F684-B228-48D5-9692-69465BE6D720'

NHibernate: How to insert C# [Guid] into MySQL [BINARY(16) DEFAULT (uuid_to_bin(uuid(),1))] column?

By default, MySql.Data will store a Guid as CHAR(36). You can use BINARY(16) instead by specifying Old Guids = True; in your connection string.

From Connector/NET 8.0 Connection Options Reference:

The back-end representation of a GUID type was changed from BINARY(16) to CHAR(36). This was done to allow developers to use the server function UUID() to populate a GUID table - UUID() generates a 36-character string. Developers of older applications can add 'Old Guids=true' to the connection string to use a GUID of data type BINARY(16).

Can I use a function for a default value in MySql?

No, you can't.

However, you could easily create a trigger to do this, such as:

CREATE TRIGGER before_insert_app_users
  BEFORE INSERT ON app_users 
  FOR EACH ROW
  SET new.api_key = uuid();

MySQL 8.0.13: Default Value as uuid not working

This is unfortunately a bug with default expressions for primary key columns, Expression Default is made NULL during CREATE TABLE query, if field is made PK.

It is fixed in MySQL 8.0.19:

For a column defined as a PRIMARY KEY in a CREATE TABLE statement, a default value given as an expression was ignored. (Bug #29596969, Bug #94668)

As a workaround (if you cannot upgrade), you can add the primary key afterwards with an ALTER TABLE-statement:

CREATE TABLE `session` (
  `id` binary(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), TRUE)),
  `start_timestamp` timestamp NOT NULL, 
  `end_timestamp` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `session` ADD PRIMARY KEY(`id`);

---

Related Topics