mysql数据库的student表结构如下:
id | name | age |
1 | zhangsan | 16 |
2 | alice | 20 |
3 | lanlan | 30 |
4 | haha | 25 |
如果新增一个字段no,规则是:六位数,从000001开始依次递增。
id | name | age | no |
1 | zhangsan | 16 | 000001 |
2 | alice | 20 | 000002 |
3 | lanlan | 30 | 000003 |
4 | haha | 25 | 000004 |
分两步
步骤一:首先将id字段赋值给no字段。
update student set no = id;
步骤二:将id左补位0赋值给字段pad和id字段组成表b,与原表做内连接,两个表id相等作为判断条件,将表b的pad字段赋值给a表的no字段。
update student a
inner JOIN (select id, LPAD(id,6,'0') as pad from student) as b on a.id = b.id
set a.no= b.pad
步骤二:将id左补位0赋值给字段pad和id字段组成表b,与原表做内连接,两个表id相等作为判断条件,将表b的pad字段赋值给a表的no字段。
update student a
inner JOIN (select id, LPAD(id,6,'0') as pad from student) as b on a.id = b.id
set a.no= b.pad
参考文章地址:https://www.cnblogs.com/azhqiang/p/6972947.html
右补0:select RPAD(id,8,'0') as pad from tmp;
左补0:select LPAD(id,8,'0') as pad from tmp;
左补0:select LPAD(id,8,'0') as pad from tmp;