>我有这样的表格:
name | index
item A | 1
item B | 3
item C | 3.2
item D | 3.3
item E | 30
item F | 30.1
如何使用 mysql 或 php 对索引列重新排序以删除整数序列中的所有间隙,使表如下所示:
name | index
item A | 1
item B | 2
item C | 2.2
item D | 2.3
item E | 3
item F | 3.1
我尝试使用 SUBSTRING
仅替换索引中的第一个数字,但这不允许项目 E 和 F
您可以使用用户定义的变量来跟踪最后看到的主要零件,以及要替换的当前主要零件:
SET @new:=0, @old:=0;
UPDATE myTable
SET `index` = (@new := @new + (@old < (@old := `index` DIV 1))) + `index` MOD 1
ORDER BY `index`;
从内到外解释这一点:
要使用的变量首先初始化为零。
`index` DIV 1
提供当前index
值的整数部分;该值分配给@old
以便可用于下一条记录。使用
<
小于运算符将其与当前值@old
(即来自前一条记录(进行比较;由于MySQL没有真正的布尔类型,因此此表达式的计算结果为1
如果为真,如果为假,则0
- 从而为递增@new
提供了一个方便的快捷方式。@new
将更新为其新的(即递增,如果适用(值。index
将用这个值@new
加上`index` MOD 1
的结果进行更新,这是原始index
值的小数部分。ORDER BY
子句确保以正确的顺序执行更新。
但是,考虑到您尝试执行的任务的性质,这些值似乎根本不是真正的小数,而是它们由两个由.
字符分隔的整数组成,并且恰好呈现为分数以供显示。
如果是这样,您可能明智地将值存储在两个单独的整数列中(如果需要,在应用程序的表示层组合成一个字符串(。
通过使用视图来模拟旧表结构,可以提供迁移路径,而不会对现有代码库造成太大中断:
CREATE TABLE myNewTable LIKE myTable;
ALTER TABLE myNewTable
ADD COLUMN index_major INT UNSIGNED NOT NULL,
ADD COLUMN index_minor INT UNSIGNED NOT NULL;
INSERT INTO myNewTable
SELECT myTable.*,
@new := @new + (@old < (@old := `index` DIV 1)),
CAST(SUBSTRING_INDEX(`index`, '.', -1) AS UNSIGNED)
FROM myTable, (SELECT @new:=0, @old:=0) init
ORDER BY `index`;
ALTER TABLE myNewTable
DROP COLUMN `index`;
ALTER TABLE myTable
RENAME TO archived_myTable;
CREATE VIEW myTable AS
SELECT ..., CONCAT(index_major, '.', index_minor) AS `index` FROM myNewTable;
然后,应用程序可以读取(并更新,除了合成的index
列(此myTable
视图,就好像它是原始myTable
一样,即无需了解已发生的基础更改。 但是,INSERT
语句需要修改以改用新的基础myNewTable
。