mysql - 排序方式忽略第四个字符

Question

我有一个表，其中包含以下值，并在路径列上有一个索引。当我选择按路径排序的值时，它使用索引，但忽略第三位之后的任何数字。见下文。

我得到了什么:

category_id name                    path
1091        Bagels and Donuts       114.1091 (1 is ignored)
1092        Barbecue                114.1092 (2 is ignored)
115         Afghan Restaurant       114.113
1140        International           114.1140 (0 is ignored)
116         African Restaurant      114.116 
117         Ethiopian Restaurant    114.116.117
118         American Restaurant     114.118
119         New American Restaurant 114.118.119
120         Asian Restaurant        114.120

我应该得到什么:

category_id name                    path
115         Afghan Restaurant       114.113
116         African Restaurant      114.116 
117         Ethiopian Restaurant    114.116.117
118         American Restaurant     114.118
119         New American Restaurant 114.118.119
120         Asian Restaurant        114.120
1091        Bagels and Donuts       114.1091
1092        Barbecue                114.1092
1140        International           114.1140

有什么想法吗？

Answer 1

假设路径中只有 3 个部分:

SELECT * FROM tab
ORDER BY
  substring_index(path, '.',  1) + 0, ## sort by 1st part
  substring_index(substring_index(path, '.', 2), '.' , -1) + 0, ## sort by 2nd part
  substring_index(path, '.', -1) + 0 ## sort by 3rd part

想象一下这不是 IP，否则有更简单的方法。