产生一个有序且不重复的交易号,,,, 需要用MySQL函数生成一下有序且不重复的交易号,如下标准: 2009051100001234.......................200905110000123520090511000012362009051100001237 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 1、日期可以用CURDATE()得到,格式化一下就OK;2、后面的8位生成:你的字段是自增还是一般的VARCHAR字段,在网络上还是单机? 如果是自增:用LAST_INSERT_ID()取得刚刚插入的值,注意 ,要先插入再取得如果不是:SELECT MAX(ID) FROM FOR UPDATE将两部份CONCAT就OK mysql> CREATE TABLE tb(BH CHAR(16),content VARCHAR(20),`date` DATETIME,val INT);Query OK, 0 rows affected (0.05 sec)mysql>mysql>mysql> DELIMITER $$mysql> DROP TRIGGER IF EXISTS tri_NewBH $$Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>mysql> CREATE TRIGGER tri_NewBH BEFORE INSERT ON tb -> FOR EACH ROW -> BEGIN -> DECLARE dt CHAR(8); -> DECLARE bh_id CHAR(16); -> DECLARE number INT; -> DECLARE new_bh VARCHAR(16); -> -> SET dt = DATE_FORMAT(CURDATE(),'%Y%m%d'); -> -> SELECT -> MAX(BH) INTO bh_id -> FROM tb -> WHERE BH LIKE CONCAT(dt,'%'); -> -> IF bh_id = '' OR bh_id IS NULL THEN -> SET new_bh = CONCAT(dt,'00000001'); -> ELSE -> SET number = RIGHT(bh_id,8) + 1; -> SET new_bh = RIGHT(CONCAT('00000000',number),8); -> SET new_bh=CONCAT(dt,new_bh); -> END IF; -> -> SET NEW.BH = new_bh; -> END$$Query OK, 0 rows affected (0.09 sec)mysql>mysql> DELIMITER ;mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM tb;+------------------+---------+---------------------+------+| BH | content | date | val |+------------------+---------+---------------------+------+| 2009051100000001 | LiangCK | 2009-05-11 00:00:00 | 20 || 2009051100000002 | LiangCK | 2009-05-11 00:00:00 | 20 || 2009051100000003 | LiangCK | 2009-05-11 00:00:00 | 20 || 2009051100000004 | LiangCK | 2009-05-11 00:00:00 | 20 || 2011051200000001 | LiangCK | 2009-05-11 00:00:00 | 20 || 2011051200000002 | LiangCK | 2009-05-11 00:00:00 | 20 || 2011051200000003 | LiangCK | 2009-05-11 00:00:00 | 20 |+------------------+---------+---------------------+------+7 rows in set (0.00 sec) 本帖最后由 ACMAIN_CHM 于 2009-05-11 20:47:58 编辑 1、首先判断日期是否为当日,如是,数值自动增长1,否则从1开始;2、在网络上,用SELECT MAX FOR UPDATE,锁表,取得最大数+1,不过推荐用自增字段。 至于自增字段,,,,,,必须为主键,才可使用自增,,,,,项目开发规定: 主键统一为UUID32(),,,,,,,, 震惊!数据库个人认为不应该含逻辑业务层的东西。震惊1: 这种带日期的编号是老式传统的纸质的思想的延续,个人完全反对。个人经验中如果你真的用用户,用户反而说不出来什么为什么一定要用这种编号。震惊2: 以后一但不用MySQL,换另外 的数据库,那工作量几乎是推倒重来。 震惊2: 以后一但不用MySQL,换另外 的数据库,那工作量几乎是推倒重来。 Mysql Fetch without a select 【求助】Apache和MySQL异常高流量,导致网站无法访问 我没有找到重复记录,错误1022:MYSQL关键字重复,更改记录失败 如何建立mysql可视化平台 谁能帮我优化下 关于mysql的用户及权限问题 急!急!急! 提问:查找数据库中table中null行,并删除这行的问题 求助! 替换符合开头结尾范围内的的字符串 关于java eclipse连接数据库出现java.sql.SQLException 怎么实现表中数据的剪切??? mysql中,如何获得刚插入一条记录的ID号,系统需要刚插入记录的Id做一些处理
2、后面的8位生成:你的字段是自增还是一般的VARCHAR字段,在网络上还是单机?
如果不是:SELECT MAX(ID) FROM FOR UPDATE
将两部份CONCAT就OK
Query OK, 0 rows affected (0.05 sec)mysql>
mysql>
mysql> DELIMITER $$
mysql> DROP TRIGGER IF EXISTS tri_NewBH $$
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>
mysql> CREATE TRIGGER tri_NewBH BEFORE INSERT ON tb
-> FOR EACH ROW
-> BEGIN
-> DECLARE dt CHAR(8);
-> DECLARE bh_id CHAR(16);
-> DECLARE number INT;
-> DECLARE new_bh VARCHAR(16);
->
-> SET dt = DATE_FORMAT(CURDATE(),'%Y%m%d');
->
-> SELECT
-> MAX(BH) INTO bh_id
-> FROM tb
-> WHERE BH LIKE CONCAT(dt,'%');
->
-> IF bh_id = '' OR bh_id IS NULL THEN
-> SET new_bh = CONCAT(dt,'00000001');
-> ELSE
-> SET number = RIGHT(bh_id,8) + 1;
-> SET new_bh = RIGHT(CONCAT('00000000',number),8);
-> SET new_bh=CONCAT(dt,new_bh);
-> END IF;
->
-> SET NEW.BH = new_bh;
-> END$$
Query OK, 0 rows affected (0.09 sec)mysql>
mysql> DELIMITER ;
mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM tb;
+------------------+---------+---------------------+------+
| BH | content | date | val |
+------------------+---------+---------------------+------+
| 2009051100000001 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2009051100000002 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2009051100000003 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2009051100000004 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2011051200000001 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2011051200000002 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2011051200000003 | LiangCK | 2009-05-11 00:00:00 | 20 |
+------------------+---------+---------------------+------+
7 rows in set (0.00 sec)
2、在网络上,用SELECT MAX FOR UPDATE,锁表,取得最大数+1,
不过推荐用自增字段。
震惊!数据库个人认为不应该含逻辑业务层的东西。震惊1: 这种带日期的编号是老式传统的纸质的思想的延续,个人完全反对。个人经验中如果你真的用用户,用户反而说不出来什么为什么一定要用这种编号。
震惊2: 以后一但不用MySQL,换另外 的数据库,那工作量几乎是推倒重来。