blocks|key|873542|text|如果字符串始终以单个字母/数字前缀开头，则可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|873543|+SELECT+no+from+my_table+order+by+substring(no,1,1),+substring(no,2)%2B0+|code-block|syntax|javascript|873544|fiddle+here|offset|length|873545|在PHP中，一旦进入多维数组，就可以通过使用usort对字段返回strnatcasecmp调用的结果来进行排序。|873546|usort($records,+function($a,+$b){return+strnatcasecmp($a["fieldname"],+$b["fieldname"]);});|873547|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/74959/11|1|http://www.php.net/manual/en/function.usort.php|2|http://www.php.net/manual/en/function.strnatcasecmp.php^0|0|0|0|B|0|0|M|5|1|W|D|2|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@$I|13|J|14|1|15]]|A|$]]|$1|K|3|L|5|6|7|16|8|@]|9|@$I|17|J|18|1|19]|$I|1A|J|1B|1|1C]]|A|$]]|$1|M|3|N|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]|W|$5|R|S|T|A|$U|X]]|Y|$5|R|S|T|A|$U|Z]]]]

If the string always starts with a single alpha/numeric prefix then you can use

<pre><code> SELECT no from my_table order by substring(no,1,1), substring(no,2)+0 
</code></pre>

<a href="http://sqlfiddle.com/#!2/74959/11" rel="nofollow">fiddle here</a>

In PHP you can sort once it's in a multidimensional array by using <a href="http://www.php.net/manual/en/function.usort.php" rel="nofollow">usort</a> returning the results of a <a href="http://www.php.net/manual/en/function.strnatcasecmp.php" rel="nofollow">strnatcasecmp</a> call on the fields.

<pre><code>usort($records, function($a, $b){return strnatcasecmp($a["fieldname"], $b["fieldname"]);});
</code></pre>

blocks|key|4340873|text|这就是所谓的natural+sort,+or+a+natural+order。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4340874|MySQL没有进行自然排序的内置能力。|4340875|对于这个问题，有许多不同的选项可供选择：Natural+Sort+in+MySQL+...but没有任何东西可以轻松地实现您想要的功能。|4340876|但是，您可以做的是将所有数据返回到PHP，并在那里使用natsort()函数进行排序。|style|CODE|4340877|$vals+=+array('A1',+'A10',+'A12',+'A2',+'A5',+'A8');
natsort($vals);
print_r($vals);+//+A1,+A2,+A5,+A8,+A10,+A12|code-block|syntax|javascript|4340878|这只对单个值进行排序-但如果需要对整行进行排序，则可以通过使用带有strnatcmp()函数的usort()来实现相同的功能|4340879|请注意，这需要您获取所有的行，以便对它们进行排序-任何限制或分页都必须在中的排序之后进行，这对于大型数据集来说很快就会变得低效。|BOLD|4340880|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/5167928/what-is-natural-ordering-when-we-talk-about-sorting|1|https://stackoverflow.com/questions/153633/natural-sort-in-mysql|2|http://ca3.php.net/manual/en/function.natsort.php|3|http://ca3.php.net/manual/en/function.strnatcmp.php|4|http://ca3.php.net/manual/en/function.usort.php^0|6|W|0|0|0|K|L|1|0|R|9|R|9|2|0|0|X|B|1B|7|X|B|3|1B|7|4|0|10|S|0^^$0|@$1|2|3|4|5|6|7|1B|8|@]|9|@$A|1C|B|1D|1|1E]]|C|$]]|$1|D|3|E|5|6|7|1F|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|1G|8|@]|9|@$A|1H|B|1I|1|1J]]|C|$]]|$1|H|3|I|5|6|7|1K|8|@$A|1L|B|1M|J|K]]|9|@$A|1N|B|1O|1|1P]]|C|$]]|$1|L|3|M|5|N|7|1Q|8|@]|9|@]|C|$O|P]]|$1|Q|3|R|5|6|7|1R|8|@$A|1S|B|1T|J|K]|$A|1U|B|1V|J|K]]|9|@$A|1W|B|1X|1|1Y]|$A|1Z|B|20|1|21]]|C|$]]|$1|S|3|T|5|6|7|22|8|@$A|23|B|24|J|U]]|9|@]|C|$]]|$1|V|3|-4|5|6|7|25|8|@]|9|@]|C|$]]]|W|$X|$5|Y|Z|10|C|$11|12]]|13|$5|Y|Z|10|C|$11|14]]|15|$5|Y|Z|10|C|$11|16]]|17|$5|Y|Z|10|C|$11|18]]|19|$5|Y|Z|10|C|$11|1A]]]]

