Format the auto_increment

[lotus18]

Hello World

How to format the auto increment in mysql? Something like

000001
000002
000003
.
.
.
00000N.

Rey Sean

[coolsti]

What do you mean by formatting it? You mean you want it stored in the database in that form, "left-padded" with zeros? Or is it good enough just to do the padding when you retrieve the values from the database using a select?

If the latter is good enough, then you can use the MySQL function LPAD, such as like this:

select LPAD(id,5,'0') as idpadded from dbtable

where here I assume the index column you want to select is named "id", and the above will pad the result with zeros to the left to a maximum field width of 5.

[amitpatel66]

Are you looking at incrementing a value by 1 and left padding it with 4 zeros?

Have a look at this:

[code=mysql]

CREATE TABLE employee (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id));

-- Insert the data in the table for the column name. Id column will be
-- automatically incremented and filled with the increment value

-- Then try this:

SELECT LPAD(id,5,0),na me FROM employee;

[/code]

[amitpatel66]

If you want the AUTO_INCREMENT value to start from other number than 1 then try this:

[code=mysql]

mysql> ALTER TABLE employee AUTO_INCREMENT = 100;

INSERT INTO employee (name) VALUES ('A'),
-> ('B'),
-> ('C'),
-> ('D'),
-> ('E'),
-> ('F');

mysql> SELECT * FROM employee;

+-----+------+
| id | name |
+-----+------+
| 100 | A |
| 101 | B |
| 102 | C |
| 103 | D |
| 104 | E |
| 105 | F |
+-----+------+
6 rows in set (0.00 sec)

mysql>

[/code]

[lotus18]

Thanks to both of you. I'll try it later : )


Rey Sean