[分享]MySql varchar排序 CAST ,CONVERT_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2800 | 回复: 0

主题： [分享]MySql varchar排序 CAST ,CONVERT

Koei111

注册用户

等级：列兵
经验：81
发帖：42
精华：0
注册：2011-7-22
状态：离线
发送短消息息给Koei111

加好友发送短消息息给Koei111

发消息

发表于：

2014-12-19 11:25:03 | [全部帖] [楼主帖]

楼主

废话不多说了，自己建表的时候，把一个字段类型创建为varchar(2) ，其实应该建为integer(2)的。因为我只允许输出数字。这本来也没什么，无非就是占点空间，懒得改了。

但是今天在后台发现排序有问题。于是，没办法，改之。下面简单说一下MySQL的varchar排序问题，引以为戒。

示例表结构：

先来看一下，我的表结构

[cc lang=’mysql’]
show create table cardserver \G
*************************** 1. row ***************************
Table: cardserver
Create Table: CREATE TABLE cardserver (
id int(11) NOT NULL default ‘0’,
ver int(11) default NULL,
createtime datetime default NULL,
updatetime datetime default NULL,
game_id int(2) NOT NULL default ‘0’,
server_id varchar(2) NOT NULL default ”,
server_name varchar(40) NOT NULL default ”,
PRIMARY KEY (id),
UNIQUE KEY game_id_server_id (game_id,server_id),
UNIQUE KEY game_id_server_name (game_id,server_name)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
[/cc]

因为有外键的存在，所以我不想改变字段类型，费劲啊。呵呵。虽然最后我还是选择了更改字段类型，这是后话。因为我本篇日志想要说明的是varchar排序问题。所以不再说明我是如何更改字段类型的，大家有兴趣可以搜索我以前的日志。（骗一下点击）

现象描述：

下面，我从数据库里面以server_id排一下序，大家来看一下排序后的结果：

[cc lang=’mysql’]
select server_id from cardserver where game_id = 1 order by server_id desc limit 10;
+———–+
| server_id |
+———–+
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 10 |
| 1 |
+———–+
[/cc]

很明显，我想要的结果应该是 10,8,7,6,5 这样的。但是这个10排在了2的后面。按照字符串来排的。其实我是想把它当做数值来排。

手动转换类型：

用下面的方法就可以了，使server_id+0之后再排序，问题解决了。

[cc lang=’mysql’]
select server_id from cardserver where game_id = 1 order by server_id+0 desc limit 10;
+———–+
| server_id |
+———–+
| 10 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+———–+
[/cc]

使用MySQL函数CAST/CONVERT：

mysql为我们提供了两个类型转换函数：CAST和CONVERT，现成的东西我们怎能放过？

CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。
这个类型可以是以下值其中的一个：

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

所以我们也可以用CAST解决问题：

[cc lang=’mysql’]
select server_id from cardserver where game_id = 1 order by CAST(server_id as SIGNED) desc limit 10;
+———–+
| server_id |
+———–+
| 10 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+———–+
[/cc]

也可以使用CONVERT来搞定此问题：

[cc lang=’mysql’]
select server_id from cardserver where game_id = 1 order by CONVERT(server_id,SIGNED) desc limit 10;
+———–+
| server_id |
+———–+
| 10 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+———–+
[/cc]

总结：

条条大道通罗马，条条小路通我家。不管是啥方法，解决问题就是好方法。当然，既然MySQL为我们提供了现成的函数。我们为何不让代码显得更漂亮些呢？呵呵。

所以MySql varchar排序我推荐使用CAST或CONVERT函数。

--转自