MySQL: Should my user ID be INT auto-increment primary key starting at 1?

I'm a beginning programmer, building a non-commercial web-site.

I need a user ID, and I thought it would be 开发者_如何学Gological to use for that a simple INTEGER field with an auto-increment. Does that make sense? The user ID will be not be directly used by users (they'll have to select a user-name); should I care about where they start at (presumably 1)?

Any other best practices I should incorporate in building my 'Users' table?

Thanks!

JDelage

Your design is correct. Your internal PK should be a meaningless number, not seen by users of the system and maintained automatically. It doesn't matter if it starts at 1 and it doesn't matter if it's sequential or not, or if you have "holes" in the sequence. (For cases in which you do expose the number to end users, it is sometimes important that the numbers be neither sequential nor fully-populated so that they are not guessable).

Users should identify themselves to the system with another, meaningful piece of the information (such as an email address). That piece of information should either be guaranteed unique (using a UNIQUE index) or else your front end must provide an interface for disambiguation.

Among the benefits of this design are:

1. The meaningful identifier for the account can be changed by updating one value in one record of one table, rather than requiring updates all around the database.

2. Your PK value, which will appear many, many times in the database, is a small and efficiently indexed integer while your user-facing identifier can be of any type you want including a longish text string.

3. You can use a non-unique identifier with disambiguation if the application calls for it.

auto_increment is okay. But, you shouldn't care of it's particular number.
Extremely contrary, you should never be concerned of the identifier's particular value. Take is as an abstract identifier only.

Though I doubt it can be invisible to users. Do you have another identifier to use? Auto_inqrement identifiers are usually visible to users as well. For example your ID here is 98361, nobody is hiding it. It is very handy to use such numbers, being unique and unchanged, forever bound to particular table row, it will always identify the same matter (a user, or an article, etc).

An auto incrementing field is fine unless you need to do things like share this ID across multiple databases then you will probably need to create the id value yourself. Also beware of exporting and importing data. If you are not careful all the id values will get reassigned.

In general I avoid auto incrementing fields so I have more control over how the id values are generated. Which is not to say I care what the values are just that they are unique. These are internal values the end user should never see.

Yes, that is correct. Auto-Increment starts at 1, usually. It's not usually accepted to have 0 as an ID.

If you are storing passwords, do not store them as clear text, use md5 (most popular) or some other hash.

Yes, auto incrementing is fine, Problably you will be saving passwords as well, make sure these have some kind of protection, hashing (md5) or encrypting is fine.

Also make sure you index the columns you will use to perform lookups, such as email etc... to avoid full table scans.