MySQL 对文本和数字进行自然排序

Question

我有包含项目名称的表格，例如:

...
Cheburashka with detailed ear 10g. (O.Box 5 pcs.)
Cheburashka with detailed ear 12g. (O.Box 5 pcs.)
Cheburashka with detailed ear 4g. (O.Box 5 pcs.)
Skittle with two swivels 20g. (O.Box 5 pcs.)
Skittle with two swivels 6g. (O.Box 5 pcs.)
...

我需要:

...
Cheburashka with detailed ear 4g. (O.Box 5 pcs.)
Cheburashka with detailed ear 10g. (O.Box 5 pcs.)
Cheburashka with detailed ear 12g. (O.Box 5 pcs.)
Skittle with two swivels 6g. (O.Box 5 pcs.)
Skittle with two swivels 20g. (O.Box 5 pcs.)
...

等等，我需要按字母顺序对行进行排序，并使用值数字 - 权重。

SQL Fiddle 链接 - http://sqlfiddle.com/#!2/4cbd8/1

我尝试使用 LENGTH()、CAST()、SUBSTRING_INDEX() 对行进行排序，但都失败了。

Answer 1

查询 1:

SELECT item_name
FROM items 
ORDER BY 
reverse(mid(
reverse(mid(replace(replace(replace(replace(replace(item_name,',',''),' . ','. '),'r. ','g. '),'d. ','g. '),' g ','g. '),1,instr(replace(replace(replace(replace(item_name,' . ','. '),'r. ','g. '),'d. ','g. '),' g ','g. '), 'g. (')-1)),
instr(reverse(mid(replace(replace(replace(replace(replace(item_name,',',''),' . ','. '),'r. ','g. '),'d. ','g. '),' g ','g. '),1,instr(replace(replace(replace(replace(item_name,' . ','. '),'r. ','g. '),'d. ','g. '),' g ','g. '), 'g. (')-1)),' ')
)),
cast(reverse(mid(
reverse(mid(replace(replace(replace(replace(replace(item_name,',',''),' . ','. '),'r. ','g. '),'d. ','g. '),' g ','g. '),1,instr(replace(replace(replace(replace(item_name,' . ','. '),'r. ','g. '),'d. ','g. '),' g ','g. '), 'g. (')-1)),1,  
instr(reverse(mid(replace(replace(replace(replace(replace(item_name,',',''),' . ','. '),'r. ','g. '),'d. ','g. '),' g ','g. '),1,instr(replace(replace(replace(replace(item_name,' . ','. '),'r. ','g. '),'d. ','g. '),' g ','g. '), 'g. (')-1)),' ')
)) as signed)

<强> Results :

|                                                ITEM_NAME |
|----------------------------------------------------------|
|   Load Cheburashka with detailed ear 4g. ( O.Box 5 pcs.) |
|   Load Cheburashka with detailed ear 6d. ( O.Box 5 pcs.) |
|   Load Cheburashka with detailed ear 8g. ( O.Box 5 pcs.) |
|  Load Cheburashka with detailed ear 10g. ( O.Box 5 pcs.) |
|  Load Cheburashka with detailed ear 12g. ( O.Box 5 pcs.) |
| Load Cheburashka with detailed ear 14g . ( O.Box 5 pcs.) |
| Load Cheburashka with detailed ear 16g . ( O.Box 5 pcs.) |
| Load Cheburashka with detailed ear 18g . ( O.Box 5 pcs.) |
| Load Cheburashka with detailed ear 20g . ( O.Box 5 pcs.) |
| Load Cheburashka with detailed ear 22g . ( O.Box 3 pcs.) |
| Load Cheburashka with detailed ear 24g . ( O.Box 3 pcs.) |
| Load Cheburashka with detailed ear 26g . ( O.Box 3 pcs.) |
| Load Cheburashka with detailed ear 28g . ( O.Box 3 pcs.) |
| Load Cheburashka with detailed ear 32g . ( O.Box 3 pcs.) |
| Load Cheburashka with detailed ear 36g . ( O.Box 3 pcs.) |
| Load Cheburashka with detailed ear 40g . ( O.Box 3 pcs.) |
|                  Load Cone with dope 3d. ( O.Box 5 pcs.) |
|                  Load Cone with dope 4g. ( O.Box 5 pcs.) |
|                  Load Cone with dope 6d. ( O.Box 5 pcs.) |
|                 Load Cone with dope, 8g. ( O.Box 5 pcs.) |
|                 Load Cone with dope 10g. ( O.Box 5 pcs.) |
|                 Load Cone with dope 12g. ( O.Box 5 pcs.) |
|                Load Cone with dope 13g . ( O.Box 5 pcs.) |
|                Load Cone with dope 15g . ( O.Box 5 pcs.) |
|                Load Cone with dope 18g . ( O.Box 5 pcs.) |
|               Load Ovsinka with dope 3d. ( O.Box 5 pcs.) |
|              Load Ovsinka with dope, 7g. ( O.Box 5 pcs.) |
|              Load Ovsinka with dope 10g. ( O.Box 5 pcs.) |
|             Load Ovsinka with dope 14g . ( O.Box 5 pcs.) |
|             Load Ovsinka with dope 18g . ( O.Box 5 pcs.) |
|               Load Skittle with dope 6d. ( O.Box 5 pcs.) |
|              Load Skittle with dope, 9g. ( O.Box 5 pcs.) |
|             Load Skittle with dope 14g . ( O.Box 5 pcs.) |
|             Load Skittle with dope 16g . ( O.Box 5 pcs.) |
|             Load Skittle with dope 20g . ( O.Box 5 pcs.) |
|        Load Skittle with two swivels 6d. ( O.Box 5 pcs.) |
|        Load Skittle with two swivels 9g. ( O.Box 5 pcs.) |
|      Load Skittle with two swivels 14g . ( O.Box 5 pcs.) |
|      Load Skittle with two swivels 16g . ( O.Box 5 pcs.) |
|      Load Skittle with two swivels 20g . ( O.Box 5 pcs.) |
|             Oliva cargo with dope, 3.5 g ( O.Box 5 pcs.) |
|               Oliva cargo with dope, 7g. ( O.Box 5 pcs.) |
|            Oliva cargo with dope, 10.5 g ( O.Box 5 pcs.) |
|              Oliva cargo with dope 14g . ( O.Box 5 pcs.) |
|                Thumb load with dope, 2r. ( O.Box 5 pcs.) |
|                 Thumb load with dope 3d. ( O.Box 5 pcs.) |
|                Thumb load with dope, 7g. ( O.Box 5 pcs.) |
|                Thumb load with dope 10g. ( O.Box 5 pcs.) |