current position:Home>Explore the vachar, test, longtext storage limits of the database mysql

Explore the vachar, test, longtext storage limits of the database mysql

2023-01-25 11:20:10source world


If you feel a little interesting, please pay attention,一键三连吧!蟹蟹!!!

背景

Want to understand clearly,使用longtext类型,Save rich text data tomysqlIs the database sufficient,And how is it enough.I searched the Internet for a long time and found no answer,自己尝试了一下,Although not to the limit,但是完全够用.
longtest类型,对应java和mybatisThe frame type is string.

介绍

    mysql5.0版本以前,longtext可以储存127个文字.5.0以上版本longtext可以储存32767个文字,utf8编码下最多支持10921个字符(不为空).
    mysql5.0版本以前,longtextThe maximum can be defined255字节长度.5.0以上版本支持65535字节长度,utf8编码下最多支持21843个字符(不为空).
    Because a literal occupies two bytes,因此mysql5.0版本以前,longtext可以储存127个文字.5.0以上版本longtext可以储存32767个文字,utf8编码下最多支持10921个字符(不为空).
扩展资料:
longtextUse than fixed length type(text)Take up more storage space(除了使用ROW_FORMAT=FIXED创建的MyISAM表).节约空间,So performance will help.Additional work will be generated when updating.
5.0以上版本,longtextGet value or set value will preserve the space at the end of the string,4.1之前的版本longtextwill remove the spaces at the end of the string.The maximum length is much larger than the average length,Suitable for use when updates are rarelongtext,Because there are fewer fragments.
简单了解下
varcharIt is a variable-length byte,所占空间为字符串Actual length plus1,最长为65535个字节
而longtextIt is also variable-length character storage,只保存字符数据,最长为4294967295字节,More suitable for storing large content
换句话来说(理想状态下):

4294967295除以3得到,1431655765个汉字,14亿多.

项目实操

You can create a table however you like,id,content两个字段,It can also be created as per my table.
Problems you may have,For example, the auto-increment of the primary key is not according to what you want,可以看这一篇博客:idea中mybatisPlusThe auto-increment primary key fails
创建表

DROP TABLE IF EXISTS `duck_message`;
CREATE TABLE `duck_message`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `message_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标题',
  `message_content` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '内容',
  `message_images` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '图片地址',
  `status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态(L-message list,P-排行榜,T-推荐榜)',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `creation_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户id',
  `type_push` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Is it on the list(Y-上榜,N-Not on the list)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Duck friends list' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

注意:longtextIt is not necessary to set the length
在这里插入图片描述

代码很简单,It's just an insert statement:创建一个Springboot项目,使用mybatis-plus自动生成.Then insert table data.
如何自动生成,可以看下这篇博客:mybatisPlus自动生成代码
I use rich text to call,可以参考博客:summernote富文本使用.也可以使用apipost进行测试,下面有介绍.
完整代码

getInputValue() {
      var that = this;
      // 获取summernoteThe method of entering the value $('#summernote2').summernote('code')
      that.contant = $("#summernote2").summernote("code");
      console.log(that.contant);
      that.axios({
        url: "http://localhost:8081/duckMessage/text",
        method: "POST",
        data: that.contant,
        headers: {
          // "Content-Type": "application/x-www-form-urlencoded",
          "Content-Type": "text/plain",
        },
        success: function (data) {
          console.log("返回的数据" + data.data);
        },
      });
    },
//Controller层
//测试,The maximum insert data that can be inserted
@PostMapping("/text")
    public Result textInsert(@RequestBody String contant){
    
    System.out.println("值:"+contant);
    int num= duckMessageService.textInsert(contant);
    return new Result("200",num,"成功");
}
//serveceimp层
@Override
    public int textInsert(String contant) {
    
    DuckMessage duckMessage = new DuckMessage().setMessageContent(contant).setCreationTime(new Date());
	int num = duckMessageMapper.insert(duckMessage);
	return num;
}

接近78万个字
在这里插入图片描述
我是用的SummernoteRich text to try to call,文件显示2.3M(也可以直接使用Apipost调用)
在这里插入图片描述

Logs printed in the background
在这里插入图片描述
表数据

在这里插入图片描述

I'm ready to double again,但是复制粘贴,Display speed is too slow.不再尝试.
在这里插入图片描述

If want to try it yourself,使用Apipost工具,调用接口测试即可

官方下载地址:https://www.apipost.cn/
注意:The client must be downloaded to debug locally,Only non-local interfaces are supported on the web side
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

表数据
在这里插入图片描述

mysql类型

数字类型
在这里插入图片描述

辨別Null与Not Null :Null为允许储存空值(Null)

数值
在这里插入图片描述日期时间
在这里插入图片描述

Pay attention if you find it useful,一键三连吧!蟹蟹!!!
在这里插入图片描述

各位看官》创作不易,点个赞!!!
诸君共勉:万事开头难,只愿肯放弃.

免责声明:本文章仅用于学习参考

copyright notice
author[source world],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2023/025/202301251109140390.html

Random recommended