如何对以字节KB MB MB GB的数据排序的mysql coloumn? [英] How to sort mysql coloumn that has data in Bytes KB MB GB?

问题描述

我有一个表，其中大小"列的文件大小以diff单位为单位，例如"10 Kb"，"50 MB"，"1 GB". 问题是当我尝试排序时，它首先返回1 GB的行.

I have a table with column 'size' that has file sizes in diff units like '10 Kb', '50 MB', '1 GB'. The problem is when I try to sort, it returns 1 GB row at first.

如何在不更改旧数据的情况下解决这个问题?

How to solve this prob without changing old data ???

非常感谢您的帮助...谢谢.

Any help is much appreciated... Thanks.

推荐答案

您实际上应该将所有数据转换为一个通用单位(例如字节)，或者添加一个包含度量单位"的列，并保留size列本身数字.

You should really either convert all data into a common unit (say bytes) or add a column which contains a "unit of measure" and keep the size column itself numeric.

话虽如此，以下内容应适用于以GB/MB/KB或B结尾的数据.

Having said that, the following should work on data in that ends with either GB/MB/KB or B.

select size
  from t
 order 
    by case when size like '%GB' then power(1024, 3) * substr(size, 1, length(size) - 2)
            when size like '%MB' then power(1024, 2) * substr(size, 1, length(size) - 2)
            when size like '%KB' then power(1024, 1) * substr(size, 1, length(size) - 2)
            when size like '%B'  then                  substr(size, 1, length(size) - 1)
        end desc;


+-------+------------+
| size  | bytes      |
+-------+------------+
| 1GB   | 1073741824 |
| 10MB  | 10485760   |
| 100KB | 102400     |
| 1000B | 1000       |
+-------+------------+

这篇关于如何对以字节KB MB MB GB的数据排序的mysql coloumn?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！