1

I am trying to create a user-defined function in MySQL in order to generate unique numbers. As the auto_increment feature of MySQL just increments its seed by one, I need to have such a function to handle fields that need to be incremented by more than one. Here is my SQL script:

DELIMITER $$
CREATE FUNCTION `getUniqueID`(
    id_type CHAR(1)
) RETURNS INT(10)
BEGIN
    DECLARE run INT(10);

    START TRANSACTION;

        SELECT unique_gen_id INTO @run FROM tbl_unique_seed WHERE id_type = @id_type;
    UPDATE tbl_unique_seed SET unique_gen_id = (unique_gen_id + 1) WHERE id_type = @id_type;

    COMMIT;

    RETURN run;
END$$
DELIMITER ;

I do not have much experience in MySQL, but creating this kind of functions in SQL Server is quite easy. It will be nice of you to help me figure out a solution for this issue. Currently, I am facing some syntax errors. The most basic ones relate to the syntax of my transaction and the select statement.

samanca
  • 153
  • 3
  • 11
  • I have tried to use a table to store seeds and increment it by this function. However, it seems I have some syntax errors that prevents me from creating this function. – samanca Mar 10 '13 at 08:36
  • Edit your question and say what you've tried (you mentioned syntax errors, make sure to include those). – BDM Mar 10 '13 at 09:12

1 Answers1

0

See the docs here: http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_increment

You can change the value of auto_increment_increment, which is normally 1.

kainaw
  • 4,256
  • 1
  • 18
  • 38
  • According to the link you provided, _auto_increment_increment_ is used to set the increment value for the whole session or the database, but I need to have variable increments for my tables. – samanca Mar 09 '13 at 06:17