mysql求varchar类型的最大值(或按照varchar类型排序)

工作中为了方便后续扩展，建表时将数字类型定义为varchar类型进行存储，但是在排序时会乱序，这里是因为mysql默认order by 只对数字与日期类型可以排序

演示：

一、数据初始化

CREATE TABLE `testvarcharmax` (
  `age` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

insert into testvarcharmax VALUES ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),('11')；

二、错误结果

1.求age的最大值，结果预期为11，但是sql结果显示9

mysql> select MAX(age) from testvarcharmax;
+----------+
| MAX(age) |
+----------+
| 9        |
+----------+
1 row in set (0.00 sec)

2.按age倒叙排列，结果预期第一行为11，但结果显示9

mysql> select age from testvarcharmax order by age desc;
+------+
| age  |
+------+
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 11   |
| 10   |
| 1    |
+------+
11 rows in set (0.00 sec)

三、修改sql语句

方式一：对age执行加法运算，sql语句改为：

select MAX(age+0) from testvarcharmax;

select age from testvarcharmax order by age+0 desc;

mysql> select MAX(age+0) from testvarcharmax;
+------------+
| MAX(age+0) |
+------------+
|         11 |
+------------+
1 row in set (0.00 sec)

mysql> select age from testvarcharmax order by age+0 desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)

方式二：使用CAST函数字将age转换为int后求最大值或排序、sql语句修改为：

select MAX(CAST(age as signed int)) as maxAge from testvarcharmax;（或select MAX(CAST(age as signed integer)) as maxAge from testvarcharmax; select MAX(CAST(age as signed)) as maxAge from testvarcharmax;）

select age from testvarcharmax order by CAST(age as SIGNED) desc; （或select age from testvarcharmax order by CAST(age as SIGNED int) desc; select age from testvarcharmax order by CAST(age as SIGNED integer) desc;）

mysql> select MAX(CAST(age as signed integer)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select MAX(CAST(age as signed int)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select MAX(CAST(age as signed)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select age from testvarcharmax order by CAST(age as SIGNED) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)

mysql> select age from testvarcharmax order by CAST(age as SIGNED int) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)

mysql> select age from testvarcharmax order by CAST(age as SIGNED integer) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)

方式三、使用CONVERT函数将age转换为int后求最大值或排序、sql语句修改为：

select MAX(CONVERT(age, signed)) as maxAge from testvarcharmax;（或select MAX(CONVERT(age, signed int)) as maxAge from testvarcharmax; select MAX(CONVERT(age, signed integer)) as maxAge from testvarcharmax;）

select age from testvarcharmax order by CONVERT(age, SIGNED) desc; （或select age from testvarcharmax order by CONVERT(age, SIGNED int) desc; select age from testvarcharmax order by CONVERT(age, SIGNED integer) desc;）

mysql> select MAX(CONVERT(age, signed)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select MAX(CONVERT(age, signed int)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select MAX(CONVERT(age, signed integer)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select age from testvarcharmax order by CONVERT(age, SIGNED) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)

mysql> select age from testvarcharmax order by CONVERT(age, SIGNED int) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)

mysql> select age from testvarcharmax order by CONVERT(age, SIGNED integer) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)