Mysql常见问题处理集锦

- root用户密码忘记，重置的操作(windows上的操作)
- MySQL报错：ERROR 1118 (42000): Row size too large. 或者 Row size too large (＞ 8126).
- - 场景：
  - 报错原因
  - 解决办法
- 详解
- - 行大小限制示例：
  - - - 内容来源于网络，主要用于知识分享，若侵权，请联系博主，会及时删除

root用户密码忘记，重置的操作(windows上的操作)

1: 通过任务管理器或者服务管理，关掉mysqld(服务进程)
2: 通过命令行+特殊参数开启

mysqld mysqld --
defaults-file="D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini" --skip-grant-tables

3: 此时，mysqld服务进程已经打开。并且不需要权限检查
4: 另启动一个客户端进行

mysql -uroot //无密码登陆服务器。

5: 修改权限表

 use mysql; 
 update user set authentication_string=password('新密码') where user='root' and Host='localhost'; 
 flush privileges;

6: 通过任务管理器，关掉mysqld服务进程。
7: 再次通过服务管理，打开mysql服务。
8: 即可用修改后的新密码登陆。

MySQL报错：ERROR 1118 (42000): Row size too large. 或者 Row size too large (＞ 8126).

场景：

今天拿到一个建语句时，大概二百多个字段，然后大部分类型是 string 的，要求建 MySQL 的表。首先将 string 替换为 varchar(xx)，然后执行了一下语句，报错如下所示：

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.

报错原因

MySQL 建表时有一个单行最大限制长度限定：一张表中所有字段设置的字节数总和不大于 65535 字节。

注意点一：单个字段大小超过 65535 转换为 TEXT。
注意点二：其余字段总和不超过 65535 字节（不包括 BLOB/TEXT）。
注意点三：数据库使用 UTF-8 编码，一个字符 = 三个字节大小（使用编码不同，字节数大小略有不同）。
报错举例：数据库存在 10 个 varchar 字段，每个大小为 3000 则数据库单行目前计算长度为 3000 * 10 * 3 = 90000 > 65535 ，则建表时就会报错。

解决办法

将数据库表大字段类型设置为 TEXT，或者将部分可以减小长度的长度调小至总和小于 65535。

调整完之后，执行建表语句，又报如下错误：

1. 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.（低版本报错）

2. Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. （新版本报错）

上面两个错误信息，前者基本出现在 ROW_FORMAT <> DYNAMIC 或者较早版本的情况，在其中 BLOB 需要存储 768 字节在行内部。对整个行 size 贡献较大。

后者基本对于较新的版本，因为默认的 ROW_FORMAT = DYNAMIC，在其中，一个 TEXT 或者 BLOB 字段对行 size 的贡献在 9-12 个字节之间。

对于第一种情况，可以设置 innodb_file_per_table = 1，innodb_default_row_format = dynamic，又因为 dynamic 要求 innodb_file_format 必须为 Barracuda，所以一般还要加上 innodb_file_format = Barracuda 设置。innodb_default_row_format = dynamic 可以在创建表的时候动态指定。当然也可以按照提示那样的，设置 ROW_FORMAT =COMPRESSED，这个对于只读场景用处比较大，如果用于读写负载，那比较不好。

在数据库执行如下语句：

SHOW GLOBAL VARIABLES LIKE '%innodb_file%';

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table	   | ON        |
+--------------------------+-----------+

确保 innodb_file_format 使用的是 Barracuda，innodb_file_per_table 使用的是 ON，如果不是执行以下语句（不用重启 MySQL）：

SET GLOBAL innodb_file_format = barracuda;
SET GLOBAL innodb_file_per_table = ON;

或者，在配置文件中添加使用独立表空间的配置：innodb_file_per_table=1
修改配置文件 my.cnf（需要重启 MySQL）：

innodb_file_per_table = ON;
innodb_file_format = barracuda;

或者，建表语句设置 ROW_FORMAT =COMPRESSED：

