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:10【source 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;
}