Database Reference
In-Depth Information
103K
for 103 kilobytes. You can convert a byte count to that kind of value using this
expression:
CONCAT(FLOOR((size+1023)/1024),'K')
However, such values are strings, so they sort lexically, not numerically. If you use them
for sorting, a value such as
96K
sorts after
2339K
, even though it represents a smaller
number:
mysql>
SELECT t, srcuser,
->
CONCAT(FLOOR((size+1023)/1024),'K') AS size_in_K
->
FROM mail WHERE size > 50000
->
ORDER BY size_in_K;
+---------------------+---------+-----------+
| t | srcuser | size_in_K |
+---------------------+---------+-----------+
| 2014-05-12 12:48:13 | tricia | 191K |
| 2014-05-14 17:03:01 | tricia | 2339K |
| 2014-05-11 10:15:08 | barb | 57K |
| 2014-05-14 14:42:21 | barb | 96K |
| 2014-05-15 10:25:52 | gene | 976K |
+---------------------+---------+-----------+
To achieve the desired output order, display the string, but use actual numeric size for
sorting:
mysql>
SELECT t, srcuser,
->
CONCAT(FLOOR((size+1023)/1024),'K') AS size_in_K
->
FROM mail WHERE size > 50000
->
ORDER BY size;
+---------------------+---------+-----------+
| t | srcuser | size_in_K |
+---------------------+---------+-----------+
| 2014-05-11 10:15:08 | barb | 57K |
| 2014-05-14 14:42:21 | barb | 96K |
| 2014-05-12 12:48:13 | tricia | 191K |
| 2014-05-15 10:25:52 | gene | 976K |
| 2014-05-14 17:03:01 | tricia | 2339K |
+---------------------+---------+-----------+
Displaying values as strings but sorting them as numbers helps solve some otherwise
difficult problems. Members of sports teams typically are assigned a jersey number,
which normally you might think should be stored using a numeric column. Not so fast!
Some players like to have a jersey number of zero (
0
), and some like double-zero (
00
).
If a team happens to have players with both numbers, you cannot represent them using
a numeric column because both values will be treated as the same number. To solve this
problem, store jersey numbers as strings:
CREATE
TABLE
roster
(
name
CHAR
(
30
),
#
player
name
