如何修复1709 -索引列大小太大,最大列大小是767字节,在mysql中在XAMPP中

pcww981p  于 2023-01-08  发布在  Mysql
关注(0)|答案(1)|浏览(141)

我正在将一个新表作为Users导入到数据库中,但它显示错误

1709-索引列太大。最大列大小为767字节。

我的服务器版本:PHP版本号:10.1.16 7.2.14 phpmyAdmin版本:版本信息:4.8.4

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_show` tinyint(1) NOT NULL DEFAULT '0',
  `email_confirmed` tinyint(1) NOT NULL DEFAULT '0',
  `iso2` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0=> Not Specified,1=> Male, 2=> Female',
  `phone_show` tinyint(1) NOT NULL DEFAULT '0',
  `phone_confirmed` tinyint(1) NOT NULL DEFAULT '0',
  `country_id` int(10) UNSIGNED DEFAULT NULL,
  `state_id` int(10) UNSIGNED DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  `confirmation_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `updated_by` int(10) UNSIGNED DEFAULT NULL,
  `password` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` text COLLATE utf8mb4_unicode_ci,
  `confirmed` tinyint(1) NOT NULL DEFAULT '0',
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `phone_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provider` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provider_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `company_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `portfolio` text COLLATE utf8mb4_unicode_ci,
  `opening_time` time DEFAULT NULL,
  `closing_time` time DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `education` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notary_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `monday_opening_time` time DEFAULT NULL,
  `monday_closing_time` time DEFAULT NULL,
  `thuesday_opening_time` time DEFAULT NULL,
  `thuesday_closing_time` time DEFAULT NULL,
  `wednesday_opening_time` time DEFAULT NULL,
  `wednesday_closing_time` time DEFAULT NULL,
  `thursday_opening_time` time DEFAULT NULL,
  `thursday_closing_time` time DEFAULT NULL,
  `friday_opening_time` time DEFAULT NULL,
  `friday_closing_time` time DEFAULT NULL,
  `saturday_opening_time` time DEFAULT NULL,
  `saturday_closing_time` time DEFAULT NULL,
  `sunday_opening_time` time DEFAULT NULL,
  `sunday_closing_time` time DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_country_id_index` (`country_id`),
  KEY `users_area_id_index` (`state_id`),
  KEY `users_created_by_index` (`created_by`),
  KEY `users_updated_by_index` (`updated_by`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

MySQL说:

1709-索引列太大。最大列大小为767字节。

ryevplcw

ryevplcw1#

在创建表代码的最后一行,尝试更改以下内容:

ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

对此:

ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COLLATE=utf8mb4_unicode_ci;

相关问题