Zero. Background of the problem

When you first learned SpringBoot + Angular, the database software still used MySQL, except that this time the MySQL service was no longer built using XAMPP, but a more convenient Docker was used to provide the service.

1. Reproduction of Problems

When you configure your database, after you've set up your environment with Docker, start building your first data table

Then create a new query according to the query statement in the tutorial
(Tutorial address: https://www.kancloud.cn/yunzh...)

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '' COMMENT 'Full name',
  `sex` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0 Male, 1 female',
  `username` varchar(255) NOT NULL COMMENT 'User name',
  `email` varchar(255) DEFAULT '' COMMENT 'mailbox',
  `create_time` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Creation Time',
  `update_time` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Update Time',
  PRIMARY KEY (`id`),
  UNIQUE KEY `nx1HkMqiUveGnJz5lHE7mEcFI5WVew3iXbv3HCwF` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of teacher
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES (1, 'Zhang San', 1, 'zhangsan', 'zhangsan@mail.com', 1569721598000, 1569721598000);
INSERT INTO `teacher` VALUES (2, 'Li Si', 0, 'lisi', 'lisi@yunzhi.club', 1569721598000, 1569721598000);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Copy Code to Query Window

The following prompts appear:
1071 - Specified key was too long; max key length is 767 bytes, Time: 0.001000s

Traditionally, errors are translated first
The error message is: "The specified keyword is too long; the maximum keyword length is 767 bytes"

2. Causes

After reading the material, I found in a blog:

If the system variable innodb_large_prefix is enabled (MySQL 5.6.41, turned off by default, and MySQL 5.7 turned on by default), the index key prefix is limited to 3072 bytes for InnoDB tables using DYNAMIC or COMPRESSED row formats.If innodb_large_prefix is disabled, the index key prefix is limited to 767 bytes for any table in row format.Attempting to use an index key prefix length that exceeds the limit returns an error.
Note: 767 bytes above, not characters, depending on the number of characters, depending on the character set.GBK is double-byte, UTF-8 is three-byte
(Cited from: https://www.cnblogs.com/kerry...)

The reason for this is very good. The data table in the tutorial is utf8mb4, which is four bytes. Looking at the longest field in the data table, it is 255, 255 * 4 = 1020 > 767, so the error will occur.

  `username` varchar(255) NOT NULL COMMENT 'User name',
  `email` varchar(255) DEFAULT '' COMMENT 'mailbox',

3. Solutions

1. Reduce field length

Once you understand the principles, the first thing you think about is trying to reduce the field length to verify the correctness of the theory.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT '' COMMENT 'Full name',
  `sex` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0 Male, 1 female',
  `username` varchar(128) NOT NULL COMMENT 'User name',
  `email` varchar(128) DEFAULT '' COMMENT 'mailbox',
  `create_time` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Creation Time',
  `update_time` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Update Time',
  PRIMARY KEY (`id`),
  UNIQUE KEY `nx1HkMqiUveGnJz5lHE7mEcFI5WVew3iXbv3HCwF` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of teacher
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES (1, 'Zhang San', 1, 'zhangsan', 'zhangsan@mail.com', 1569721598000, 1569721598000);
INSERT INTO `teacher` VALUES (2, 'Li Si', 0, 'lisi', 'lisi@yunzhi.club', 1569721598000, 1569721598000);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Halve all 255 to 128, 128 * 4 = 512 < 767
After running the query statement again, it passed smoothly.

2. Upgrade MySQL version to above 5.7

I ran this query statement on MySQL version 5.6, 5.7, and Latest, respectively.This problem has been tested for both version 5.7 and Latest, indicating that innodb_large_prefix is indeed turned on by default for versions above 5.7.

3. Manually open innodb_large_prefix

The following conditions need to be met (reference) https://www.cnblogs.com/kerry...):
1: The system variable innodb_large_prefix is ON
2: The system variable innodb_file_format is Barracuda
3:ROW_FORMAT is DYNAMIC or COMPRESSED

4. Summary

1. Version

It is mentioned in the tutorial that MySQL uses versions 5.6 and 5.7 without affecting you. In fact, the problem found this time shows that there are many differences between different versions of MySQL.

2. XAMPP built-in MySQL and MariDB

So, when you used XAMPP before, you should use MySQL 5.6. Why didn't this error occur?

Looking at the XAMPP website, I suddenly found that the integration of XAMPP is not MySQL, but a branch of it - MariaDB!

But what's written in the new version of the software is MySQL?
With a questionable attitude, I opened the XAMPP console and entered mysql on the command line

The result confirms that MariaDB did change!.