MySQL中将字符型字段作为整型排序

MySQL中将字符型字段作为整型排序

在MySQL的使用中，经常会遇到需要将varchar类型的字段作为整数类型进行排序的情况，这时就需要用到一些数据类型转换的技巧。

阅读更多：MySQL 教程

示例

假设有一个表grade，其中有两个字段name和score，其中score字段为varchar类型，包含整数和非整数值。

CREATE TABLE grade (
    name VARCHAR(20),
    score VARCHAR(10)
);

INSERT INTO grade VALUES ('Tom', '60');
INSERT INTO grade VALUES ('Jerry', '85');
INSERT INTO grade VALUES ('Mike', '73');
INSERT INTO grade VALUES ('Mary', '82');
INSERT INTO grade VALUES ('John', '90');
INSERT INTO grade VALUES ('Lucy', '78');
INSERT INTO grade VALUES ('Bob', '75');
INSERT INTO grade VALUES ('Alice', '76');
INSERT INTO grade VALUES ('Tony', '80');
INSERT INTO grade VALUES ('Linda', '66');

如果直接对score字段进行排序，会按照字符型的字典顺序排序，结果可能不符合预期。

SELECT * FROM grade
ORDER BY score;

结果如下：

+-------+-------+
| name  | score |
+-------+-------+
| Tom   | 60    |
| Jerry | 73    |
| Mike  | 75    |
| Mary  | 76    |
| John  | 78    |
| Lucy  | 80    |
| Bob   | 82    |
| Alice | 85    |
| Tony  | 90    |
| Linda | 66    |
+-------+-------+

可以发现，数字60和数字66之间的字典序是数字6和数字7之间，不符合实际需求。这时可以使用函数CAST将score字段转换为整数类型，再进行排序。

SELECT * FROM grade
ORDER BY CAST(score AS SIGNED) DESC;

这样就能得到按照分数从高到低排名的结果：

+-------+-------+
| name  | score |
+-------+-------+
| Tony  | 90    |
| John  | 78    |
| Bob   | 82    |
| Alice | 85    |
| Mary  | 76    |
| Lucy  | 80    |
| Mike  | 75    |
| Jerry | 73    |
| Linda | 66    |
| Tom   | 60    |
+-------+-------+

分析

在MySQL中，函数CAST可以将一个表达式转换为指定的数据类型。在此示例中，将score字段转换为有符号整数类型SIGNED。在转换中，MySQL会尝试将字符串中的数字部分提取出来，如果有非数字字符则返回0。

需要注意的是，如果在varchar类型的字段中包含一些非数字的字符，如空格、字母等，那么它们会被转换成0，这可能会影响排序的精度。

总结

在MySQL中，如果要将varchar类型的字段作为整数类型进行排序，可以使用函数CAST将其转换为整数类型，以实现排序的精确性。但需要注意，字段中包含非数字的字符会影响转换的精度。