如何创建AUTO增量ID号 [英] how to create AUTO increment ID number

问题描述

如何创建由年份和数字组成的AUTO增量ID号，例如:"2012-0001"，如果我输入其他数据，则将自动将其增量为"2012-0002".

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.

谢谢！

推荐答案

使用MyISAM，您可以执行此操作...

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;

...再往前走一步(因为我想是时候我知道如何在proc中使用预备语句了)...

...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 |
+------+----+

这篇关于如何创建AUTO增量ID号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！