MySql Autoincrement

[cancer10]

Hi,

I have 2 questions regarding the auto increment datatype in mysql.

Q1) Since we all know that if we create an auto increment column, it will start from the number '1' but is there a way we can force it to start with the number '300' and it should increment to 301 on the next new record inserted then 302 and so on?

Q2) Is there a way to make the auto increment field prefixed by 000? For example instead of making it run from the number '1' it should auto increment in this method '001' then 002 and so on?

Thanx

[iamthwee]

Why would you want to do that? It makes no sense.

[trudge]

Depending on your version of MySQL there are a couple of ways to do this. Assuming a version of at least 3.23, you can do this

CREATE TABLE MyTable
(seq INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY)
AUTO-INCREMENT=300

If your table already exists, insert a fake record with a value of 300 in the AUTO_INCREMENT field. The next INSERT will then have 301 as the value.

Q2) Is there a way to make the auto increment field prefixed by 000? For example instead of making it run from the number '1' it should auto increment in this method '001' then 002 and so on?

Why would you want to do this anyway? That field is used by MySQL to identify records. If you really want leading zeros for whatever reason, look up CONCAT or do it with your programming language.

[mwasif]

Is there a way to make the auto increment field prefixed by 000?

Yes, you can with the UNSIGNED ZEROFILL keywords. If you want to prefix the existing table column, use the following query

ALTER TABLE `tablename` CHANGE `id` `id` INT( 10 ) UNSIGNED ZEROFILL;

[cancer10]

Can I use the "UNSIGNED ZEROFILL" keyword while creating a new table?

If yes, how?

Thanx

[mwasif]

Syntax will be

CREATE TABLE tablename (
  id int(10) unsigned zerofill default NULL
) ENGINE=MyISAM;

[enginerd]

Why would you want to do that? It makes no sense.

How about instead of being a pompous know-it-all, you answer the man's question? It's obviously a newbie question, the man thinks he needs to do it, whether it needs to be done or not is ancillary to the question: "Can it be done?". If you really wanted to be helpful, AFTER answering the question at hand, you could address his specific issue and whether or not it needs to be done. You might find out he's smarter than you and is doing something really clever.

Knowledge of these things is not a priori, as any modestly intelligent person understands (not you). Furthermore learning this material can be intimidating and middle-aged-virgin-nerd-bullys (like you) aren't making life easier for people who just want to learn a new skill.

I realize that at the age we were all having intercourse for the first time you were sitting at a computer terminal, but you need to show some human understanding, I don't care what your blog-dweeb-ranking is.

The same goes for any other smarmy weasel asking "Why would you want to do that anyway?". Just answer people's questions.

On to the matter at hand:
Under, no doubt, similar circumstances, I'd like to set up an unsigned 4byte auto-incrementing primary key that starts from 1 (not zero). My questions are...
1) what is the benefit of using INT(M) vs MEDIUMINT(M), etc?
2) does it start from 1? I assumed it started from zero
3) does setting the AUTO_INCREMENT field also imply that the integer is UNSIGNED?
4) is there a MYSQL documentation out there that actually has useful information (ie not the official documentation which bites)