How to create a table in MySQL with automatic numeration of rows?

I just want to have a table in which a first column is a monotonically increasing unique integer number. For example, if I have a table with 5 rows the indexes of the rows should be 1,2,3,4,5. And if I add a new row it gets index 6.

I am alway confused with this things because there are many related options: "auto_increment", "primary key", "unique", "index". What should I use?

From serverfault Roman

• CREATE TABLE test (
       id INT NOT NULL AUTO_INCREMENT,
       name CHAR(30) NOT NULL,
       PRIMARY KEY (id)
   );

  auto_increment: This value will automatically increase.

  primary key: This is the column which holds the first, and probably most logical, sort order. Almost always this means that it's unique.

  index: This column is one which might be chosen as a sort order. Any column or combination of columns can be indexed, not just the primary key.

  unique: This is a special index, where each value is going to appear only once.

  Roman : Just to be sure that I got it right. Since "id" is declared as a "PRIMARY KEY", the "id" will be used as a the default column for ordering or rows when "select" is used. Is it right? But what if I do not use "PRIMARY KEY (id)"? Rows will be displayed in a random order?

  ptman : primary key isn't necessarily sort order. Unless you give a specific sort order, the database is free to return the rows in any order, usually in the one they are found on the disk, which in the case of an autoincrement id column usually is the same, but doesn't have to be. And this question probably belongs to stackoverflow and not serverfault.

  Roman : So, than I do not understand what "PRIMARY KEY (id)". You said "it holds the firs, and probably most logical, sort order". But what does it means? What if I remove "PRIMARY KEY" from your example? What will it affect? P.S. Can I move my question to stackoverflow?

  gorilla : PRIMARY KEY is basically a comment. You could do the exact same thing by defining a unique index, but by putting primary key you're letting anyone who looks at the schema know your intent.
  From gorilla