56,687
社区成员
发帖
与我相关
我的任务
分享
mysql> CREATE TABLE `order` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> cdate date,
-> `oid` int(11) NOT NULL,
-> PRIMARY KEY (cdate,`id`)
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> insert into `order`(cdate,oid) values
-> (curdate(),123),
-> (curdate(),124),
-> (curdate(),125),
-> (curdate()+interval 1 day,126),
-> (curdate()+interval 1 day,127),
-> (curdate()+interval 1 day,128);
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from `order`;
+----+------------+-----+
| id | cdate | oid |
+----+------------+-----+
| 1 | 2012-03-30 | 123 |
| 2 | 2012-03-30 | 124 |
| 3 | 2012-03-30 | 125 |
| 1 | 2012-03-31 | 126 |
| 2 | 2012-03-31 | 127 |
| 3 | 2012-03-31 | 128 |
+----+------------+-----+
6 rows in set (0.00 sec)
mysql> select concat(DATE_FORMAT(cdate,'%Y%m%d'),right(1000+id,3)) as sn,oid from `order`;
+-------------+-----+
| sn | oid |
+-------------+-----+
| 20120330001 | 123 |
| 20120330002 | 124 |
| 20120330003 | 125 |
| 20120331001 | 126 |
| 20120331002 | 127 |
| 20120331003 | 128 |
+-------------+-----+
6 rows in set (0.00 sec)
mysql>