mysql最熟悉又最陌生的几个问题

varchar(n) 最多能储存多少数据？

以Compact 行格式来举例：假设使用ascii字符编码集，那么按我们都知道的来说最大占用就是65535个字符（ascii一个字节一个字符）。创建一个表演示：

CREATE TABLE test(
       c  VARCHAR(65535)
  ) CHARSET=ascii ROW_FORMAT=Compact;
  ---------------------------------------------------------------------------------------------
  [SQL]CREATE TABLE test(
       c  VARCHAR(65535)
  ) CHARSET=ascii ROW_FORMAT=Compact;
[Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

可以看到报错了，Row size too large。也就是超过了存放的最大限制。实际上mysql在储存一行数据的时候还有其他的一些头信息。具体如下：

真实数据
真实数据占用字节的长度
null值标识，如果该列有not null属性则可以没有这部分存储空间

上面的表的字段因为可以为空，所以null标识会占用一个字节，再加上实际数据的长度可能占用2个字节，也就是最终改字段的容量最大设置上限为65535-2-1

[SQL]CREATE TABLE test(
       c  VARCHAR(65532)
  ) CHARSET=ascii ROW_FORMAT=Compact;
受影响的行: 0
时间: 0.060s

如果该表的字段设置非空，则没有null标识最大上限则变为65533

[SQL]CREATE TABLE test(
       c  VARCHAR(65533) not null
  ) CHARSET=ascii ROW_FORMAT=Compact;
受影响的行: 0
时间: 0.065s

那么正常来说我们的表的字符编码不会选择ascii,一般会是utf8mb4,那么上限又是多少呢？

同样使用上述例子，减去真实数据长度占用字节，无null字段标识。

[SQL]CREATE TABLE test(
       c  VARCHAR(65533) not null
  ) CHARSET=utf8mb4 ROW_FORMAT=Compact;
[Err] 1074 - Column length too big for column 'c' (max = 16383); use BLOB or TEXT instead

可以看到报错了，上限为16383，那么这个数据怎么来的呢？utf8mb4占用的字节为1-4个字节，65533/4 = 16383.25 取整也就是16384。由此我们可以看到储存字符上限是由字符编码的最大占用字节所决定的。

InnoDB表对主键生成策略

优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为DB_ROW_ID的隐藏列作为主键。一般而言：InnoDB存储引擎会为每条记录都添加 DB_TRX_ID 和 DB_ROLL_PTR 这两个列，但是 DB_ROW_ID 是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）。最后这些列不用我们去管了，因为InnoDB存储引擎会自己帮我们生成。

列名	描述
DB_ROW_ID	行ID，唯一标识一条记录
DB_TRX_ID	事务ID
DB_ROLL_PTR	回滚指针

mysql行格式

查看当前表的行格式（Row_format）：

show TABLE STATUS LIKE '表名'

COMPACT,Redundant,Dynamic和Compressed行格式。后面两种是5.7版本的行格式。5.6默认Compressed行格式。后两种行格式类似于COMPACT行格式，具体的不再详细说。

char(n) 类型有什么特殊？

char(n)类型，当该字段所使用的字符集是ascii编码时属于定长字段，此时char(10)占用的字节数就是10个，但是当字符集是utf8时或者utf8mb4时就属于变长字段了，占用字节数分别是10-30或10-40个（原因：utf8一个字符占用字节为1-3个字节，utf8mb4占用字节1-4个字节）。
另外有一点还需要注意，变长字符集的char(n)类型的列要求至少占用n个字节，而varchar(n)却没有这个要求。举个例子：utf8mb4字符集的char(10)的字段来说，该列存储的数据字节长度的范围是10～40个字节。即使我们向该列中存储一个空字符串也会占用10个字节，原因♥：后期更新该字段的值时，如果字节长度大于原来值的字节长度而小于10个字节时，可以在该记录处直接更新，而不会在存储空间中重新分配一个新的记录空间,从而导致大家所知道的char(n)浪费空间这个说法.

注意：

上述讨论的是在Compact行格式中。在这种行格式中，char(n)类型的列中存储数据分变长字符集和定长字符集的情况定长按实际，变长按最小（上述举例就是如此）,而在Redundant行格式中，不管该列使用的字符集是哪种，只要是使用char(n)类型，那占用的真实数据空间就是该字符集表示一个字符最多需要的字节数和n的乘积。举例：该字段或者表使用utf8字符集时，某个字段使用char(5)类型,最终的数据真实占用空间始终为5*3=15个字节，使用ascii字符集的char(5)类型的列占用的真实数据空间始终为5个字节
原文：技术分享