关于Mysql的“字符串”数值转换使用

首先我们先讨论下什么时候，可能会用到Mysql的字符串转数值类型？

• “数值”字符串用来按大小排序；

遇到这样的问题，首先是不恰当的数据库设计导致的。但存在这种情况，我相信也一定存在其合理性。但是存在即合理，毕竟因为“不合理”的存在本身就是一种“合理”……

为了避免大家方便大家阅读、沟通，子涵斗胆定义一个词：“数值”字符串或“字符串”数值，其特点如下：

• 其值在mysql中本身是字符串；
• 但开发者又想表达其数值功能，比如比大小、排序。

一、mysql支持长度超过bigint的大数排序

SELECT * FROM
	( SELECT 1111111111111111111111111111111 id 
	UNION ALL SELECT 1111111111111111111111111111112 id 
	UNION ALL SELECT 1111111111111111111111111111111 id 
	UNION ALL SELECT 1111111111111111111111111111113 id ) s 
ORDER BY id DESC;
+---------------------------------+
| id                              |
+---------------------------------+
| 1111111111111111111111111111113 |
| 1111111111111111111111111111112 |
| 1111111111111111111111111111111 |
| 1111111111111111111111111111111 |
+---------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

首先mysql是支持超大数值的排序的，但这样的数据在mysql中该如何存储呢？别忘了，Mysql还有一种数据类型，decimal，最大长度不超过64。

create table tmp_big_num as
SELECT * FROM
	( SELECT 1111111111111111111111111111111222 id 
	UNION ALL SELECT 1111111111111111111111111111112 id 
	UNION ALL SELECT 1111111111111111111111111111111 id 
	UNION ALL SELECT 1111111111111111111111111111113 id ) s 
ORDER BY id DESC;
1
2
3
4
5
6
7

查看一下Mysql自动生成的建表语句：

CREATE TABLE `tmp_big_num` (
  `id` decimal(31,0) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1
2
3

其中decimal(31,0)中的31取决于最大数据长度。

二、Mysql“数值”字符串转换

隐式转换（不推荐）

mysql>  select * from ( 
    ->  select '1111111111111111111111111111111'+0 id 
    ->  union all select '1111111111111111111111111111112'+0 id 
    ->  union all select '1111111111111111111111111111111'+0 id 
    ->  union all select '1111111111111111111111111111113'+0 id ) s 
    ->  order by id desc ;
+----------------------+
| id                   |
+----------------------+
| 1.111111111111111e30 |
| 1.111111111111111e30 |
| 1.111111111111111e30 |
| 1.111111111111111e30 |
+----------------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

注意：在某些编辑器（DBeaver）中，你可能会看到这样的结果，请不要惊奇：

1111111111111111000000000000000
1111111111111111000000000000000
1111111111111111000000000000000
1111111111111111000000000000000
1
2
3
4

隐式转换虽然不会报错，但是其默认转换的数据可能存在一定错误。因此，我们应该正确使用convert函数进行显式转换。

使用convert函数进行显式转换（推荐）

 select * from ( 
 select convert('1111111111111111111111111111111', DECIMAL(31,0)) id 
 union all select convert('1111111111111111111111111111112', decimal(31,0)) id 
 union all select convert('1111111111111111111111111111111', decimal(31,0)) id 
 union all select convert('1111111111111111111111111111113', decimal(31,0)) id ) s 
 order by id desc ;
1
2
3
4
5
6

结果：

+---------------------------------+
| id                              |
+---------------------------------+
| 1111111111111111111111111111113 |
| 1111111111111111111111111111112 |
| 1111111111111111111111111111111 |
| 1111111111111111111111111111111 |
+---------------------------------+
1
2
3
4
5
6
7
8

三、Mysql中的数值类型

1、整型

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。

int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个m有什么用。

2、浮点型(float和double)

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.

3、定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数m<65 是总个数，d<30且 d

四、这种字段在数据库中该如何存储呢？

首先应该避免这样的设计：

• 字符串对字符内容的限制可以很灵活，不是所有的字符串都可以直接通过隐式转换为数值；
• 从业务角度讲，存在数值类型操作的字段，应使用相应的数值类型。Mysql提供了丰富的数值类型，如：int、bigint、decimal等。

当然了，回到文章开头的需求，对“大数”字符串进行排序比大小，细心的同学可能发现同样长度的字符串排序也可以保证正确的顺序。不错，mysql提供了lpad函数来实现字符串长度填充，本文不再展开，欢迎自行尝试。