1

I saw another post explaining the use of triggers to create prefixed IDs
How to make MySQL table primary key auto increment with some prefix

http://sqlfiddle.com/#!2/0ed88/1

I am really new to triggers and I would like to find out if there's a way to have the prefix be the Year-Month (YYMM) instead of the preset 4 letters "LHPL" as written in the SQLfiddle?

Much appreciated! Lots to learn in this mysql journey!

    CREATE TABLE table1_seq
    (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    )|

    CREATE TABLE Table1
    (
      id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
    )|


    CREATE TRIGGER tg_table1_insert
    BEFORE INSERT ON table1
    FOR EACH ROW
    BEGIN
      INSERT INTO table1_seq VALUES (NULL);
      SET NEW.id = CONCAT('LHPL', LPAD(LAST_INSERT_ID(), 3, '0'));
    END |


    INSERT INTO Table1 (name) VALUES ('Jhon'), ('Mark')|
Community
  • 1
  • 1
user3448267
  • 191
  • 1
  • 1
  • 15

1 Answers1

2

replace

'LHPL'

with

DATE_FORMAT(NOW(),'%y%m')

The NOW() and DATE_FORMAT() functions are documented here:

Reference: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • perfect! thanks. i m really new at triggers and was under the impression i needed have another step to have this inserted using SELECT current_date etc.. never knew I could just use the function in there directly! Thanks for the great help! – user3448267 Apr 21 '14 at 21:56
  • As a side note and not related to the question you asked: I personally wouldn't use a generated "yymmnnn" column as a PRIMARY KEY. (For one thing, it only allows for 1,000 distinct values per month. Note that LPAD will shorten a string to "n" characters.) Secondly, I prefer to use primary keys that are entirely anonymous, and have no "meaning". – spencer7593 Apr 21 '14 at 22:08