mysql - 这个带赋值和自增的MySQL语句是如何工作的？

Question

我使用此代码从 1(序列号)开始分配和自动递增。但实际上不知道它是如何工作的，它有什么好处。

    select cast((@cnt := @cnt + 1) as char(10)) as sno, temp1.*
    from
    (
        ..............
    )temp1, (SELECT @cnt := 0) AS dummy

Answer 1

select cast((@cnt := @cnt + 1) as char(10)) as sno, temp1.*
from
(
    ..............
)temp1, (SELECT @cnt := 0) AS dummy

等同于:

select cast((@cnt := @cnt + 1) as char(10)) as sno, temp1.*
    from
    (
        ..............
    )temp1
    CROSS JOIN (SELECT @cnt := 0) AS dummy

请注意 FROM 子句在 SELECT 之前执行

那么发生的事情是将一个虚拟列作为@cnt 的占位符添加到其他表的每一行。然后在 select 子句中，@cnt 对结果集中的每一行递增 1。

SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE Temp1
    (`id` int, `productName` varchar(7), `description` varchar(55))
;

INSERT INTO Temp1
    (`id`, `productName`, `description`)
VALUES
    (11, 'OpenIDM', 'Platform for building enterprise provisioning solutions'),
    (322, 'OpenAM', 'Full-featured access management'),
    (4763, 'OpenDJ', 'Robust LDAP server for Java')
;

查询 1:

select *
from temp1
CROSS JOIN (SELECT @cnt := 0) AS dummy

Results :

|   id | productName |                                             description | @cnt := 0 |
|------|-------------|---------------------------------------------------------|-----------|
|   11 |     OpenIDM | Platform for building enterprise provisioning solutions |         0 |
|  322 |      OpenAM |                         Full-featured access management |         0 |
| 4763 |      OpenDJ |                             Robust LDAP server for Java |         0 |

查询 2:

select cast((@cnt := @cnt + 1) as char(10)) as sno, temp1.*
from temp1
CROSS JOIN (SELECT @cnt := 0) AS dummy

Results :

| sno |   id | productName |                                             description |
|-----|------|-------------|---------------------------------------------------------|
|   1 |   11 |     OpenIDM | Platform for building enterprise provisioning solutions |
|   2 |  322 |      OpenAM |                         Full-featured access management |
|   3 | 4763 |      OpenDJ |                             Robust LDAP server for Java |