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?

Answer 1

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

Answer 2

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