This is known as a <a href="https://stackoverflow.com/questions/5167928/what-is-natural-ordering-when-we-talk-about-sorting">natural sort, or a natural order</a>.

MySQL doesn't have the built-in ability to do a natural sort. 
There's various options listed in response to this question: <a href="https://stackoverflow.com/questions/153633/natural-sort-in-mysql">Natural Sort in MySQL</a> ...but nothing that easily does what you want.

What you can do though, is get all the data back to PHP, and do the sorting there using the <a href="http://ca3.php.net/manual/en/function.natsort.php" rel="nofollow noreferrer"><code>natsort()</code></a> function.

<pre><code>$vals = array('A1', 'A10', 'A12', 'A2', 'A5', 'A8');
natsort($vals);
print_r($vals); // A1, A2, A5, A8, A10, A12
</code></pre>

This just sorts single values - but if you need to sort entire rows, you get the same functionality by using <a href="http://ca3.php.net/manual/en/function.usort.php" rel="nofollow noreferrer"><code>usort()</code></a> with the <a href="http://ca3.php.net/manual/en/function.strnatcmp.php" rel="nofollow noreferrer"><code>strnatcmp()</code></a> function

Note that this requires you to fetch all the rows in order to sort them - any limiting or paging must be done after the sort in PHP, which can quickly become inefficient with large datasets.

blocks|key|873632|text|如果前缀总是相似的，你可以先按长度排序吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|873633|ORDER+BY+LENGTH(column),+column|code-block|syntax|javascript|873634|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

if the prefixes are always similar, you could maybe order by length first?

<pre><code>ORDER BY LENGTH(column), column
</code></pre>

blocks|key|879733|text|Array
(
++++[1]+=>+Array
++++++++(
++++++++++++[thumb]+=>+oiuy
++++++++)

++++[1a]+=>+Array
++++++++(
++++++++++++[thumb]+=>+asdf
++++++++)

++++[1b]+=>+Array
++++++++(
++++++++++++[thumb]+=>+2345
++++++++)
)

uksort($array,+function($a,+$b)+{

++++sscanf($a,+'%25d%25s',+$an,+$as);
++++sscanf($b,+'%25d%25s',+$bn,+$bs);

++++if($an+==+$bn)
++++++++return+strcmp($as,+$bs);

++++return+$an+-+$bn;
});|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|879734|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Array
(
 [1] =&gt; Array
 (
 [thumb] =&gt; oiuy
 )

 [1a] =&gt; Array
 (
 [thumb] =&gt; asdf
 )

 [1b] =&gt; Array
 (
 [thumb] =&gt; 2345
 )
)

uksort($array, function($a, $b) {

 sscanf($a, '%d%s', $an, $as);
 sscanf($b, '%d%s', $bn, $bs);

 if($an == $bn)
 return strcmp($as, $bs);

 return $an - $bn;
});
</code></pre>

blocks|key|557687|text|使用natsort()。这对于字母数字值非常有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|557688|entityMap|0|LINK|mutability|MUTABLE|url|https://www.w3schools.com/php/func_array_natsort.asp^0|2|9|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Use <a href="https://www.w3schools.com/php/func_array_natsort.asp" rel="nofollow noreferrer">natsort()</a>. This works perfect with alphanumeric values.

in database, it store values of A1, A2, A10, A12, A5, A8
but when i retrieve it out it show as below:-

A1, A10, A12, A2, A5, A8

is there a way to sort it correctly? like

A1, A2, A5, A8, A10, A12

Alphanumeric sorting/ordering from database

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

在数据库中，它存储A1、A2、A10、A12、A5、A8值，但当我检索它时，它显示如下：A1、A10、A12、A2、A5、A8有没有办法对它进行正确的排序？喜欢A1、A2、A5、A8、A10、A12

问数据库中的字母数字排序/排序
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问数据库中的字母数字排序/排序EN