我想让一个字符串的值按序列增加,增加如下:
OP-SU-00000001
OP-SU-00000002
OP-SU-00000003
OP-SU-00000004
OP-SU-00000005
OP-SU-00000006
OP-SU-00000007
OP-SU-00000001
OP-SU-00000002
OP-SU-00000003
OP-SU-00000004
OP-SU-00000005
OP-SU-00000006
OP-SU-00000007
select concat('OP-SU-',right(concat('0000000',id),8))
from tb
用concat('OP-SU-',right(concat('0000000',id),8))解决
1个自增字段,1个字段(lid)保存你的字符串,在TRIGGER中
set new.lid=concat('OP-SU-',right(concat('0000000',new.id),8))
mysql> create table test(id int(8) zerofill primary key auto_increment, col2 int);
Query OK, 0 rows affected (0.08 sec)mysql> insert into test (col2) values(100);
Query OK, 1 row affected (0.00 sec)mysql> select concat('OP-SU-', id), col2 from test;
+----------------------+------+
| concat('OP-SU-', id) | col2 |
+----------------------+------+
| OP-SU-00000001 | 100 |
+----------------------+------+
1 row in set (0.01 sec)mysql> insert into test (col2) values(199);
Query OK, 1 row affected (0.00 sec)mysql> select concat('OP-SU-', id), col2 from test;
+----------------------+------+
| concat('OP-SU-', id) | col2 |
+----------------------+------+
| OP-SU-00000001 | 100 |
| OP-SU-00000002 | 199 |
+----------------------+------+
2 rows in set (0.00 sec)mysql>