MySQL - autoincrement to guid

Question

I have a table with an auto-increment ID field as shown below.

+------------+-------------------------------------+
| company_id | name                                |
+------------+-------------------------------------+
|          1 | International Client                |
|          2 | Oracle                              |
|          3 | test                                |
|          4 | testabc                             |
|          5 | testdef                             |
|          6 | abcd                                |
+------------+-------------------------------------+

I want to update the ID column to be a GUID using the

uuid()

function.

Additionally, how do I update the foreign key references to the correct GUID?

Sarmentose

Answer 1

Use triggers.

CREATE TABLE `tbl_test` (
  `GUID` char(40) NOT NULL,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY (`GUID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

table and pk, now trigger..

DELIMITER //
CREATE TRIGGER `t_GUID` BEFORE INSERT ON `tbl_test`
 FOR EACH ROW begin
 SET new.GUID = uuid();
end//
DELIMITER ;

Now try,

insert into tbl_test(Name) value('trigger happy...');

regards, /t

Malayan

That's fine. But there are tables with GUID as foreign key. How do I update those as the master table is updated? – Sarmentose

(master table = PK?) why would you change the PK once set? – Malayan

If you wish to add an auto-time/date stamp for 'creation of data', this worked adding this line after the GUID line: Created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, – Diverticulum

@Malayan - Does not work for me. ALL records are created with the same GUID. – Chandless

@DuckDucking , sounds strange. Quick google says you may suffer a bug: #58601123 – Malayan

@Teson, this is what I am suspecting. .THANKS – Chandless

Answer 2

you can't use it with autoincrement

guid is char not intger

you need to insert it your self

also you will need to change the id to char(40)

insert into table_name (id,name) values (uuid(),'jon');

Rastus

Answer 3

In mysql 8.0 you can use something like below:

For UUID without dashes:

create table temp (`company_id` VARCHAR(64) NOT NULL DEFAULT (REPLACE(UUID(),'-','')), 'name' varchar(100) NOT NULL);

For UUID with dashes:

create table temp (`company_id` VARCHAR(64) NOT NULL DEFAULT (UUID()), 'name' varchar(100) NOT NULL);

I hope this answers your question, Let me know if it is not or you need more.

Teishateixeira