mysql - 使用 JSON 数据通过数学对 SQL 行进行排序

Question

假设我有一个像这样的MySQL数据库(赞成票和反对票是用户id的JSON数组)，id是一个自动递增的整数，内容是文本)

id  upvotes             downvotes    content
--- ------------------- ------------ --------------
1   [1, 5, 15]          [1]          some text
10  [1, 100, 25]        [3, 9]       other stuff
17  [5, 10, 1110]       [5, 6, 7, 8] also includes
36  [5, 6, 7, 8, 9]     []           metadata stuff
37  [5, 6, 8, 10, 1, 7] [4]          equal count to 36

我想按赞成票数减去反对票数对返回的行进行排序，然后在相等时按 id 排序，因此 id 的顺序为 36, 37, 1, 10, 17。这在 PHP 中可行吗？如何实现？

(这个问题是 https://stackoverflow.com/questions/29527800/sort-sql-rows-with-math-using-column-data-in-php 的重新发布，因为它的工作原理是对数组进行计数，而不是简单地从行中获取数字。)

Answer 1

MySQL 没有原生 JSON 支持，尽管它似乎即将推出。因此，您必须将这些值视为字符串。基本上，您想要计算每个字符串中的元素数量并按差异进行排序。

您可以通过计算逗号来做到这一点:

order by (length(upvotes) - length(replace(upvotes, ',', '')) ) -
         (length(downvotes) - length(replace(downvotes, ',', '')) )

注意:如果您要维护每个可能投票的字符串，也许您还应该在记录中包含一个计数器。