在上面帖子里面1的基础上编写存储过程
附上建表语句(原帖有):
DROP TABLE IF EXISTS `tickets64`;
CREATE TABLE `tickets64` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`stub` char(1) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='from http://my.oschina.net/u/142836/blog/174465';
insert into tickets64(stub) values('a');
存储过程:
CREATE DEFINER = `root`@`localhost` PROCEDURE `getorderid`(out orderid varchar(20))
begin
declare currentDate VARCHAR(8);
declare currentid BIGINT;
SELECT DATE_FORMAT(NOW(), '%Y%m%d') INTO currentDate ;
REPLACE INTO Tickets64 (stub) VALUES ('a');
select id into currentid from tickets64
where stub='a';
select CONCAT(currentDate,LPAD(currentid,12,'0')) into orderid;
select orderid;
end;
使用方法:
mysql> call getorderid(@myOrderid);
+----------------------+
| orderid |
+----------------------+
| 20151205000000000030 |
+----------------------+
1 row in set
Query OK, 0 rows affected
mysql>
记录,备查