Generating automatic numbers in MySQL

Question

I write a query and give a table in output but I want to every row of my table has a number automatically that starts from 1. for example , I want that my table has "number" column that first row of my table have 1 in that column , second row of my table have 2 in that column , third row of my table have 3 in that column , ... How can I do this ? thanks

** My DBMS is MySQL **

Exact duplicate of [ROW_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql). Other RDBMS can do this with ROW_NUMBER, you have to emulate it in MySQL. See OMG Ponies' answer, not the accepted one — gbn, Feb 03 '12 at 08:25

score 4 · Accepted Answer · answered Feb 03 '12 at 08:34

Use a variable and increment it like following.

set @num:=0; 
select *,  @num:=@num+1 `Row` from names;

Example

mysql> create table names( name varchar(10) primary key )engine=Innodb charset=utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into names values('a'), ('b'), ('cat'), ('dog'), ('parrot'), ('bird');
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> set @num:=0; select *,  @num:=@num+1 `Row` from names;
Query OK, 0 rows affected (0.00 sec)

+--------+------+
| name   | Row  |
+--------+------+
| a      |    1 |
| b      |    2 |
| bird   |    3 |
| cat    |    4 |
| dog    |    5 |
| parrot |    6 |
+--------+------+
6 rows in set (0.00 sec)

Note: If you use * make sure it precedes.

Mike · Answer 2 · 2012-02-03T08:38:40.337

1

AUTO_INCREMENT is what you are looking for

Like this :

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

Taken from here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

EDIT: Based on the comment someone might want to add that manually. You select the maximum value from your filed, increment by 1 and then insert it into your table.

SELECT MAX(id) FROM animals

edited Feb 03 '12 at 08:38

answered Feb 03 '12 at 08:19

Mike

3,017
1
34
47

How can I use this attribute in a query, because my query is formed form 3 or more tables. – amir amir Feb 03 '12 at 08:21
You use this when you create your table. Is no need to add it everytime in the query. It simply works by itself. – Mike Feb 03 '12 at 08:30

score 0 · Answer 3 · answered Feb 03 '12 at 08:19

0

Make your primary key an auto increment field. More info here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

answered Feb 03 '12 at 08:19

Francis Lewis

8,872
9
55
65

Generating automatic numbers in MySQL

3 Answers3