如何在MySQL中将一列中的0移动到末尾并保持升序搜索顺序？

在开发数据库应用程序时，当需要对某一列进行排序操作时，我们经常会遇到需要将其中的0值移动到该列的末尾并保持升序搜索顺序的需求。这种需求在MySQL中非常普遍，今天我们就来学习如何实现这个需求。

阅读更多：MySQL 教程

基本原理

我们可以将该列中非0值抽取出来，进行升序排列，然后再将0值添加在该列非0值排序结果的末尾即可。示例如下：

SELECT column FROM table WHERE column <> 0 ORDER BY column ASC UNION SELECT column FROM table WHERE column = 0;

在上述示例代码中，SELECT column FROM table WHERE column <> 0 ORDER BY column ASC 将列中非0值抽取出来并升序排列，UNION SELECT column FROM table WHERE column = 0 则将0值添加到非0值升序排序结果的末尾。最终查询结果就是0值移动到末尾并保持升序搜索顺序的结果。

实现步骤

步骤1：在MySQL中创建一张表并添加几条测试数据

创建名为test的表，并添加10条测试数据，其中有5个0值。

CREATE TABLE test (id INT(11) NOT NULL AUTO_INCREMENT, column1 INT(11), PRIMARY KEY (id));
INSERT INTO test (column1) VALUES (1), (0), (3), (2), (0), (5), (0), (6), (0), (4);

步骤2：实现按照上述基本原理进行查询，并验证查询结果是否符合要求

使用示例代码进行查询，并验证查询结果是否符合要求。

SELECT column1 FROM test WHERE column1 <> 0 ORDER BY column1 ASC UNION SELECT column1 FROM test WHERE column1 = 0;

可以看到，该查询结果符合我们的要求，将原表中的0值移动到了末尾并保持升序搜索顺序。

步骤3：将查询结果更新回原表

将查询结果更新回原表，即可实现原表中列的0值被移动到末尾并保持升序搜索顺序的效果。具体示例代码如下：

UPDATE test SET column1 = sorted.column1, id = sorted.id FROM (SELECT @n:=@n+1 AS id, column1 FROM (SELECT column1 FROM test WHERE column1 <> 0 ORDER BY column1 ASC UNION SELECT column1 FROM test WHERE column1 = 0) AS sub1, (SELECT @n:=0) AS sub2) AS sorted WHERE test.id = sorted.id;

运行该示例代码后，再次查询表test，可以看到表中的0值被移动到了末尾并保持升序搜索顺序。