create table_name (
...
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FROMAT=COMPRESSED COMMENT='表注释';

如果上面的方法仍然解决不了问题，那还有其它办法：

1、关掉 innodb_strict_mode，这个选项是在创建表的时候检查行大小，如果确定实际存储的字段没有这么多，可以关掉。但是问题是如果确实有这么多内容，插入的时候会报错。
查看：

SHOW VARIABLES LIKE '%innodb_strict_mode%';

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_strict_mode	   | ON        |
+--------------------------+-----------+

修改：

SET SESSION innodb_strict_mode = OFF
或者
SET innodb_strict_mode = OFF

以上 OFF 也可以用 0 代替，ON 也可以用 1 代替。

2、将 innodb_page_size 调整成 64K，这样，64K 的 page 即使需要容纳2行数据的话，每行也可以最大达到 32K(实际达不到，因为 header 和 footer 需要空间)。但是这个最好把现有的 MySQL 备份出来，然后按照新的 page size 重新初始化，再导入备份，保证整个库都使用统一的 page size 大小，以免出现稀奇古怪的问题。

set global innodb_page_size = 65536

3、可以看到上面的提示 In current row format, BLOB prefix of 0 bytes is stored inline，把较长的字段都转成 TEXT 或者 BLOB 存储。

以上步骤可以一个一个试，基本就可以解决自己的问题了。

详解

ERROR1118 的报错信息分为两种：

1. 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.

一行最大记录长度是 65535（定义到这个长度也会报错，行本身维护也会占用字节），建议使用 TEXT 或 BLOBs 类型。

2. Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

一条记录太长，超过了 8126 字节，建议部分列使用 TEXT 或 BLOB 类型。

看到这两个报错信息，有些人可能就会有疑问，感觉描述的有些冲突，一个说一条记录最大长度不超过 65535 字节，一个说长度不能超过 8126 字节。那么到底哪个是正确的的呢？
先看下官方文档的描述：

【The MySQL maximum row size limit of 65,535 bytes is demonstrated in the following InnoDB and MyISAM examples. The limit is enforced regardless of storage engine, even though the storage engine may be capable of supporting larger rows.】

在 MySQL 数据库中一条记录的最大长度是 65535 字节，在下面的 InnoDB 和 MyISAM 示例中做了演示。尽管存储引擎可能能够支持更大的行，但无论使用何种存储引擎，都会强制执行该限制。

【InnoDB restricts row size (for data stored locally within the database page) to slightly less than half a database page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings, and to slightly less than 16KB for 64KB pages.】

nnoDB 限制一条记录的大小(对于本地存储在数据库页面中的数据)，对于 4KB、8KB、16KB 和 32KB 的 innodb_page_size 设置为略小于数据库页面的一半，对于 64KB 的页面则略小于 16KB。

小结：
一条记录最大长度 65535 字节是 MySQL 数据库 Server 层面的限制，默认情况下，InnoDB 页面大小是 16KB，所以一条记录在页面中的存储长度不能超过 8126 字节，这是 InnoDB 存储引擎的限制。

这里可能会有些疑问，平常创建 varchar(10000) 类型字段，已经超过 8126 了，但也没报这个错误，这个和 InnoDB 的存储一条记录的格式有关系，官方文档对存储格式的说明：

【Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page】

当列的长度超过 768 字节时，多余的内容会存储到一个溢出页上，compact/dynamic 格式在这方面是一样的。
也就是说创建了 varhcar(10000) 类型字段，同时写入到 10000 字节的数据，其实只有 768 个字节存储在数据页面上，其余的字节存储在溢出页面上。

为什么Innodb存储引擎，每个存储页面上，最少要有两条记录呢？

截图来自于【MySQL运维内参】

在这里插入图片描述

这是假如每个页面只能存储一条记录的情况下，表内存储了【1，2，3，4】4 条记录 B+ 树结构图，如果一个页面的数据量不能存储 2 条记录，则这个 B+ 树就不能称为 B+ 树，因为它起不到一个索引的作用，其实就是一个双向链表，但比双向链表占用的空间大很多。
如果不能够存储 2 条记录，那么这个 B+ 树是没有意义的，形不成一个有效的索引。

总结：
创建表和写入数据时有两个限制，一个是 Server 层面的限制，一条记录最大长度不能超过 65535（真实创建的记录长度到不了 65535，因为记录本身也需要一些字节去维护），另一个是 InnoDB 层面的限制，一条记录存储在页面中的长度不能够超过 8126 字节。

行大小限制示例：

1、在 MySQL 数据库中一条记录的最大长度是 65535 字节，在下面的 InnoDB 和 MyISAM 示例中做了演示。尽管存储引擎可能能够支持更大的行，但无论使用何种存储引擎，都会强制执行该限制。

创建一个表 t，记录长度之合超过 65535。默认字符集是 latin1，一个字符占一个字节，如果用的 utf8，则一个字符占用 3 个字节。要在定义的 varchar 字段类型上乘以 3 才是占用字节数（不同字符集所占字节数可能不同）。

InnoDB 示例：

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

或者 MYISAM

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=MyISAM 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

在下面的 MyISAM 示例中，将列更改为 TEXT 避免了 65535 字节的行大小限制，所以就操作成功了，因为 BLOB 和 TEXT 列只贡献了 9-12 个字节的行大小（6 * 10000 + 9 ~ 6 * 10000 + 12）。

mysql> CREATE TABLE t2 (
       a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)
       ) ENGINE=MyISAM CHARACTER SET latin1;
       
Query OK, 0 rows affected (0.02 sec)

对于下面 InnoDB 表，操作成功，是因为将列更改为 TEXT 可避免 MySQL中 65535 字节的行大小限制，而 InnoDB 变长列的分页存储可避免 InnoDB 行大小限制（< 8126 的限制）。

