Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL - autoincrement to guid

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?

like image 483
Gaurav Sharma Avatar asked Mar 08 '11 09:03

Gaurav Sharma


People also ask

What does Autoincrement do in MySQL?

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

How do I reset Autoincrement to 1?

In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.

How do I set Autoincrement value?

In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.

How can reset primary key ID after delete the row?

ALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.


2 Answers

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

like image 184
Teson Avatar answered Oct 19 '22 22:10

Teson


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');
like image 20
Alaa Jabre Avatar answered Oct 19 '22 22:10

Alaa Jabre