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?

score 9 · Answer 1 · answered Mar 08 '11 at 10:24

9

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

answered Mar 08 '11 at 10:24

Teson

6,644
8
46
69

That's fine. But there are tables with `GUID` as foreign key. How do I update those as the master table is updated? – Gaurav Sharma Mar 08 '11 at 11:41
(master table = PK?) why would you change the PK once set? – Teson Mar 08 '11 at 12:23
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, – Jeff Clayton Apr 20 '16 at 20:36
@Teson - Does not work for me. ALL records are created with the same GUID. – Duck May 07 '20 at 05:04
@DuckDucking , sounds strange. Quick google says you may suffer a bug: https://stackoverflow.com/questions/58600623/mysql-uuid-function-produces-the-same-value-when-used-as-a-function-parameter – Teson May 07 '20 at 13:23
@Teson, this is what I am suspecting. .THANKS – Duck May 08 '20 at 05:28

score 4 · Answer 2 · answered Mar 08 '11 at 09:37

4

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');

answered Mar 08 '11 at 09:37

Alaa Jabre

1,843
5
26
52

score 3 · Answer 3 · answered Jan 28 '21 at 06:10

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.

MySQL - autoincrement to guid

3 Answers3

Linked