看 t2 表，varchar 类型是 10000，记录最长是 5 * 10000 = 50000 字节，再加上TEXT 字段对行 size 贡献的 9-12 个字节，没有达到 server 层面的限制。
对于可变长字段，数值超过 768 个字节的，字段的前 768 字节存储在 InnoDB 页面上，其余的数据存储在溢出页面上。t2 表一共 7 个字段，每个字段只有前边的 768 字节存储在 InnoDB 页面上，7 * 768 = 5376 字节，没有达到 InnoDB 存储引擎 8126 的限制，不会报错，所以创建成功。

mysql> CREATE TABLE t2 (
       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)

可变长度列的存储包括长度字节（值的长度），它被计入行大小。例如，VARCHAR（255）CHARACTER SET utf8mb3 列需要两个字节来存储值的长度，因此每个值最多占用 767 个字节。如下示例：

mysql> CREATE TABLE t3 (
       c1 VARCHAR(32765) NOT NULL, 
       c2 VARCHAR(32766) NOT NULL
       ) ENGINE = InnoDB CHARACTER SET latin1;
       
Query OK, 0 rows affected (0.02 sec)

上面创建表 t3 的语句成功，因为列需要 32765 + 2 字节和 32766 + 2 字节，这在 65535 字节的最大行大小范围内。

mysql> CREATE TABLE t3 (
       c1 VARCHAR(65535) NOT NULL
       ) 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

上面创建表 t3 的语句失败，因为尽管列长度在 65535 字节的最大长度内，但需要另外两个字节来记录长度，这会导致行大小超过 65535 个字节。

2、InnoDB 限制一条记录的大小(对于本地存储在数据库页面中的数据)，对于 4KB、8KB、16KB 和 32KB 的 innodb_page_size 设置为略小于数据库页面的一半，对于 64KB 的页面则略小于 16KB。

示例：

创建表 t4，使用 char(255) 定长字符串类型，char 类型无论写入的内容多少（当然，一定要小于等于 255），在实际存储时都会占用 255 个字节。一共33字段，每个字段定长 255 字节，33 * 255 = 8415，每个记录最大长度是 8145 字节，是 Server 层的限制之内，所以没报 65535 的错误，但一条记录在 InnoDB 页面存储时超过了 8126 限制，所以 InnoDB 存储引擎报错了。如下所示：

mysql> CREATE TABLE t4 (
       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),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET latin1;
       
ERROR 1118 (42000): 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.

创建表 t4 的语句失败，因为定义的列超过了 16KB InnoDB 页面的行大小限制。

修改 t4 表为 varhcar(255)，可变长字段试下，varchar 字段类型在实际存储到页面的时候，并不以定长存储，而是写入多少字节，存储多少字节。如下所示，可以看到，这样创建表是没有问题，如果写入字节数小于 8126 字节也没有问题，但是如果写入字节数超过 8126 了，由于 InnoDB 存储引擎的限制，还是会报错的。

mysql>  CREATE TABLE t4 (
        c1 VARCHAR(255),c2 VARCHAR(255),c3 VARCHAR(255),
        c4 VARCHAR(255),c5 VARCHAR(255),c6 VARCHAR(255),
        c7 VARCHAR(255),c8 VARCHAR(255),c9 VARCHAR(255),
        c10 VARCHAR(255),c11 VARCHAR(255),c12 VARCHAR(255),
        c13 VARCHAR(255),c14 VARCHAR(255),c15 VARCHAR(255),
        c16 VARCHAR(255),c17 VARCHAR(255),c18 VARCHAR(255),
        c19 VARCHAR(255),c20 VARCHAR(255),c21 VARCHAR(255),
        c22 VARCHAR(255),c23 VARCHAR(255),c24 VARCHAR(255),
        c25 VARCHAR(255),c26 VARCHAR(255),c27 VARCHAR(255),
        c28 VARCHAR(255),c29 VARCHAR(255),c30 VARCHAR(255),
        c31 VARCHAR(255),c32 VARCHAR(255),c33 VARCHAR(255)
        ) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET latin1;
    
Query OK, 0 rows affected (0.01 sec)

写入数据长度小于 8126 的场景，240 * 33 = 7920，可以成功。如下所示：

mysql> insert into t4 (
       c1,c2,c3,c4,c5,c6,c7,c8,
       c9,c10,c11,c12,c13,c14,c15,c16,
       c17,c18,c19,c20,c21,c22,c23,c24,
       c25,c26,c27,c28,c29,c30,c31,c32,c33
       ) values(
       repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240), 
       repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240), 
       repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240), 
       repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240)
       )
       
Query OK, 1 row affected (0.01 sec)

写入数据长度大于 8126 的场景，255 * 33 = 8415，直接报错。如下所示：

mysql> insert into t4 (
       c1,c2,c3,c4,c5,c6,c7,c8,
       c9,c10,c11,c12,c13,c14,c15,c16,
       c17,c18,c19,c20,c21,c22,c23,c24,
       c25,c26,c27,c28,c29,c30,c31,c32,c33
       ) values(
       repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255), 
       repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255), 
       repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255), 
       repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255)
       )

ERROR 1118 (42000): 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.
mysql>