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>