表结构如下(ID子增)
id name age
-----------------------------------
1 kevin 26
2 jack 26
3 ralf 24
...
我想用一个存储过程+游标的方法把所有的ID的格式变为531+ID,比如ID=1 就变为531001,ID=10 就变为531010,ID=100 就变为531100,请问这个存储过程应当怎样写呢?谢谢各位!~~
id name age
-----------------------------------
1 kevin 26
2 jack 26
3 ralf 24
...
我想用一个存储过程+游标的方法把所有的ID的格式变为531+ID,比如ID=1 就变为531001,ID=10 就变为531010,ID=100 就变为531100,请问这个存储过程应当怎样写呢?谢谢各位!~~
create table test(
id int auto_increment primary key not null,
name Nvarchar(200)
);insert into test(name)values('a');
insert into test(name)values('b');
insert into test(name)values('c');select * from testupdate test set id=53100+idselect * from test/**
id name
53101 a
53102 b
53103 c
**/
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | kevin | 26 |
| 2 | jack | 26 |
| 3 | ralf | 24 |
+----+-------+------+
3 rows in set (0.00 sec)mysql> update t_yyhakusu set age=age+531000;
Query OK, 3 rows affected (0.09 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from t_yyhakusu;
+----+-------+--------+
| id | name | age |
+----+-------+--------+
| 1 | kevin | 531026 |
| 2 | jack | 531026 |
| 3 | ralf | 531024 |
+----+-------+--------+
3 rows in set (0.00 sec)mysql>
update就可以了如果是字符串链接用concat("5100",id)
+----+-------+--------+
| id | name | age |
+----+-------+--------+
| 1 | kevin | 531026 |
| 2 | jack | 531026 |
| 3 | ralf | 531024 |
+----+-------+--------+
3 rows in set (0.02 sec)mysql> update t_yyhakusu set id=531000+id;
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from t_yyhakusu;
+--------+-------+--------+
| id | name | age |
+--------+-------+--------+
| 531001 | kevin | 531026 |
| 531002 | jack | 531026 |
| 531003 | ralf | 531024 |
+--------+-------+--------+
3 rows in set (0.00 sec)mysql>
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
id name age
-----------------------------------
1 kevin 26
...
12 jack 26
...
101 ralf 24
我想要的结果:
id name age
-----------------------------------
531001 kevin 26
...
531012 jack 26
...
531101 ralf 24
这样够清楚了吧
直接加即可
update tb_name set id=531000+id;或update tb_name set id=concat('531',repeat('0',3-char_length(id)),id);