MySQL 学习笔记

MySQL是一个开源的关系型数据库,非常流行。

如无特殊说明,本专题文章的 SQL 均在 5.6 版本中验证。

👉文章列表
MySQL 学习笔记 ⏬ 创建和删除数据库 切换和查看数据库 创建和删除表 在表中增加、删除、修改列 添加和删除索引 增删查改 创建和删除主键 大小写和反引号 字符串类型值的大小写 SQL注释 CHAR类型 VARCHAR类型 整型数字 时间戳 不要使用utf8 唯一索引与NULL 唯一索引的单列长度限制 NULL的判等 DDL、DML、DCL的含义 查看表的状态 show table status InnoDB存储引擎的限制 if和case的使用 自定义函数 InnoDB 索引 事务 事务隔离级别的概念、查看和修改 读已提交(read committed)隔离级别下的锁 可重复读(repeatable read)隔离级别下的锁 修改自增主键id的类型 使用 load data 快速导入数据 使用 select into outfile 导出数据

MySQL: InnoDB存储引擎的限制


https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html 给出了 5.6 版本的限制。注意,不同版本 MySQL 的限制可能略有不同。

这里列出 InnoDB 存储引擎的部分限制:

  • 一张表最多可以有 1017 列。
  • 一张表最多64个二级索引。(说明:主键是一级索引,也可以叫做「聚集索引」;除了主键外的其他索引,是二级索引,或者说是「非聚集索引」。)
  • 对于string类型(如char、varchar、text等)的列,在用它做索引时,只会用字符串前面的部分做索引。
  • 对CHAR、 VARCHAR、BINARY 和 VARBINARY 类型的列,创建索引时候,可以指定用前面的多少字符做索引,也可以不指定,InnoDB会自动做限制(但会用尽可能多的字符做索引)。
  • 对 BLOB 和 TEXT 类型的列,必须指定用前面的多少字符做索引。
  • 一列能索引的字节不能超过 767 。若启用了innodb_large_prefix,一列能索引的字节不能超过 3072 。 innodb_large_prefix 默认未启用。
  • 所以,对于使用utf8mb4字符集的表,TEXT、VARCHAR、CHAR等类型的列,只能用前面的 191 个字符做索引,因为 191×4=764,192×4=768。
  • 页大小(page size)默认为16KB,对应的,一个索引(联合索引也算一个索引)最多支持 3072 个字节。页大小是在创建MySQL实例(可以理解为安装MySQL)时指定的。若页大小设置为8KB,则一个索引最多支持 1536 个字节;若设置为4KB,则一个索引最多支持 768 个字节。
  • 一个联合索引,最多可含有16列。
  • 行的大小(row size)不能超过 65535 字节。否则创建表的时候胡报错,例如:
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): 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

latin1 字符集中每个字符1字节。根据报错信息,可以看出 65535 的限制是在去除 TEXT、BLOB 之后。所以下面的不会报错:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)
  • 除去变长列(VARBINARY, VARCHAR, BLOB and TEXT)),行的长度(row length)应略小于页的大小的一半。也的默认大小是16KB。这个限制,在创建表的时候不会报错,在插入数据时可能报错,例如:
create table test_table (
    c1 CHAR(255),
        c2 CHAR(255),
        c3 CHAR(255),
        c4 CHAR(255),
        c5 CHAR(255),
        c6 CHAR(255),
        c7 CHAR(255),
        c8 CHAR(255),
        c9 CHAR(255),
        c10 CHAR(255),
        c11 CHAR(255),
        c12 CHAR(255),
        c13 CHAR(255),
        c14 CHAR(255)
) engine = InnoDB character set = utf8mb4;

表创建成功,使用 utf8mb4 字符集。

插入数据:

-- @you255 含有255个'你'
set @you255 = '你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你你'

insert into test_table (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14) values(
@you255, @you255, @you255, @you255, @you255, @you255, @you255, @you255, @you255, @you255, @you255, @you255, @you255, @you255
)

报错:

(1118, u'Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.')
  • SHOW TABLE STATUS给出的信息是不精确的。
  • 在 Windows 上,数据库名和表名,都以小写的形式存储。
  • 这些名称的大小写都不能当做列名,因为MySQL内部会用到:DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, DB_MIX_ID 。
  • 表内部不会存储有多少行数据,需要用SELECT COUNT(*)查询得到。
  • AUTO_INCREMENT 类型的列必须被索引。注意,主键也是一种索引。

( 本文完 )

文章目录