今天做項(xiàng)目的時(shí)候，將本地的sql到出到服務(wù)器的mysql上錯(cuò)誤不斷送火，表導(dǎo)入成功拳话，有兩張表導(dǎo)入失敗，報(bào)一下錯(cuò)誤种吸！
Specified key was too long; max key length is 767 bytes
查閱資料后大概是原因
數(shù)據(jù)庫表采用utf8編碼弃衍，其中varchar(255)的column進(jìn)行了唯一鍵索引
而mysql默認(rèn)情況下單個(gè)列的索引不能超過767位(不同版本可能存在差異)
本人的sql語句如下
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`? (
? `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
? `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
? `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
? `mobile` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
? `email_verified_at` timestamp(0) NULL DEFAULT NULL,
? `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
? `remember_token` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
? `created_at` timestamp(0) NULL DEFAULT NULL,
? `updated_at` timestamp(0) NULL DEFAULT NULL,
? PRIMARY KEY (`id`) USING BTREE,
? UNIQUE INDEX `users_email_unique`(`email`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of users
-- ----------------------------
SET FOREIGN_KEY_CHECKS = 1;
分析
由于項(xiàng)目的用戶表采用數(shù)據(jù)庫遷移生成的，忘了對?`email``mobile`‘name’字段長度進(jìn)行限制坚俗，用戶表郵箱賬號(hào)名手機(jī)號(hào)均可以用來登錄服務(wù)器镜盯，均設(shè)置了唯一岸裙，mysql就會(huì)其設(shè)置索引，導(dǎo)致索引過長速缆，
解決辦法
1.合理分配唯一字段的長度降允，對?`email``mobile`‘name’字段長度減小調(diào)整為合適大小，避免浪費(fèi)以及不必要的問題出現(xiàn)艺糜！
2? 啟用innodb_large_prefix選項(xiàng)剧董，將約束項(xiàng)擴(kuò)展至3072byte；（不推薦倦踢，索引過長會(huì)影響查詢效率）
具體更改后的sql如下
CREATE TABLE `users` (
? `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
? `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
? `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
? `mobile` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
? `email_verified_at` timestamp(0) NULL DEFAULT NULL,
? `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
? `remember_token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
? `created_at` timestamp(0) NULL DEFAULT NULL,
? `updated_at` timestamp(0) NULL DEFAULT NULL,
? PRIMARY KEY (`id`) USING BTREE,
? UNIQUE INDEX `users_email_unique`(`email`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of users
-- ----------------------------
SET FOREIGN_KEY_CHECKS = 1;
