Format the auto_increment

Question posted by: lotus18 (Site Addict) on July 1st, 2008 10:16 AM

Hello World

How to format the auto increment in mysql? Something like

Expand|Select|Wrap|Line Numbers

Rey Sean

coolsti

Needs Regular Fix

295 Posts

July 1st, 2008
01:27 PM
#2

Re: Format the auto_increment

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

Moderator

1,884 Posts

July 1st, 2008
02:05 PM
#3

Re: Format the auto_increment

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

Have a look at this:

Expand|Select|Wrap|Line Numbers

	 
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),name FROM employee;

amitpatel66

Moderator

1,884 Posts

July 1st, 2008
02:06 PM
#4

Re: Format the auto_increment

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

Expand|Select|Wrap|Line Numbers

	 
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>

lotus18

Site Addict

806 Posts

July 2nd, 2008
08:51 AM
#5

Re: Format the auto_increment

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

Rey Sean

Not the answer you were looking for? Post your question . . .
189,872 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).

Latest Articles: Read & Comment

Top MySQL Forum Contributors