文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

第02期:MySQL 数据类型的艺术-大对象字段

2021-10-23 23:52

关注

第02期:MySQL 数据类型的艺术-大对象字段

我以前分享过一篇《MySQL 大对象一例》,今天就来详细说下大对象的优缺点以及使用场景。

我们把 MySQL 的大对象类型分 TEXT / BLOB 和 JSON 两部分来说明。

一、TEXT / BLOB 类型

TEXT 和 BLOB 的区别非常简单。TEXT 存储以明文存储,有对应的字符集和校验规则;BLOB 则以二进制存储,没有字符集和排序规则,所有的对比都是以二进制来进行。

示例 1

创建一张表 c1 字段 f1,f2 分别为 tinytext 和 tinyblob。

mysql> create table c1 (f1 tinytext, f2 tinyblob);
Query OK, 0 rows affected (0.03 sec)

插入示例数据。

insert into c1 values ("a","a"),("b","b"),("B","B"),("d","d"),("F","F"),("你","你"),("我","我"),("是吧","是吧");

根据字段 f1 排序。

mysql> select * from c1 order by f1;
+--------+--------+
| f1     | f2     |
+--------+--------+
| a      | a      |
| b      | b      |
| B      | B      |
| d      | d      |
| F      | F      |
| 你     | 你     |
| 我     | 我     |
| 是吧   | 是吧   |
+--------+--------+
8 rows in set (0.01 sec)

根据字段 f2 排序。

mysql> select * from c1 order by f2;
+--------+--------+
| f1     | f2     |
+--------+--------+
| B      | B      |
| F      | F      |
| a      | a      |
| b      | b      |
| d      | d      |
| 你     | 你     |
| 我     | 我     |
| 是吧   | 是吧   |
+--------+--------+
8 rows in set (0.00 sec)

f1,f2 字段各自排序的结果都不一致。f1 是按照不区分大小写的校验规则,f2 直接二进制检验。

MySQL 的大对象类型,我从以下几类详细说明:

1.1 磁盘空间占用

1.2 表的存储格式

1.3 表引擎相关

NDB 引擎不推荐使用大对象。涉及的细节比较多,比如含有大对象的关联字段不支持引擎关联数据下推;含有大对象的表需要额外的加锁等等。

1.4 索引相关

在大对象字段上建立索引必须是前缀,比如字段 f1 为 text,给前 10 个字符建立索引 idx_f1(f1(10))。

mysql> alter table t2 add key idx_f100(f100);
ERROR 1170 (42000): BLOB/TEXT column "f100" used in key specification without a key length

1.5 分区表相关

分区表分区字段不支持大对象。

mysql> create table p1(f1 text) partition by list columns(f1) (partition p0 values in ("1","2"));
ERROR 1502 (HY000): A BLOB field is not allowed in partition function

1.6 参数相关

mysql_allowed_packet,这个参数代表 MySQL 服务端和客户端传输的单次数据包上限,如果有 text/blob 字段,此参数设置为最大值 1GB。当然了,必须同时设置客户端和服务端。

1.7 如何插入/获取

示例 2

插入一个 /var/lib/mysql-files 目录下的所有文件内容。

