怎样在MySql中处理这类格式的字符？

在一张表里有近百万条数据，id = 0_1013377_161_1;现要将id字段的末尾1改成001，末尾本来是int型转来的，都是数字的，如果是_2则要得到002,如果是_11则换成_011,如果是_111,则就是_111。总之就是换算成三位表示的，如何做呢？

帮顶。。。

select
concat(
left(id,length(id)-SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2)),
 case when LENGTH(SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2))=3 then SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2) 
when LENGTH(SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2))=2 then concat('0',SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2))
when LENGTH(SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2))=1 then concat('00',SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2)) end) from test21;

SQL code

update table1 set id=LPAD(SUBSTRING_INDEX(id,'_',-1),3,'0');

这个语句的问题是：一旦你的ID的尾数为12345，只会取前3个数字123。

select 
  case 
  when length(substring_index(id,'_',-1))=1  
  then 
  concat(substring_index(id,'_',3),'_00',substring_index(id,'_',-1))
  when length(substring_index(id,'_',-1))=2
  then 
  concat(substring_index(id,'_',3),'_0',substring_index(id,'_',-1))
  else 
  id  
  end
  as id;

我一直找不到倒着查找的函数，原来是 substring_index

SQL code

set @a := "0_1013377_161_1";
set @b := "0_1013377_161_12";
set @c := "0_1013377_161_123";

select  @a as 原数据, concat(SUBSTRING_INDEX(@a, '_', 3), '_', LPAD(SUBSTRING_INDEX(@a, '_',-1),3,'0' )) as 处理后数据
union
select  @b, concat(SUBSTRING_INDEX(@b, '_', 3), '_', LPAD(SUBSTRING_INDEX(@b, '_',-1),3,'0' ))
union
select  @c, concat(SUBSTRING_INDEX(@c, '_', 3), '_', LPAD(SUBSTRING_INDEX(@c, '_',-1),3,'0' ));

+-------------------+-------------------+
| 原数据                  | 处理后数据        |
+-------------------+-------------------+
| 0_1013377_161_1   | 0_1013377_161_001 |
| 0_1013377_161_12  | 0_1013377_161_012 |
| 0_1013377_161_123 | 0_1013377_161_123 |
+-------------------+-------------------+

实际操作，如下即可：
SQL code

update 表 set id = concat(SUBSTRING_INDEX(id, '_', 3), '_', LPAD(SUBSTRING_INDEX(id, '_',-1),3,'0' ));