牛刀小试MySQL学习-String Types

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Storage Requirements for String TypesData TypeStorage RequiredCHAR(M)M × w bytes, 0  M  255, where w is the number of bytes required ...
Storage Requirements for String Types
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 8
BLOB, TEXT L + 2 bytes, where L 16
MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L 24
LONGBLOB, LONGTEXT L + 4 bytes, where L 32
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)

char&varchar:

一个是定长字符类型,一个是可变长字符类型。char(M)(0~255),存储需求:M*w bytes.意思就是说:这个w嘛,它还得根据字符集,例如latin字符集

是1字节,gb2312是2字节,utf8是三字节。所以,create table t3(name char(255)) character set utf8;这name列存储的应该是255*3的字节。


varchar则是可变存储数据(0~65535),可以节省存储空间。但它有个前缀长度1字节,这个基本上可以猜测它用来定义可以长度的标识量。因为1字节=>8bit=>256

所以,varchar超过255字节后,前缀长度就得使用两个字节了。

以下表格,是官方给出的char和varchar对应的存储字节。

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

char(4)就一直是固定长度

varchar(4)就是可变长度,前面有1字节的前缀长度,超过255字节,就是2字节的前缀长度


性能的比较:由于,char是固定长度的,所以是用空间来换取时间,所以char的查询速度是比varchar的查询速度要快的。

但是对于Innodb存储引擎来说:内部的行存储就本身没有区分固定长度和可变长度列(数据行是使用了头指针,来指向数据列值的),所以这里

使用varchar更好,性能也不比char差


BINARY&VARBINARY:

BINARY和VARBINARY类似于CHAR和VARCHAR,不同的是她们包含二进制字符串而不包含非二进制字符串。

mysql> create table t(C binary(3));

Query OK, 0 rows affected (0.15 sec)


mysql> insert into t values('a');

Query OK, 1 row affected (0.05 sec)


mysql> select * from t;

+------+

| C    |

+------+

| a    |

+------+

1 row in set (0.00 sec)


mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from t;

+------+--------+-------+---------+-----------+

| C    | hex(c) | c='a' | c='a\0' | c='a\0\0' |

+------+--------+-------+---------+-----------+

| a    | 610000 |     0 |       0 |         1 |

+------+--------+-------+---------+-----------+

1 row in set (0.02 sec)

从上面可以看出,它在保存a值的时候,会在最后默认填充'0x00'(零字节),所以c='a\0\0' boolean返回的值就是1了。


TEXT&BLOB

TEXT保存较大的文本,BLOB保存二进制数据。存储的形式上面的表格有描述。

BLOB和TEXT的数据在被删除时,会引起"空洞"的记录。但是innodb可以阻止这个情况的发生

mysql> create table t8(id int,context text);

mysql> insert into t10 values(1,repeat('zsdzsd',100));(重复插入到,有几十万条数据)

mysql> insert into t10 select * from t10;

查看ibd

[root@localhost test]# du -sh t10.*

12K     t10.frm

269M    t10.ibd

和ibdata1

[root@localhost innodb_ts]# du -sh ./*

2.1G    ./ibdata1

再把数据删除。

mysql> delete from t10 where id = 1;

Query OK, 131072 rows affected (37.98 sec)

再次查看ibd和ibdata1,发现大小没变,这里就出现了空洞


在innodb存储引擎中,是无法使用optimize的,需要使用recreate和analyze代替。

mysql> optimize table t10;

+----------+----------+----------+-------------------------------------------------------------------+

| Table    | Op       | Msg_type | Msg_text                                                          |

+----------+----------+----------+-------------------------------------------------------------------+

| test.t10 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |

| test.t10 | optimize | status   | OK                                                                |

+----------+----------+----------+-------------------------------------------------------------------+

2 rows in set (2 min 36.33 sec)

虽然这样看起来,像是造成了空洞,但是如果你在插入一张表

mysql> create table t11(name varchar(20));

Query OK, 0 rows affected (0.08 sec)


mysql> insert into t11 values(repeat('zsd',10));

Query OK, 1 row affected, 1 warning (0.04 sec)


mysql> insert into t11 select * from t11;(继续重复插入50w条数据)

Query OK, 1 row affected (0.04 sec)

Records: 1  Duplicates: 0  Warnings: 0

再去查看系统层面的大小:(发现,数据已经被释放了,t11正好用上了t10释放的空间,但是ibddata还是那么大)

[root@localhost innodb_ts]# du -sh ./*

2.1G    ./ibdata1

[root@localhost test]# du -sh t11.*

12K     t11.frm

61M     t11.ibd

[root@localhost test]# du -sh t10.*

12K     t10.frm

181M    t10.ibd

关于排序的问题:TEXT和BLOB排序的时候,会用上临时表,这样对性能来说是有下降的。

使用合成的(Synthetic)索引,可以提高大文本字段的查询性能,适合精确查询,不适合模糊查询。

create table t12(id int,context text,hash_value varchar(40));

insert into t12 values(1,  repeat ('zsd',2),md5(context));

insert into t12 values(2,  repeat ('zsd',3),md5(context));

insert into t12 values(3,  repeat ('zsd2008',3),md5(context));

mysql> select * from t12;

+------+-----------------------+----------------------------------+

| id   | context               | hash_value                       |

+------+-----------------------+----------------------------------+

|    1 | zsdzsd                | 0a8a135e9ae7d104ae7a81f4733b66cd |

|    2 | zsdzsdzsd             | 912d5bffb559146700698871e7730447 |

|    3 | zsd2008zsd2008zsd2008 | 93d60b10af275780ff14ce2de0e20445 |

+------+-----------------------+----------------------------------+

3 rows in set (0.07 sec)

mysql> select * from t12 where hash_value=md5('zsdzsd');

+------+---------+----------------------------------+

| id   | context | hash_value                       |

+------+---------+----------------------------------+

|    1 | zsdzsd  | 0a8a135e9ae7d104ae7a81f4733b66cd |

+------+---------+----------------------------------+

1 row in set (0.00 sec)


ENUM&SET

ENUM:对1~255各成员,存储一个字节,对于255~65535个成员,存储两个字节。

SET:1~8个成员:占1字节

          9~16成员:占2字节

          17~24成员:占3个字节

          25~32成员:占4个字节

          33~64成员:占8个字节

   

例子:create table t13(gender enum('M','F'));

           insert into t13 values('M'),('1'),('f'),(NULL);其他的字会默认变为M,NULL值还是NULL,忽略大小写

      

           create table t14(col set('a','b','c','d'));

           insert into t14 values('a,b'),('a,b,a'),('a,b'),('a,c'),('a'),('a,b,c,d');(就是可以组合自由搭配的存储到表中)

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
72 3
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
165 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
springboot学习五:springboot整合Mybatis 连接 mysql数据库
这篇文章是关于如何使用Spring Boot整合MyBatis来连接MySQL数据库,并进行基本的增删改查操作的教程。
768 0
springboot学习五:springboot整合Mybatis 连接 mysql数据库
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
这篇文章是关于如何使用Spring Boot框架通过JdbcTemplate操作MySQL数据库的教程。
255 0
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
k8s学习--利用helm部署应用mysql,加深helm的理解
k8s学习--利用helm部署应用mysql,加深helm的理解
481 0
学习MySQL操作的有效方法
学习MySQL操作的有效方法
70 3
如何学习 MySQL?
如何学习 MySQL?
58 3
学习mysql基础操作
【8月更文挑战第20天】学习mysql基础操作
51 1

热门文章

最新文章