首页 > 数据库 >总结MYSQL中VHARCHAR和TEXT

总结MYSQL中VHARCHAR和TEXT

时间:2023-11-18 13:02:24浏览次数:55  
标签:varchar 字节 TEXT MYSQL bytes VHARCHAR text 65535 255

总结MYSQL中VHARCHAR和TEXT_bc

前几天在设计表结构时,针对表中的一个字段使用text还是使用varchar是受到了开发同学的挑战。本篇文章对text和varchar的区别做个总结。

VHARCHAR和TEXT对比

  1. char(n)varchar(n)中括号中n代表字符的个数,并不代表字节个数,所以当使用了中文的时候(utf8)意味着可以插入m个中文,但是实际会占用m*3个字节。
  2. 同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间,而varchar只会占用实际字符应该占用的空间+1,并且实际空间+1<=n。
  3. 超过char和varchar的n设置后,字符串会被截断。
  4. char的上限为255字节,varchar的上限65535字节,text的上限为65535。
  5. char在存储的时候会截断尾部的空格,varchar和text不会。
  6. varchar会使用1-3个字节来存储长度,text不会。

下图可以非常明显的看到结果:

Value

CHAR(4)

Storage Required

VARCHAR(4)

Storage Required

''

' '

4 bytes

''

1 byte

'ab'

'ab '

4 bytes

'ab'

3 bytes

'abcd'

'abcd'

4 bytes

'abcd'

5 bytes

'abcdefgh'

'abcd'

4 bytes

'abcd'

5 bytes

总体来说:

  1. char,存定长,速度快,存在空间浪费的可能,会处理尾部空格,上限255。
  2. varchar,存变长,速度慢,不存在空间浪费,不处理尾部空格,上限65535,但是有存储长度实际65532最大可用。
  3. text,存变长大数据,速度慢,不存在空间浪费,不处理尾部空格,上限65535,会用额外空间存放数据长度,顾可以全部使用65535。

场景问题

从空间方面

varchar

varchar在mysql中满足最大行限制,也就是65535(16k)字节,在mysql中使用utf8(mysql中的utf8和我们正常的编码utf8不同)字符集一个字符占用三个字节。

  1. 使用utf8字符编码集varchar最大长度是(65535-2)/3=21844个字符(超过255个字符会有2字节的额外占用空间开销,所以减2,如果是255以下则减1),mysql中使用utf8字符集一个字符占用3个字节。
  2. 使用utf8mb4字符集varchar最大长度是(65535-2)/4=16383 个字符(超过255个字符会有2字节的额外占用空间开销,所以减2,如果是255以下则减1,mysql中utf8mb4字符集也就是我们通常使用的utf8mb4字符集),mysql中使用utf8mb4字符集一个字符占用4个字节。

text

最大限制是64k,采用utf8字符集占用(262144-2)/3=87381个字符;采用utf8mb4字符集占用(262144-2)/4=65535个字符。除此之外基于 blob(二进制大对象)类型的变体还有如下三个:

  1. text 的最大长度为 65,535 字节,与 varchar 相同。
  2. mediumtext 的最大长度约为 16 兆字节。
  3. longtext 的最大长度约为 4 gb。

另外,从官方文档中可以得知当varchar大于某些数值的时候其会自动转换为text,大概规则如下:

  1. 大于varchar(255)变为 tinytext
  2. 大于varchar(500)变为 text
  3. 大于varchar(20000)变为 mediumtext

所以对于过大的内容使用varchar和text没有太多区别。

关于性能方面

索引会是影响性能的最关键因素,而对于text来说只能添加前缀索引,并且前缀索引最大只能达到1000字节。而貌似varhcar可以添加全部索引,但是经过测试其实也不是。由于会进行内部的转换,所以long varchar其实也只能添加1000字节的索引,如果超长了会自动截断。如下代码:

localhost.test>create table test (a varchar(1500));
Query OK, 0 rows affected (0.01 sec)

localhost.test>alter table test add index idx_a(a);
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 2

localhost.test>show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+

从上面可以明显单看到索引被截断了,而这个767是由于innodb自身的问题,使用innodb_large_prefix设置。

总结

  1. 使用varchar可以选择大小,并为存储设置上限;使用text则不能设置上限,固定长度65535(16k)。
  2. 使用varchar可以设置默认值;text不可以设置默认值。
  3. 使用varchar可以进行排序查询;text不可以排序查询。
  4. 使用varchar超过255长度都会隐式的转为text处理。
  5. 涉及text列的查询可能会导致在磁盘而不是内存中创建临时表。使用基于磁盘的表会显着降低性能,这意味着使用 text 的查询可能会慢得多。
  6. 使用text字段时需要将text字段单独放到一个表中。

