Generate a unique string with integer as postfix for column in MySQL

Question

I want to generate a unique incremental string value for one column in MySQL table. The format of this key is {STRING}-{INT}, for example, FOO-1, FOO-2, and so on.

Below is my table structure:

CREATE TABLE IF NOT EXISTS `items` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `item_key` varchar(100) NOT NULL,
  `title` tinytext NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `item_key` (`item_key`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

The requirement here is, when a new record is inserted, it's value of 'item_key' should be calculated as:

Step-1: Find the last record

Step-1.1

If there is, get the value of 'item_key' and increment it's integer part and set it for new record

Step-1.2

Else set FOO-1 as item_key

There are two options to achieve this as per my knowledge: 1. MySQL Triggers 2. Using PHP-MySQL to manually query for last record and set new value in insert query

I prefer first option - MySQL triggers - Before Insert. I have created a trigger for this.

DELIMITER $$

CREATE TRIGGER generate_item_key
BEFORE INSERT
    ON items FOR EACH ROW

BEGIN

    DECLARE itemKey varchar(10);
    DECLARE lastItemKey varchar(100) DEFAULT "";
    DECLARE lastItemNum varchar(20) DEFAULT "";

    SET itemKey = "FOO";

    -- Find the last item
    SET lastItemKey = (SELECT item_key FROM items WHERE item_key != "" ORDER BY created DESC LIMIT 1);

    IF (lastItemKey <> '') THEN
        SET lastItemNum = CONVERT(REPLACE(lastItemKey, CONCAT(itemKey, "-"), ""), UNSIGNED);
    ELSE
        SET lastItemNum = 0;
    END IF;

    -- Set new issue key
    SET New.item_key = CONCAT(itemKey, "-", lastItemNum + 1);

END; $$

DELIMITER ;

This works fine with single record insert query. For example,

INSERT INTO `items` (`id`, `item_key`, `title`, `created`, `modified`) VALUES (NULL, 'key', 'First item', '2015-07-14 00:00:00', '2015-07-14 00:00:00');

It generates FOO-1 for item_key column.

But, the question is, it is not working for multiple insert query. The reason is that there is a UNIQUE key applied on that column, and trigger generates same key for all records. For example,

INSERT INTO `items` (`id`, `item_key`, `title`, `created`, `modified`) VALUES (NULL, 'key', 'First item', '2015-07-14 00:00:00', '2015-07-14 00:00:00'),(NULL, 'key2', 'Second item', '2015-07-14 00:00:00', '2015-07-14 00:00:00'),(NULL, 'key3', 'Third item', '2015-07-14 00:00:00', '2015-07-14 00:00:00'),(NULL, 'key', 'First item', '2015-07-14 00:00:00', '2015-07-14 00:00:00');

Can anyone please suggest here? What can be the best and fast solution to achieve this?

If anyone has better solution other than above two, please suggest also.

Thank you

You can use another table (1 field 1 column) with the next number to be inserted and query+increment it in the before insert trigger. You will need to either use `SELECT .. FOR UPDATE` and then incrment or a single statement (incrment and fetch) to make sure there are no gaps and duplicates. — Vatev, Jul 14 '15 at 11:38
Will it work with multi insert query? Is adding a new table will be good idea here? — Narendra Vaghela, Jul 14 '15 at 11:40
It will work with multi insert. It is possible to lock the last row in the `items` table instead, but you will need another field for the un-prefixed number (otherwise you can't get the last one). — Vatev, Jul 14 '15 at 12:00
Thanks @Vatev. Using an intermediate table is good and it worked also. — Narendra Vaghela, Jul 14 '15 at 13:01

score 1 · Answer 1 · answered Jul 14 '15 at 11:21

1

If the gap between item key number being generated is not cared, you can make use of the newly generated primary key value as part of the item_key name.

Example:

DELIMITER $$

CREATE TRIGGER bi_items  --  generate_item_key 
       BEFORE INSERT ON items 
       FOR EACH ROW
BEGIN

    DECLARE itemKey varchar(10);
    DECLARE _key_id INT DEFAULT 0;

    SET itemKey = "FOO";

    -- Find which new id is being assigned for PK column
    SELECT AUTO_INCREMENT INTO _key_id
      FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_NAME = 'items'
       AND TABLE_SCHEMA = DATABASE();

    -- Set new issue key
    SET New.item_key = CONCAT( itemKey, "-", _key_id );

END;
$$

DELIMITER ;

Note: Auto increment field values are not rolled back on errors. And hence on next insertion requests after on an insert error, previously generated auto incremented and discarded values will not be used.

answered Jul 14 '15 at 11:21

Ravinder Reddy

23,692
6
52
82

This doesn't look very thread safe. What happens when two rows are inserted at the same time? – Vatev Jul 14 '15 at 11:28
`INFORMATION_SCHEMA.TABLES` can get modified by another thread between the AUTO_INC number generation and the trigger fetching it. If two threads insert at the same time the sequence of events can become `AUTO_INC > AUTO_INC > fetch > fetch` which will result in both records having the same `item_key`. – Vatev Jul 14 '15 at 11:35
@Vatev: When a table can only have *ONE* time based event trigger, how can it be justified that two threads insert at the same time? – Ravinder Reddy Jul 14 '15 at 11:37
It can have only one trigger which can be executed multiple times at the same moment (by different threads inserting rows). – Vatev Jul 14 '15 at 11:39
*@Vatev*: And what about *FOR EACH ROW* ? – Ravinder Reddy Jul 14 '15 at 11:40
The syntax debate is pointless. MySQL can and will insert multiple rows in parallel, triggers which don't block on other locks will also run in parallel. – Vatev Jul 14 '15 at 11:49
**@Vatev**: I appreciate your input. I also found [*a question on SO*](http://stackoverflow.com/questions/28168477/is-this-mysql-trigger-thread-safe) with the same arguments as you did. But OP did only post the observations. I suggest you post any resource link, if you know, to support that triggers are not thread safe. – Ravinder Reddy Jul 15 '15 at 03:25

Generate a unique string with integer as postfix for column in MySQL

1 Answers1