root@ytt-pc:/var/lib/mysql-files# ll
总用量 32M
1324681  14M -rw-r--r-- 1 root  root   14M 12月 30 17:23 test3
1311598 6.6M -rw-r--r-- 1 root  root  6.6M 12月 30 17:23 test
1316840 6.6M -rw-r--r-- 1 root  root  6.6M 12月 30 17:23 test2
1316693 5.5M -rw-r--r-- 1 root  root  5.4M 12月 30 17:26 random_test
1315066 4.0K -rw-r--r-- 1 root  root  1.9K 12月 30 14:56 sample_y1.txt.nl
1311256 4.0K -rw-r----- 1 mysql mysql 1.7K 12月 30 16:23 y1.txt
1316129 4.0K -rw-r--r-- 1 root  root  1.5K 12月 30 15:28 sample_y1.txt.nl2
1312042 4.0K -rw-r--r-- 1 root  root  1.5K 12月 30 15:07 sample_y1.txt.awk
1316691 4.0K -rw-rw-rw- 1 mysql mysql 1.4K 12月 30 16:25 y1_older.txt
1316168 4.0K -rw-r----- 1 mysql mysql  874 12月 30 14:41 sample_y1.txt
1315893 4.0K -rw-r----- 1 root  root   874 12月 30 14:45 sample_y1.txt.bak
1311076 4.0K -rw-r----- 1 mysql mysql  662 12月 30 13:56 ascii.txt
1312152 4.0K -rw-r--r-- 1 root  root   648 12月 30 14:37 sample.txt
1325161 4.0K -rw-r--r-- 1 root  root   403 12月 31 10:59 y1111.txt.10
1312256 4.0K -rw-r----- 1 mysql mysql  164 12月 31 09:49 y111.txt
1311248 4.0K -rw-r----- 1 mysql mysql  159 12月 31 09:42 y11.txt
1327711 4.0K -rw-r--r-- 1 root  root   126 12月  5 09:56 performance_schema_enable.sql
1326639 4.0K -rw-r--r-- 1 root  root    22 1月  10 11:32 系统名称

存放文件内容的表结构。

mysql-(ytt/3305)->create table file_list_upload_dir(file_name varchar(100) not null primary key,content longtext, updated datetime);
Query OK, 0 rows affected (0.03 sec)

插入文件内容。

root@ytt-pc:/var/lib/mysql-files# for i in $(ls); 
do mysql -uytt -pytt -P3305 -h127.0.0.1  
-e "use ytt;insert into file_list_upload_dir  
values ("$i",load_file("$(pwd)/$i"),now())";done;

二、JSON 类型

JSON 类型相比大家都非常熟悉了,轻量级的文本交互格式,不依赖于任何语句。

制定信息参考: https://tools.ietf.org/html/rfc7159 https://tools.ietf.org/html/rfc7396

JSON 类型在 MySQL 内部以特殊的二进制方式存放,类似于 PostgreSQL 的 JSONB 类型。最大占用空间和 longtext 或者 longblob 一样。text 其实也能存储 JSON 对象,但是没有 JSON 类型的格式校验以及内部提供的众多函数。比如以下例子:

示例 3

变量 @a 和 @b 分别为标准 JSON 格式和非 JSON 格式。创建 json1 表。

mysql-(ytt/3305)->set @a="{"a":1,"b":2,"c":3,"d":4}";
Query OK, 0 rows affected (0.00 sec)

mysql-(ytt/3305)->set @b="{"a":1,"b":2,"c":3,"d":4}";
Query OK, 0 rows affected (0.00 sec)

mysql-(ytt/3305)->create table json1 (str1 json,str2 longtext);
Query OK, 0 rows affected (0.02 sec)

给 str1 插入 @a 成功,@b 失败;str2 任何字符都能插入。

mysql-(ytt/3305)->insert into json1 values (@a,@a);
Query OK, 1 row affected (0.00 sec)

mysql-(ytt/3305)->insert into json1 values (@b,@b);
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column "json1.str1".

mysql-(ytt/3305)->insert into json1 values (@a,@b);
Query OK, 1 row affected (0.01 sec)

又比如说检索部分。对 JSON 的检索比对 text 的检索容易太多。还是拿刚才的例子,需要拿出 a 对应的值 1,对 @b 来说,可能比较麻烦,但是换成 @a 就容易多了。json_extract 函数即可。

mysql-(ytt/3305)->select json_extract(@a,"$.a") as "a";
+------+
| a    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

但是有一点比较幸运的时,针对 @b 这种格式,可以把单个字段拆分为多个,这样就可以利用 SQL 语句简单的检索出结果来了。关于拆或者不拆的优劣暂时不在本篇讨论范围内,将会在表设计理念篇来详细介绍。好了,本篇关于 MySQL 的大对象类型就介绍就到此为止,希望对大家有所帮助。


关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