mysql - 在选择的每个结果上使用用户定义的变量执行 SQL UPDATE

Question

这是一个将运行一次的迁移脚本，因此性能不是问题。

我有一张表 sb_nav_page 和一张表 sb_module。一个模块属于一个页面(外键id_page_module)；

CREATE TABLE IF NOT EXISTS `sb_nav_page` (
   `id_page` int(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_page`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `sb_module` (
  `id_module` int(11) NOT NULL AUTO_INCREMENT,
  `id_page_module` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_module`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

我需要在 sb_module 上添加并填充一个新列 zindex，它将代表页面中模块的初始顺序，该顺序基于之前的排序规则。

mysql> SELECT id_module, name_module, zindex_module from sb_module  
    WHERE id_page_module = 148 ;
+-----------+-------------+---------------+
| id_module | name_module | zindex_module |
+-----------+-------------+---------------+
|       560 | A           |            0  |
|       561 | A           |            0  |
|       562 | B           |            0  |
+-----------+-------------+---------------+
3 rows in set (0.00 sec)

以下 SQL UPDATE 对一页 (id_page = 148) 进行了正确的初始化。

mysql> SET @x = 0;
mysql> UPDATE sb_module SET zindex_module = (@x:=@x+1) WHERE 
    id_page_module = 148     
    ORDER BY name_module DESC, id_module ASC;

mysql> SELECT id_module, name_module, zindex_module from sb_module  
    WHERE id_page_module = 148 ;
+-----------+-------------+---------------+
| id_module | name_module | zindex_module |
+-----------+-------------+---------------+
|       560 | A           |            2  |
|       561 | A           |            3  |
|       562 | B           |            1  |
+-----------+-------------+---------------+
3 rows in set (0.00 sec)

但我可以弄清楚如何为所有页面执行此操作，即为所有 SELECT id_page from sb_nav_page。

Answer 1

您可以使用变量来做到这一点:

SET @x = 0, @p = -1;
UPDATE sb_module
    SET zindex_module = (@x := if(@p = id_page_module, @x + 1,
                                  if(@p := id_page_module, 1, 1)
                                 )
                        )
ORDER BY id_page_module, name_module DESC, id_module ASC;

• 第一个 if 检查我们是否与上一个模块在同一页面上。
• 第二个if设置新的id_page_module并重置@x。

sb_module(id_page_module, name_module, id_module) 上的索引有助于提高性能。