MySQL

MySQL中BLOB和TEXT数据类型的区别

在本文中，我们将介绍MySQL中BLOB和TEXT数据类型的区别。MySQL是一种流行的关系型数据库管理系统，支持多种数据类型，包括BLOB和TEXT数据类型。这两种数据类型用于存储大块数据，例如图像、音频和文本文件等。

BLOB和TEXT数据类型的定义

BLOB是二进制大对象（Binary Large Object）的缩写，是MySQL中用来存储二进制数据的类型。它可以存储任何类型的数据，例如图像、音频、视频等。BLOB数据类型的数据可以很大，但它不能进行排序或与其他BLOB值进行比较。

TEXT是用于存储字符数据的数据类型。它可以存储任何类型的字符数据，例如长文本、XML数据、HTML数据等。它可以进行排序和与其他TEXT值进行比较。

BLOB和TEXT数据类型的存储方式

BLOB数据类型存储数据时，MySQL会将数据以二进制形式完整地保存到磁盘中。因此，BLOB类型占用的存储空间取决于数据的实际大小。BLOB类型可以存储最大为64KB的数据，但如果需要存储更大的数据，则需要将数据拆分成多个BLOB块存储。

TEXT数据类型存储数据时，它会将数据以字符形式完整地保存到磁盘中。因此，TEXT类型占用的存储空间取决于数据的实际大小。但是，如果需要存储大量的数据，则可以将数据拆分成多个TEXT块进行存储。

BLOB和TEXT数据类型的区别

BLOB和TEXT数据类型的最大区别在于存储的数据类型不同。BLOB类型存储二进制数据，而TEXT类型存储字符数据。

此外，它们对于根据大小进行排序和比较的支持也不同。BLOB数据类型不能支持对它们进行排序或与其他BLOB值进行比较。而TEXT数据类型则可以通过比较字符的编码值进行排序和比较。

BLOB数据类型在存储大量数据时，对于性能的影响相对较小。但是，由于BLOB类型的数据不能进行排序或与其他BLOB值进行比较，因此在需要排序或比较数据时，应尽量避免使用BLOB类型。

示例说明

假设我们需要存储一个文本文件，大小为5MB。我们可以使用BLOB类型或TEXT类型来存储。当使用BLOB类型存储该文件时，MySQL会将整个文件存储在一个BLOB列中，因此该列并不能排序或与其他BLOB值进行比较。而当使用TEXT类型存储该文件时，MySQL会将该文件分成多个块进行存储，但我们可以对这些文本块进行排序或比较。

总结

BLOB和TEXT数据类型是MySQL中用于存储二进制和字符数据的类型。BLOB数据类型存储二进制数据，而TEXT数据类型存储字符数据。BLOB数据类型不能排序或与其他BLOB值进行比较，而TEXT数据类型可以根据字符编码进行排序和比较。在存储大块数据时，应根据实际需求选择适当的类型来存储数据。

字符串中字母数字混合排序问题

场景：

mark 字段由特定前缀 ，-，字母，数字组成。

select distinct mark
from t_mark order by mark desc ;

实际上的排序结果会出现断层现象：

我们希望 “aa-T1082” 的序号应该出现在 “aa-T999”前面。

可以采用 2 中方式解决：

• 一是改变字段内容结构，在数字前加上零，让所有的字段值拥有相同的长度。
• 二是不改变字段内容，在排序的SQL语句上想办法。

order by mid(column,2,10) +1

切分截取

函数：SUBSTRING_INDEX(str,delim,count) 解释：从第 count 个 分割符(delim) 处分割字符串(str),返回子字符串。 参数说明： str：将要处理的字符串 delim ：用来分割 str 分割符 count：第 count 个分割符 返回值： 若 count 为正，则返回分割符左侧的所有内容（从左侧开始计数）; 若 count 为负，则返回分割符右边的所有内容（从右边开始计数）。

注：分割符是大小写敏感的

# 切分
select distinct mark, substring_index(mark, '-', -1)
from t_mark
order by mark desc;

结果：

再次截取，获得数字部分字符串。

​ mid()第一个参数是字串内容，可以是字段名。第二个参数是起始字符的位置，我这里需要从第二个字符开始。第三个参数是需要取得的长度，要选一个可以包含所有字段内容长度的值，宁大勿小。

# 截取
select distinct mark, substring_index(mark, '-', -1), mid(substring_index(mark, '-', -1), 2, 10)
from t_mark
order by mark desc;

排序

但这样取出来的还只是“数字内容的字符串”，对它排序仍然使用字符规则，不会得到我们想要的结果。 这时需要给这个结果再加上一个数值，让MySQL对它进行一次算术运算，然后得到的就是一个整型值，这时再排序就是数字顺序的结果了。 例如：

select distinct mark
from t_mark
order by mid(substring_index(mark, '-', -1), 2, 10) + 1 desc;

还存在的一个现象是：当后缀数字相近时，前缀部分会交替出现

同样的方法：截取前缀

select distinct mark,substring_index(mark, '-', 1)[2023-05-01-mall-notes.md](2023-05-01-mall-notes.md)
from t_mark
order by mid(substring_index(mark, '-', -1), 2, 10) + 1 desc;

然后将前缀作为优先排序条件：

select distinct mark
from t_mark
order by substring_index(mark, '-', 1) desc, mid(substring_index(mark, '-', -1), 2, 10) + 1 desc;