问题链接
https://segmentfault.com/q/1010000019472412
MySQL 前缀索引大小限制
是否支持前缀索引以及前缀索引长度大小，依赖于数据表使用的存储引擎。
对于INNODB存储引擎而言，默认前缀长度最大能支持767字节；而在开启innodb_large_prefix属性值的情况下，最大能支持3072字节。
对于MyISAM存储引擎而言，前缀长度限制为1000字节。
对于NDB存储引擎而言，并不支持前缀索引。
SELECT
post.id,
post.title,
post.content,
GROUP_CONCAT( NAME ) AS tags
FROM
post,
tag,
p_t
WHERE
p_t.post_id = post.id
AND p_t.id = p_t.tag_id
GROUP BY
id;
建表语句
/*
Navicat Premium Data Transfer
Source Server : rasp4
Source Server Type : MariaDB
Source Server Version : 100325
Source Host : localhost:3306
Source Schema : test
Target Server Type : MariaDB
Target Server Version : 100325
File Encoding : 65001
Date: 01/12/2020 15:32:13
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for p_t
-- ----------------------------
DROP TABLE IF EXISTS `p_t`;
CREATE TABLE `p_t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`post_id` int(11) NOT NULL,
`tag_id` int(11) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of p_t
-- ----------------------------
BEGIN;
INSERT INTO `p_t` VALUES (1, 1, 1);
INSERT INTO `p_t` VALUES (2, 2, 2);
COMMIT;
-- ----------------------------
-- Table structure for post
-- ----------------------------
DROP TABLE IF EXISTS `post`;
CREATE TABLE `post` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`content` text COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of post
-- ----------------------------
BEGIN;
INSERT INTO `post` VALUES (1, '111', '1111');
INSERT INTO `post` VALUES (2, '222', '2222');
INSERT INTO `post` VALUES (3, '333', '3333');
COMMIT;
-- ----------------------------
-- Table structure for tag
-- ----------------------------
DROP TABLE IF EXISTS `tag`;
CREATE TABLE `tag` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of tag
-- ----------------------------
BEGIN;
INSERT INTO `tag` VALUES (1, 'nodejs', 1);
INSERT INTO `tag` VALUES (2, 'php', 1);
INSERT INTO `tag` VALUES (3, 'python', 1);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;