mysql 取序号,MySQL：如何获取行的序号？

How can I number my results where the lowest ID is #1 and the highest ID is the #numberOfResults

Example: If I have a table with only 3 rows in it. whose ID's are 24, 87, 112 it would pull like this:

ID 24 87 112

Num 1 2 3

The reason why I want this, is my manager wants items to be numbered like item1, item2, etc. I initially made it so it used the ID but he saw them like item24, item87, item112. He didn't like that at all and wants them to be like item1, item2, item3. I personally think this is going to lead to problems because if you are deleting and adding items, then item2 will not always refer to the same thing and may cause confusion for the users. So if anyone has a better idea I would like to hear it.

Thanks.

解决方案

I agree with the comments about not using a numbering scheme like this if the numbers are going to be used for anything other than a simple ordered display of items with numbers. If the numbers are actually going to be tied to something, then this is a really bad idea!

Use a variable, and increment it in the SELECT statement:

SELECT

id,

(@row:=@row+1) AS row

FROM table,

(SELECT @row:=0) AS row_count;

Example:

CREATE TABLE `table1` (

`id` int(11) NOT NULL auto_increment,

PRIMARY KEY (`id`)

) ENGINE=InnoDB

INSERT INTO table1 VALUES (24), (87), (112);

SELECT

id,

(@row:=@row+1) AS row

FROM table1,

(SELECT @row:=0) AS row_count;

+-----+------+

| id | row |

+-----+------+

| 24 | 1 |

| 87 | 2 |

| 112 | 3 |

+-----+------+

How it works

@row is a user defined variable. It is necessary to set it to zero before the main SELECT statement runs. This can be done like this:

SELECT @row:=0;

or like this:

SET @row:=0

But it is handy to tie the two statements together. This can be done by creating a derived table, which is what happens here:

FROM table,

(SELECT @row:=0) AS row_count;

The the second SELECT actually gets run first. Once that's done, it's just a case of incrementing the value of @row for every row retrieved:

@row:=@row+1

The @row value is incremented every time a row is retrieved. It will always generate a sequential list of numbers, no matter what order the rows are accessed. So it's handy for some things, and dangerous for other things...