MySQL将STRING命令为INT并插入

I have a dataset like so:

我有一个像这样的数据集:

print_id
--------
2b
1
2
4b
4a
3
6
2a
5

The print ID can be in the format of: /([0-9]+)([a-e]{1})?/

打印ID的格式为:/([0-9] +)([a-e] {1})?/

What I want to is order them first by number, and by letter if there is any. If there is no letter, then it's the first in the order for that number. So the result should be like so:

我想要的是先按编号和信件订购,如果有的话。如果没有字母,那么它就是该号码的第一个字母。所以结果应该是这样的:

print_id
--------
1
2
2a
2b
3
4a
4b
5
6

I tried ORDER BY (print_id + 0) it sorts the numbers correctly but it just doesn't quite do the trick. Any suggestions?

我尝试了ORDER BY(print_id + 0)它正确地对数字进行排序,但它并没有完全解决问题。有什么建议么?

2 个解决方案

#1

You can have multiple expressions in the ORDER BY clause:

您可以在ORDER BY子句中包含多个表达式:

ORDER BY print_id + 0, print_id

This will first try to order them numerically (discarding the letter suffix), and if they are numerically equivalent, it will order them by their string values (including the letter suffix). For the rule that you described, the only time this will break is if you have leading zeroes; for example, this ORDER BY clause will sort '01b' above '1a'. Can that happen in your data?

这将首先尝试以数字方式对它们进行排序(丢弃字母后缀),如果它们在数字上等效,它将按字符串值(包括字母后缀)对它们进行排序。对于你所描述的规则,唯一一次会破坏的是你有前导零;例如,这个ORDER BY子句将'01'排在'1a'之上。这可能发生在您的数据中吗?

#2

ORDER BY print_id

should do what you want. It will order by the character field print_id, which is equivalent to ordering by individual characters.

应该做你想做的事。它将按字符字段print_id排序,这相当于按个别字符排序。

So, for a table

所以,对于一张桌子

print_id
--------
1
3a
3c
2b
2c
2a
3b
0
0b
00

You'll get

print_id
--------
0
00
0b
1
2a
2b
2c
3a
3b
3c

#1