mysql之排序问题

发表于 | 分类于 ， | 评论数： | 阅读次数：

背景

模型：

1
2
3
4
5
6
7
8
9
class Solutions(db.Model):
    __tablename__ = 'solutions'
    id = db.Column(db.Integer, primary_key=True)
    label = db.Column(db.String(1000))
    weight = db.Column(db.Integer, default=0)
    timestamp = db.Column(db.DateTime(), default=datetime.now)
    title = db.Column(db.String(1000))
    solutionId = db.Column(db.String(1000))
    description = db.Column(db.Text)

按照weight排序输出：

1
out = Solutions.query.order_by(Solutions.weight.desc()).limit(10)

结果排序

1
2
3
4
5
6
7
8
9
10
11
12
13
>>> for item in out:
...     print(item.weight)
...
97
92
9
86
79
78
72
7
66
66

根据结果看，排序异常了。

排查

观察排序输出，可以发现其实是按照字符串顺序排列的。

检查一下数据库中的表结构：

mysql

果然，weight这列的类型初始化成了varchar2(1000)。

解决方法

可以采用两种方法解决：

  • 在排序的是对varchar类型进行转换，利用to_number()函数。
  • 修改表结构，将weight列的类型修改为int。

这里采用第二种方法，比较彻底。由于原表是有数据的，所以不能直接修改表格的类型，否则会产生死锁。

  • 创建一个新列

    1
    2
    ALTER TABLE `solutions`
    ADD COLUMN `weight_int`  int NOT NULL AFTER `label`;

  • 将原列的字符串转换为数值，填充到新列中

    1
    update solutions set weight_int = cast(weight as UNSIGNED INTEGER)

  • 删除原列

    1
    2
    ALTER TABLE `solutions`
    DROP COLUMN `weight`;

  • 将新列的名词修改为旧列的名词

    1
    2
    ALTER TABLE `solutions`
    CHANGE COLUMN `weight_int` `weight`  int(11) NULL DEFAULT NULL AFTER `label`;

扩展

根据网上经验，在不修改表结构（即保持表数据为varchar类型）的情况下，可以采用如下方法实现我们想要的排序：

1
out1 = Solutions.query.order_by('-weight').limit(10)

验证结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
>>> for item in out1:
...     print(item.weight)
...
229
166
134
118
106
97
92
86
79
78

这种方法虽然是可行的，但是在sqlalchemy的官网上并没有找到对应的说明，建议还是按常规办法来，修改列类型。