help creating a custom auto increment function in mysql

Question

Hello,

I would like to create an auto increment function that allows me to maintain uniqueness in designated columns across multiple tables.

I understand that this is not supported under mysql's standard functions, but it appears that this can be done by hand. Could someone point me in the right direction?

Thanks

Answer 1

You have a few choices. One, you could make the id a UUID (a 36+ varchar field), and use MySQL's UUID() function. Another option is to use a table as kind of a sequence generating table, allowing you to use the auto_increment features, and just use whatever is generated when instering into this table as a key elsewhere.

Personally, I'd opt for a UUID as that's a pretty common way to have globally unique identifiers. A word of warning: you may be better off generating these UID's in your client's language instead of using MySQL's implementation.

Answer 2

what's the problem with setting column (ID) to primary/unique and auto_increment?

ps: moreover, if you require uniqueness among multiple tables, you are probably doing something incorrectly.

Answer 3

Think this can be something like this

delimiter $$
CREATE PROCEDURE myProc()
BEGIN
DECLARE mid INT;
START TRANSACTION;
select MAX(id) + 1 INTO mid (  SELECT id FROM tbl1 UNION ALL SELECT id from tbl2 ... ) tbls;
INSERT INTO <somt_tbl> VALUES ( mid, ... ); 
COMMIT;
END$$