mysql - Mysql更新帐号为0

我遇到的一种情况是，如果我的帐号的长度小于11，则需要更新该帐号，因此如果不满足要求，则需要用0填充它。因此，如果数字为123456，则需要在数据库中将其更新为00000123456

我为此创建了一个存储过程，并且可以正常工作，

我需要知道还有更好的方法吗？

这是我的SP

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Update_AccountNumber`()
BEGIN
Drop TABLE IF EXISTS temp_table;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_table (id INT(11),accountnumber varchar(50),
                                                            length int,PRIMARY KEY (`id`));

Insert into temp_table(id,accountnumber, length)
select a.id,a.account_number,length(a.account_number) from accounts a
inner join programs p on a.program_id = p.id
where p.abbreviation = 'UA'
and length(a.account_number) < 11;

set @count = (Select count(*) from temp_table);
While @count > 0 Do
    set @id = (Select id from temp_table limit 0,1);
    set @accountnumber = (Select accountnumber from temp_table limit 0,1);
    set @length = (Select length from temp_table limit 0,1);
    set @newlength = 11 - @length;
    Delete from temp_table where id = @id;

    While @newlength > 0 Do
        set @accountnumber = concat("0" , @accountnumber);
        set @newlength = @newlength - 1;
    End While;
    Update accounts set account_number = @accountnumber where id = @id;
    set @count = (Select count(*) from temp_table);
End While;
END

谢谢

最佳答案

尝试这个

UPDATE accounts
SET account_number = CONCAT(REPEAT('0', 11-LENGTH(account_number)), CONVERT(account_number, CHAR(100)))
WHERE LENGTH(a.account_number) < 11;

关于mysql - Mysql更新帐号为0，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9409445/