在MySQL中使用货币符号排序价格

I have a price field in table which has price as

$5000
$500
$6000
$4000

I want to sort them in ascending order.

I tried

ORDER BY SUBSTRING_INDEX('price', '$', 1),
CAST(SUBSTRING_INDEX(`price`, '$', -1) AS SIGNED)

but its not working.

SELECT CAST(SUBSTRING_INDEX(price, '$', -1) AS SIGNED)
FROM test
ORDER BY 1

select * from (select '$400' cur                                                                                     
    -> union                                                                                                                           
    -> select '$600' cur                                                                                                               
    -> union                                                                                                                           
    -> select '$200' cur) t                                                                                                            
    -> order by substring(cur,2) desc; 

In short, substring in order by clause. So, you should be writing your query something like

SELECT column1,column2 from your table 
order by substring(currency_column,2) desc|asc; 

SELECT price FROM test order by CAST(SUBSTRING_INDEX(price, '$', -1) AS SIGNED)