how to create AUTO increment ID number

Question

how to create AUTO increment ID number compose of year and number, example: "2012-0001" it will auto_increment to "2012-0002" if i input another data.

Thank You!

Answer 1

you're going to have to write a program to do that. Strawberry's solution shows you what needs to be done on the SQL side, but you have to write your interface (non-sql) to break apart the input, so that when the user inputs 2012-0001, the query string says something like "WHERE year=2012 AND id=0001"

if you're asking if you can have a column in your table that can have a hyphen in it yet auto-increment, the answer is no.

Answer 2

Using MyISAM, you can do this...

CREATE TABLE myisam_example(year INT NOT NULL,id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(year,id)) ENGINE=MyISAM;

INSERT INTO myisam_example VALUES (2012,NULL);
INSERT INTO myisam_example VALUES (2012,NULL);
INSERT INTO myisam_example VALUES (2012,NULL);
INSERT INTO myisam_example VALUES (2012,NULL);
INSERT INTO myisam_example VALUES (2012,NULL);
INSERT INTO myisam_example VALUES (2013,NULL);
INSERT INTO myisam_example VALUES (2013,NULL);
INSERT INTO myisam_example VALUES (2013,NULL);
INSERT INTO myisam_example VALUES (2013,NULL);
INSERT INTO myisam_example VALUES (2013,NULL);

SELECT * FROM myisam_example;
+------+----+
| year | id |
+------+----+
| 2012 |  1 |
| 2012 |  2 |
| 2012 |  3 |
| 2012 |  4 |
| 2012 |  5 |
| 2013 |  1 |
| 2013 |  2 |
| 2013 |  3 |
| 2013 |  4 |
| 2013 |  5 |
+------+----+

SELECT * 
  FROM myisam_example 
 WHERE CONCAT(year,'-',LPAD(id,5,0)) = '2012-00004';
+------+----+
| year | id |
+------+----+
| 2012 |  4 |
+------+----+

-- a moment's quiet contemplation for the poor index

SET @sql = CONCAT('SELECT * FROM myisam_example WHERE CONCAT(year,\'-\',LPAD(id,5,0)) = ?');

PREPARE stmnt1 FROM @sql;                
SET @a = '2012-00004';
EXECUTE stmnt1 USING @a;
+------+----+
| year | id |
+------+----+
| 2012 |  4 |
+------+----+
DEALLOCATE PREPARE stmnt1;

...and taking it one step further (coz I figured it was time I knew how to use prepared statements inside sprocs)...

DROP PROCEDURE fetchid;
DELIMITER $$
CREATE PROCEDURE fetchid(IN a VARCHAR(10)) 
BEGIN
 SET @sql = CONCAT('SELECT * FROM myisam_example WHERE CONCAT(year,\'-\',LPAD(id,5,0)) = ?');
 PREPARE stmnt1 FROM @sql;                
 SET @a = a; 
 EXECUTE stmnt1 USING @a;
 DEALLOCATE PREPARE stmnt1;
END $$

DELIMITER ;

CALL fetchid('2012-00004');
+------+----+
| year | id |
+------+----+
| 2012 |  4 |
+------+----+