I have a MySQL table with just one column. There are over 16 million rows in this table. The table contains all the RGB color values closed by parentheses. For instance, (100,155,255). However, they are unsorted. How do I sort such that the first row will be (0,0,0), the second row (0,0,1) etc and the last row be (255,255,255)? I have tried ORDER BY CAST which doesn't seem to work.
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
回答1:
You could try using cast on substring
SELECT your_column
from your_table
order by CAST(replace( SUBSTRING_INDEX(SUBSTRING_INDEX( your_column, ',', 1), ',', -1) , '(','') AS UNSIGNED)
, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( your_column, ',', 2), ',', -1) AS UNSIGNED)
, CAST(replace(SUBSTRING_INDEX(SUBSTRING_INDEX( your_column, ',', 3), ',', -1), ')','') AS UNSIGNED)