MySQL 排序列设置为 varchar(255) [英] MySQL sorting column set as varchar(255)
问题描述
我有问题将字段 sID_contents_p 设置为 VARCHAR (255) 形成 table ttt 存储一个数据库 MySql 版本 8.0.17
I've problem to order the field sID_contents_p set to VARCHAR (255) form the table ttt stored an a database MySql version 8.0.17
这是 db-fiddle.com 上的结果，提供 MySQL 8
我需要这个返回，例如当 sID_contents_p 包含 1
I need this return, for example when sID_contents_p contains 1
+----------------+-----+
| sID_contents_p | sID |
+----------------+-----+
| 1.1 | 1 |
| 1.2 | 2 |
| 1.3 | 3 |
| 1.4 | 4 |
| 1.5 | 5 |
| 1.6 | 6 |
| 1.7 | 7 |
| 1.8 | 8 |
| 1.9 | 9 |
| 1.10 | 10 |
| 1.11 | 11 |
| 1.12 | 12 |
| 1.13 | 13 |
| 1.14 | 14 |
| 1.15 | 15 |
| 1.16 | 16 |
| 1.17 | 17 |
| 1.18 | 18 |
| 1.19 | 19 |
| 1.20 | 89 |
+----------------+-----+
我已经尝试过这个查询，使用这个建议，-请这个问题不是重复的，因为该建议在我的情况下不起作用-，但返回的不是您想要的(见上文)....
I've tried this query, using this suggestion, -please this question is not a duplicate because the suggestion doesn't work in my case-, but the return not what you want (see above)....
mysql> SELECT
sID_contents_p,
sID
FROM
`ttt`
ORDER BY
LENGTH( sID_contents_p ),
sID_contents_p;
+----------------+-----+
| sID_contents_p | sID |
+----------------+-----+
| 1.1 | 1 |
| 1.2 | 2 |
| 1.3 | 3 |
| 1.4 | 4 |
| 1.5 | 5 |
| 1.6 | 6 |
| 1.7 | 7 |
| 1.8 | 8 |
| 1.9 | 9 |
| 2.1 | 20 |
| 2.2 | 21 |
| 2.3 | 22 |
| 2.4 | 23 |
| 2.5 | 24 |
| 2.6 | 25 |
| 2.7 | 26 |
| 2.8 | 27 |
| 2.9 | 28 |
| 3.1 | 31 |
| 3.2 | 32 |
| 3.3 | 33 |
| 3.4 | 34 |
| 3.5 | 35 |
| 3.6 | 36 |
| 3.7 | 37 |
| 3.8 | 38 |
| 3.9 | 39 |
| 4.1 | 40 |
| 4.2 | 41 |
| 5.1 | 42 |
| 5.2 | 43 |
| 5.3 | 44 |
| 5.4 | 45 |
| 5.5 | 46 |
| 5.6 | 47 |
| 5.7 | 48 |
| 5.8 | 49 |
| 5.9 | 50 |
| 6.1 | 55 |
| 6.2 | 56 |
| 6.3 | 57 |
| 6.4 | 58 |
| 6.5 | 59 |
| 6.6 | 60 |
| 6.7 | 61 |
| 6.8 | 62 |
| 6.9 | 63 |
| 7.1 | 66 |
| 7.2 | 67 |
| 7.3 | 68 |
| 7.4 | 69 |
| 7.5 | 70 |
| 7.6 | 71 |
| 7.7 | 72 |
| 7.8 | 73 |
| 7.9 | 74 |
| 8.1 | 84 |
| 8.2 | 85 |
| 8.3 | 86 |
| 8.4 | 87 |
| 8.5 | 88 |
| 1.10 | 10 |
| 1.11 | 11 |
| 1.12 | 12 |
| 1.13 | 13 |
| 1.14 | 14 |
| 1.15 | 15 |
| 1.16 | 16 |
| 1.17 | 17 |
| 1.18 | 18 |
| 1.19 | 19 |
| 1.20 | 89 |
| 2.10 | 29 |
| 2.11 | 30 |
| 2.12 | 90 |
| 5.10 | 51 |
| 5.11 | 52 |
| 5.12 | 53 |
| 5.13 | 54 |
| 6.10 | 64 |
| 6.11 | 65 |
| 7.10 | 75 |
| 7.11 | 76 |
| 7.12 | 77 |
| 7.13 | 78 |
| 7.14 | 79 |
| 7.15 | 80 |
| 7.16 | 81 |
| 7.17 | 82 |
| 7.18 | 83 |
+----------------+-----+
90 rows in set (0.07 sec)
解决方案更新
推荐答案
我会在这里使用 SUBSTRING_INDEX 对 SID 内容的主要/次要部分进行整数转换:
I would use SUBSTRING_INDEX here with integer casts on the major/minor portions of the SID contents:
SELECT sID_contents_p, sID
FROM ttt
ORDER BY
CAST(SUBSTRING_INDEX(sID_contents_p, '.', 1) AS UNSIGNED), -- major portion
CAST(SUBSTRING_INDEX(sID_contents_p, '.', -1) AS UNSIGNED); -- minor portion
这篇关于MySQL 排序列设置为 varchar(255)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
