MySQL之char、varchar和text的设计

最近有表结构设计中出现了varchar(10000)的设计引起了大家的讨论,我们下面就来分析分析。

首先我们先普及一下常识:

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(n)后面的n非常大的时候我们是使用varchar好,还是text好呢?这是个明显的量变引发质变的问题。我们从2个方面考虑,第一是空间,第二是性能。

首先从空间方面:

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

    • 大于varchar(255)变为 tinytext
    • 大于varchar(500)变为 text
    • 大于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设置。

从索引上看其实long varchar和text也没有太多区别。

所以我们认为当超过255的长度之后,使用varchar和text没有本质区别,只需要考虑一下两个类型的特性即可。(主要考虑text没有默认值的问题)

CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `a` varchar(500) DEFAULT NULL,
  `b` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8

+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.01513200 | select a from test where id=10000 |
|        2 | 0.01384500 | select b from test where id=10000 |
|        3 | 0.01124300 | select a from test where id=15000 |
|        4 | 0.01971600 | select b from test where id=15000 |
+----------+------------+-----------------------------------+

从上面的简单测试看,基本上是没有什么区别的,但是个人推荐使用varchar(10000),毕竟这个还有截断,可以保证字段的最大值可控,如果使用text那么如果code有漏洞很有可能就写入数据库一个很大的内容,会造成风险。

故,本着short is better原则,还是使用varchar根据需求来限制最大上限最好。

附录:各个字段类型的存储需求

Data Type

Storage Required

TINYINT

1 byte

SMALLINT

2 bytes

MEDIUMINT

3 bytes

INT, INTEGER

4 bytes

BIGINT

8 bytes

FLOAT(p)

4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53

FLOAT

4 bytes

DOUBLE [PRECISION], REAL

8 bytes

DECIMAL(M,D), NUMERIC(M,D)

Varies; see following discussion

BIT(M)

approximately (M+7)/8 bytes

Data Type

Storage Required Before MySQL 5.6.4

Storage Required as of MySQL 5.6.4

YEAR

1 byte

1 byte

DATE

3 bytes

3 bytes

TIME

3 bytes

3 bytes + fractional seconds storage

DATETIME

8 bytes

5 bytes + fractional seconds storage

TIMESTAMP

4 bytes

4 bytes + fractional seconds storage

Data Type

Storage Required

CHAR(M)

M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set

BINARY(M)

M bytes, 0 <= M <= 255

VARCHAR(M), VARBINARY(M)

L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes

TINYBLOB, TINYTEXT

L + 1 bytes, where L < 28

BLOB, TEXT

L + 2 bytes, where L < 216

MEDIUMBLOB, MEDIUMTEXT

L + 3 bytes, where L < 224

LONGBLOB, LONGTEXT

L + 4 bytes, where L < 232

ENUM('value1','value2',...)

1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)

SET('value1','value2',...)

1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

oracle imp导入时出现skipping table

    最近有同事在使用传统的imp工具导入数据时,总是提示收到skipping table的提示,也就是表被跳过,而不是被重建。即使是将目标数据库上的表对象删...

14210
来自专栏Albert陈凯

2017年11月1日课后作业Hive 第二次课程DDL内部表、外部表、临时表的创建和特性DML

2017年11月1日课后作业 Hive 第二次课程 回顾上节课的内容 Hive是什么 SQL -> MapReduce 为什么会有Hive 给非Java编程者对...

34460
来自专栏Greenplum

Greenplum gpload命令使用

Runs a load job as defined in a YAML formatted control file.

48910
来自专栏乐沙弥的世界

NULL 值与索引(二)

    在NULL值与索引(一)中讲述了null值与索引的一些基本情况。其主要的内容为,基于允许存在null值的索引列,其索引值不会被存储;其次 是由于这个特...

11220
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(35)-文章发布系统②-构建项目

注:阅读本文,需要阅读本系列的之前文章 代码生成器下载地址(文章开头处)  接下来我们建立数据库的表和各层的代码 我们只需要两张表,文章列表(MIS_Artic...

26590
来自专栏数据之美

Hive 基础(2):库、表、字段、交互式查询的基本操作

1、命令行操作 (1)打印查询头,需要显示设置: set hive.cli.print.header=true; (2)加"--",其后的都被认为是注释...

738100
来自专栏SAP最佳业务实践

SAP S/4 HANA新变化-FI数据模型

With the installation of SAP Simple Finance, on-premise edition totals and appli...

45570
来自专栏乐沙弥的世界

高效SQL语句必杀技

        No SQL,No cost. SQL语句是造成数据库开销最大的部分。而不良SQL写法直接导致数据库系统性能下降的情形比比皆是。那么如何才能称得...

10020
来自专栏杨建荣的学习笔记

探索ASH 第一篇

老是在用ASH,对它的依赖感觉已经大于AWR,昨天心血来潮,想看看ash视图里面是怎么样的,过程也算曲折,不过也算抛砖引玉。 先看看v$active_sessi...

44980
来自专栏技术之路

使用Linq to Sql 创建数据库和表

1.建一个类Article 1 using System.Data.Linq.Mapping; 2 3 4 5 [Table(Name =...

20770

扫码关注云+社区

领取腾讯云代金券