How to customize data to be inserted?

Question

I have this table for items and while inserting it, an ID field(aside from the index id used) should generate a combination of date and autoincrement value in this pattern

"yy-mm-xxx" where yy-last 2 digits of current year, mm-month, xxx-the autogenerated id.

Answer 1

The best way to achieve this is to have a separate column that would be updated by a trigger

CREATE TRIGGER before_insert_table_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
  SET new.id = <your_own_function_to_create_this_prefixed_id>;

Answer 2

Apart from the fact that you probably better off to store this id in separate columns and present it as needed (e.g. with a view) your words ...autoincrement value... maybe interpreted at least in two ways:

1. you have an auto_increment PK column id and you want to use its values while producing this compound secondary id
2. you have a PK column id (whether it's auto_increment or not) but you want to generate int values that are unique per combination of year and month.

---

If it's the first case you can solve this using a separate table for sequencing and a trigger like this

Table schema

CREATE TABLE items_seq (id int not null auto_increment primary key);
CREATE TABLE items (id int not null default 0 primary key, 
                    item_id varchar(9) default '', ...);

A trigger

DELIMITER $$
CREATE TRIGGER tg_bi_items
BEFORE INSERT ON items
FOR EACH ROW
BEGIN
  INSERT INTO items_seq (id) VALUES(NULL);
  SET NEW.id = LAST_INSERT_ID(),
      NEW.item_id = CONCAT(DATE_FORMAT(CURDATE(), '%y-%m-'),
                           LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

Now you just insert rows

INSERT INTO items (item_id) VALUES (NULL),(NULL);

And you'll get

| ID |   ITEM_ID |
------------------
|  1 | 13-08-001 |
|  2 | 13-08-002 |

Here is SQLFiddle demo

---

If its the second case then you can do it with a trigger like this

CREATE TRIGGER tg_bi_items
BEFORE INSERT ON items
FOR EACH ROW
  SET NEW.item_id = CONCAT(
    DATE_FORMAT(CURDATE(), '%y-%m-'),
    LPAD(COALESCE(
      (
        SELECT SUBSTRING_INDEX(MAX(item_id), '-', -1)
          FROM items
         WHERE item_id LIKE DATE_FORMAT(CURDATE(), '%y-%') -- based on your comments reset to 1 every year
      ), 0) + 1, 3, '0'));

With this approach you have to issue separate insert statements for each row, otherwise you end up with the same generated number.

INSERT INTO items (item_id) VALUES (NULL);
INSERT INTO items (item_id) VALUES (NULL);

You'll get

| ID |   ITEM_ID |
------------------
|  1 | 13-08-001 |
|  2 | 13-08-002 |

Here is SQLFiddle demo

Answer 3

Create an after insert trigger which should do an update the column based on the pattern you are making

Answer 4

I don't believe this can be accomplished with a trigger, either a BEFORE INSERT or AFTER INSERT. And the value assigned to the AUTO_INCREMENT column isn't available to expressions in the INSERT statement.

About the closest you are going to get (I think) is a separate UPDATE statement. The trick will be identifying the row(s) just inserted. One way would be to insert a NULL value to that column, and then update all rows that have a NULL in that column:

UPDATE mytable t 
   SET t.myidcol = CONCAT(DATE_FORMAT(NOW(),'%y-%i-'),t.id)
 WHERE t.myidcol IS NULL;

(You didn't specify where that date value was coming from; the statement above uses the current date from the system. If it's a date column in the table, then use that instead. The DATE_FORMAT function converts a date/datetime/timestamp into a character string, based on the format supplied in the second argument.

---

EDIT

I may have read more into the "(aside from the index id used)" and "autoincrement" than was intended. I took this to mean that the table has an ID column defined as AUTO_INCREMENT. If that's not the case, then the answer above doesn't really work.

(This is where more details in the question i.e. the output from SHOW CREATE TABLE, and some example data, showing the types of values that would be assigned (whether the autoincrement id is "reset" for each distinct value 'yy-mm-', or whether its continuously ascending.)

If the value of the autoincrement portion is derived from a mechanism other than AUTO_INCREMENT, then the assignment of the value to the column could probably be done in a BEFORE INSERT trigger.

---

With all that said, I question the need to store a column in this format. (There is no apparent requirement is being met by the addition of this column, no reason given why this column is needed.)

If the date and id are stored as separate columns in the table, the generation of the formatted string is trivial, and could be handled in the SELECT list of a query. I can't see that this would be very useful for ordering, given that ordering on the string value is not guaranteed to sort the id values within a given yy-mm- in numerical order, without splitting it back out.