没有在 mysql 中修剪的 LPAD [英] LPAD without trimming in mysql

问题描述

我试图找到一种在 ID 开头添加 0 而不修剪它的方法:

I am trying to find a way to add 0 at the beginning of an ID without trimming it :

我目前正在使用 LPAD(id,4,"0") 女巫工作正常，直到 9999

I am currently using LPAD(id,4,"0") witch works fine until 9999

• 请求:SELECT LPAD(12345,4,0");
• 异常结果:12345
• 结果:1234

我正在寻找执行 LPAD(id, MAX(LENGTH(id),4), "0")

我发现 SELECT IF(LENGTH(12345)>4, 12345, LPAD(12345, 4, 0")); 但我更喜欢 4code> 放在一个地方(以便在其他地方更容易使用).

I found SELECT IF(LENGTH(12345)>4, 12345, LPAD(12345, 4, "0")); but i would prefer if the 4 was in a single place (to make it easier use it elsewhere).

• 是否有内置函数可以满足我的需求?
• 或者是否有替代函数 MAX() 可以在这种情况下工作?
• 或者我应该坚持使用 IF(LENGTH) 解决方案和它的缺点吗?

• Is there a build-in function that does what i'm looking For ?
• Or is there an alternative to the function MAX() that would work in that situation ?
• Or should I stick with the IF(LENGTH) solution and it's drawbacks ?

ZEROFILL 不符合我的需求，因为我还需要没有 0 的 id.我主要使用不带 LPAD() 的 ID，但是当我这样做时，我使用它带有前缀:CONCAT("PFX", LPAD(id,4,"0";))

ZEROFILL doesn't fit my needs because I also need the id without 0s. I mainly use the ID without the LPAD(), but when I do, I use it with a prefix : CONCAT("PFX", LPAD(id,4,"0"))

感谢您的帮助

PS:如果我做错了什么，请告诉我，这是我第一次在这里提问.

推荐答案

我在使用 LPAD 时遇到了类似的问题，它将数字截断为填充长度.根据 https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_lpad 这是预期的结果.

Well I had similar problem with LPAD, it was truncating number to its pad length. According to https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_lpad it is expected result.

据我所知，没有人提到解决了我同样问题的答案:

As far as I can see, nobody mentioned answer that solved my same problem:

LPAD(id, GREATEST(LENGTH(id), 4), "0")

它按预期工作.用 0 填充任何小于 4 个字符的 id，并返回长度超过 4 个字符的未更改的 id.

It works as expected. Pads any id's shorter than 4 characters with 0, and returns unchanged id's that are longer than 4 characters.

我将我的答案留给其他人，他们将来会发现这个问题.

I'm leaving my answer here for other people, that will find this question in the future.

这篇关于没有在 mysql 中修剪的 LPAD的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！