完!

标签:varchar,字节,TEXT,MYSQL,bytes,VHARCHAR,text,65535,255
From: https://blog.51cto.com/u_14800147/8460982

相关文章

  • mysql数据库ERROR 1193 (HY000): Unknown system variable 'validate_password_policy
    一、概况  平时我们安装完数据库,需要进行对应的密码或者密码策略修改,此时需要mysql的密码验证插件。MySQL可能没有这个插件,就需要进行相应的处理。二、问题描述mysql>setglobalvalidate_password_policy=0;ERROR1193(HY000):Unknownsystemvariable'validate_passw......
  • Mysql数据库的四大语言
    1.DDL(数据定义语言):DDL主要用于定义数据库对象,如创建、修改和删除表、索引、视图、序列等对象。常用的DDL命令包括:CREATE、ALTER、DROP、TRUNCATE等 2.DML(数据操作语言) :DML主要用于对数据库中的数据进行操作,如插入、更新和删除数据等。常用的 DML命令包括:INSERT、UPDATE、......
  • 数据库入门:掌握MySQL数据库的五大基本操作,轻松驾驭数据世界!
    对数据库进行查询和修改操作的语言叫做SQL(StructuredQueryLanguage,结构化查询语言)。SQL语言是目前广泛使用的关系数据库标准语言,是各种数据库交互方式的基础。在之前的文章中,我们已经掌握了SQL语言的基本概念以及常用的DDL(数据定义)和DML(数据操作)语句。接下来,我们将探讨如何......
  • MySQL数据库操作不再难:从查看到删除,一步步教你成为数据库高手!
    对数据库进行查询和修改操作的语言叫做SQL(StructuredQueryLanguage,结构化查询语言)。SQL语言是目前广泛使用的关系数据库标准语言,是各种数据库交互方式的基础。在之前的文章中,我们已经掌握了SQL语言的基本概念以及常用的DDL(数据定义)和DML(数据操作)语句。接下来,我们将探讨如何......
  • Visual Studio编译调试MySQL源码
    最近想阅读下MySQL源码,虽然在linux下编译MySQL源码还是很顺利的,但是奈何不习惯在linux下阅读代码,比较喜欢用宇宙第一IDEVisualStudio来阅读调试代码,下面就记录下在windows上用visualstudio来编译MySQL5.7.44的源码。下载mysql源码浏览器打开网页:https://dev.mysql.com/dow......
  • MySQL调优学习-快速获取占用CPU较高的SQL语句
    MySQL调优学习-快速获取占用CPU较高的SQL语句背景早上突然发现一个MySQL数据库的CPU使用率居高因为是一个混布的环境上面还有一个redis怕影响业务就上去像查看一下具体是何种原因导致的速度慢因为实发突然,并没有mysqlem等工具所以从网上学到了一个方法进行简单学习与验......
  • php_mysql: dlt - 数据库操作
    php_mysql:  dlt-数据库操作    一、数据库的建立和数据插入 1、数据库建立1DROPDATABASE`tmp`;234CREATEDATABASEIFNOTEXISTS`tmp`;567USEtmp;8910CREATETABLEIFNOTEXISTS`dlt2023`(11`uid`INTNOTNU......
  • php_mysql: PHP 格式化输出数据
    php_mysql: PHP格式化输出数据    一、php源程序1[wit@fedoramysql]$catphp_mysql2#!/usr/bin/php345<?php678910//filename=php_mysql1112131415functiontest()16{17echo"hello,php!\n";18}1......
  • PostgreSQL 和 MySQL 技术全面剖析
    本文介绍MySQL和PostgreSQL的一些特性对比,让大家了解二者的优劣,更好的做出选择。当前国内的现状,互联网公司使用MySQL的较多,PostgreSQL的使用比例反而不高,但相信看到PG的新特性后,你会爱上她。当然MySQL作为最流行的数据库,依然会吸引大部分人的眼球。PostgreSQL标榜自己是世界上最......
  • mysql函数常见字符串函数
    1、BIT_LENGTH返回值为二进制的字符串str长度。--格式:BIT_LENGTH(str)selectBIT_LENGTH('abc'); 2、CONCAT返回结果为连接参数产生的字符串。--格式:concat(str1,str2,…)selectCONCAT('a','b','c')   3、ELT假设n等于1,用这个n去跟后一个数比较,如果n大......