mysql 0001_如何在MySQL中將自動增量格式設置為0001 ?

How can I make MySQL auto increment in 4 digit format?

如何使MySQL自動遞增為4位格式?

So instead of '1' make '0001'?

所以不是“1”做“0001”?

6 个解决方案

#1

24

Try adding ZEROFILL attribute to the field.

嘗試向字段添加零填充屬性。

#2

17

Could you leave it as an integer and format it for humans in your SQL, for example, to pad with zeros to 4 chars wide

是否可以將其保留為整數，並將其格式化為SQL中的人員，例如，將0填充到4字符寬

select lpad(idcolumn,4,'0') from mytable;

Or use zerofill and specify the desired width when declaring the table:

或在聲明表格時使用零填充並指定所需的寬度:

create table tmpfoo (

mykey int(6) zerofill not null auto_increment,

primary key(mykey)

);

insert into tmpfoo values(1),(2);

select * from tmpfoo;

+--------+

| mykey |

+--------+

| 000001 |

| 000002 |

+--------+

#3

6

MySQL supports ZEROFILL on integer columns:

MySQL支持整數列的零填充:

mysql> create table foo (the_key int unsigned zerofill not null

auto_increment primary key);

Query OK, 0 rows affected (0.21 sec)

mysql> insert into foo SET the_key = Null;

Query OK, 1 row affected (0.00 sec)

...

mysql> insert into foo SET the_key = Null;

Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;

+------------+

| the_key |

+------------+

| 0000000001 |

| 0000000002 |

| 0000000003 |

| 0000000004 |

| 0000000005 |

| 0000000006 |

| 0000000007 |

| 0000000008 |

+------------+

8 rows in set (0.00 sec)

You may need to look into using a smallint (5 digits), or trimming/padding.

您可能需要使用一個小整數(5位數字)，或者修剪/填充。

#4

5

If you need the auto_increment column in a zero padded format, I suggest that you display it as such and not attempt to store it in the database that way.

如果需要使用0填充格式的auto_increment列，我建議您將其顯示為0，而不要嘗試以這種方式將其存儲在數據庫中。

In PHP, you could use the following code to display or otherwise use the id:

在PHP中，可以使用以下代碼顯示或使用id:

$padded_id = str_pad($id, 4, '0');

#5

2

To pad in the database set the id column to ZEROFILL

要在數據庫中填充，請將id列設置為零填充

But if its for display purposes only I recommend using LPAD SELECT RIGHT('000000' + yourNum, 6);

但如果只是為了顯示目的，我建議使用LPAD SELECT RIGHT('000000' + yourNum, 6);

#6

1

is the field an integer? if so, the answer is, "why? it's an integer!" ;-)

字段是整數嗎?如果是的話，答案是:“為什么?”它是一個整數!”:-)