MySQL 用于排序的 Varchar 到数字转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1868965/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Varchar to number conversion for sorting
提问by Dmitry
I have a query ordered by column:
我有一个由column以下人员订购的查询：
select * from mytable order by column asc — sort table
columntype is varchar, so the output is:
column类型是 varchar，所以输出是：
1
10
100
11
12
13
How should I sort if I want them to sort by numeric value so the output is:
如果我希望它们按数值排序，我应该如何排序，以便输出：
1
10
11
12
13
100
回答by Pablo Santa Cruz
Use:
用：
order by cast(column as unsigned) asc
回答by Vineeth Pradhan
You can use this if you want to treat columnas INTonly:
如果您只想将其column视为INT：
SELECT * FROM mytable ORDER BY column+0;
1
10
11
12
13
100
Or this if you want to treat columnas both INTand VARCHAR
或者这个，如果你想治疗column既INT和VARCHAR
SELECT * FROM mytable ORDER BY column+0, column; #this will sort the column by VARCHAR first and then sort it by INT
abc
xyz
1
10
11
12
13
100
回答by mmattax
This should work as well:
这也应该有效：
order by (0 + column) asc
回答by Vinod Khatik
If we simply modify the order by declaration slightly (add “+0″ to the order by field), you can force MySQL to sort the field naturally.
如果我们简单的修改order by声明（在order by字段加“+0”），就可以强制MySQL对字段进行自然排序。
> select * from mytable order by column+0 asc;
column
1
10
11
12
13
100
回答by Pragnesh Karia
Added a full code script here , but need to sort 1001 and 1002 before - as well.
We have total 5 solution , means 5 different queries as solution with full script.
=============================================================
SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = 'SYSTEM';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
DROP TABLE IF EXISTS `varchar_sort`;
CREATE TABLE `varchar_sort` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`actual_user_id` varchar(200) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `varchar_sort` (`user_id`, `actual_user_id`) VALUES
(1, '1001-4'),
(2, '1001-1'),
(3, '1001-111'),
(4, '1002-1'),
(5, '1001-66'),
(6, '1001-100'),
(7, '1001-110');
SELECT user_id,actual_user_id,CONVERT(SUBSTRING_INDEX(actual_user_id,'-',-1),UNSIGNED INTEGER) AS num
FROM varchar_sort
ORDER BY num;
SELECT user_id,actual_user_id
FROM varchar_sort
ORDER BY CONVERT(SUBSTRING(actual_user_id, 6), SIGNED INTEGER);
SELECT user_id,actual_user_id
FROM varchar_sort
ORDER BY CONVERT(SUBSTRING(actual_user_id, LOCATE('-', actual_user_id) + 1), SIGNED INTEGER);
SELECT *, CAST(SUBSTRING_INDEX(actual_user_id, '-', -1) AS UNSIGNED) as num FROM varchar_sort ORDER BY num;
select * from varchar_sort order by convert( replace(actual_user_id, '-',''), SIGNED INTEGER ) asc
select * from varchar_sort order by convert( replace(actual_user_id, '-',''), SIGNED INTEGER ) asc
**Need to sort 1001 and 1002 as well.**
