带有MySQL的.NET Core 2.0:指定的密钥太长；最大密钥长度为3072字节 [英] .NET Core 2.0 with MySQL: Specified key was too long; max key length is 3072 bytes

问题描述

我是一名PHP/MySQL开发人员，正在尝试移至其他技术(如.NET Core).我不确定我是否喜欢通过MySQL切换到SQL Server的想法(由于许可)，所以我一直在尝试使.NET Core与MySQL很好地兼容.

I'm a PHP/MySQL developer trying to move over to other technologies like .NET Core. I'm not sure if I like the idea of switching to SQL Server over MySQL (due to licensing), so I've been trying to get .NET Core to play nicely with MySQL.

我已经建立了一个相当基本的.NET Core 2.0项目，使用Identity作为身份验证方法，并且当数据库驱动程序是 SqlLite 和本地数据库时，一切工作正常.我尝试切换到 MySQL 数据库并运行了迁移，但失败了.

I've set up a fairly basic .NET Core 2.0 project using Identity as the authentication method and everything was working fine while the DB driver was SqlLite with a local database. I tried switching to a MySQL database and ran my migrations and they failed.

我收到错误:指定的密钥太长；最大密钥长度为3072字节尝试运行以下表创建查询时:

I received the error: Specified key was too long; max key length is 3072 bytes when trying to run the following table-creation query:

CREATE TABLE `AspNetUserTokens` (
    `UserId` varchar(767) NOT NULL,
    `LoginProvider` varchar(767) NOT NULL,
    `Name` varchar(767) NOT NULL,
    `Value` text NULL,
    PRIMARY KEY (`UserId`, `LoginProvider`, `Name`)
);

Mysql数据库使用字符集 UTF8mb4 ，因此它不能使用上面指定的主键，该主键的大小约为9204字节.

My Mysql database uses a character set of UTF8mb4 so it cannot use the primary key specified above which works out to be around 9204 bytes.

如何自定义默认身份表的创建方式?如果可能的话，我想用这张表上更合理的主键替换这个大键，该主键只是一个自动增量整数.

How can I customize the way that the default Identity tables are created? If possible, I would like to replace the huge key with a more sane primary key on this table that is just an auto increment integer.

有什么方法可以自定义默认身份相关表的创建方式，而无需手动修改迁移文件.有什么办法可以在我的 ApplicationDbContext 的 OnModelCreating 方法中做些什么?我应该停止与MySQL对抗并屈服于MS SQL吗?

Is there any way to customize how the default Identity related tables are created without manually hacking at the migration files. Is there any way I can do something in the OnModelCreating method of my ApplicationDbContext? Should I stop fighting with MySQL and succumb to MS SQL?

修改

我能够通过使用 Pomelo MySQL驱动程序而不是Oracle来解决此问题.Pomelo驱动程序似乎默认将字符串字段的最大长度限制为127个字符，从而使上述密钥适合3072个字节的限制.但是，我仍然想知道如何微调和自定义.NETCore脚手架中提供的基本Identity表.

I was able to workaround the issue by using the Pomelo MySQL Driver rather than Oracle. The Pomelo driver seems to default string fields to max length 127 characters which allows the above key to fit in the 3072 byte limit. However I would still be interested in knowing how to fine-tune and customize the base Identity tables provided in the .NETCore Scaffolding.

推荐答案

对于这些问题，我不建议您完全离开MySQL.一段时间以来，它一直是最常用的数据库.由于您已经花时间解决了这些问题并从中学习了经验，因此值得尝试完整的周期并获得完整的理解.

I wouldn't recommend leaving MySQL completely for these issues. It has been the most used DB for a while now. Since you have already spent time tackling these issues and learnt from them, it would be worth to try out complete cycles and get the complete understanding.

就使用Pomelo而言，您应该没事.我建议坚持.它确实在git和nuget上都有更多的下载，并且还具有更多的git star.Oracle的nuget，甚至其发行"版本仍未完成.据报道，它具有未实现的功能 https://bugs.mysql.com/bug.php?id = 82981 .如果MS在官方提供者旁边正式提到 Pomelo ，那确实应该是什么.

As far as you are using Pomelo, you should be fine. I would recommend sticking to that. It does have more downloads on both git and nuget and also has more git stars. Oracle's nuget, even its "release" version is still not completed. It was reported to have unimplemented functions https://bugs.mysql.com/bug.php?id=82981. If MS has officially mentioned Pomelo next to the official provider it should really be something.

关于主要问题，您应该可以在 OnModelCreate 中完成类似的操作:

Regarding your main issue, you should be able to do something like this in your OnModelCreate:

builder.Entity<ApplicationUser>()
    .Property(u => u.Id)
    .HasMaxLength(36);

请注意，根据 IdentityUser< TKey> 类， Id = Guid.NewGuid().ToString()，它仅需要36个字符.或者，您可以利用 Id 是虚拟字段这一事实，并在您的 ApplicationUser 类中进行此操作:

Note that according to the IdentityUser<TKey> class, Id = Guid.NewGuid().ToString(), it only needs 36 characters. Alternatively, you can advantage of the fact that Id is a virtual field and do this in your ApplicationUser class:

[MaxLength(36)]
public override string Id { get; set; }

此外，我并不是要与我所说的相抵触，但是对于某些项目来说，尝试使用SQL Server也没有害处.与MySQL相比，我确实在SQL Server中找到了一些便利.我不认为许可不是不尝试使用SQL Server的理由.在许多情况下，许可实际上并不重要有几个原因.

Also, I don't mean to contradict what I said, but no harm in trying SQL Server too for some projects. I did find couple of conveniences in SQL Server as compared to MySQL. I don't think licensing is a reason to not try SQL Server. There are several reasons why licensing doesn't really matter in many scenarios.

1. 您可以在Linux和Windows中免费托管多达10 GB的空间.在许多情况下，数据库需要10 GB.
2. "Web"版本是完全免费的，并且存储空间仅限于VM空间.至少天蓝色就是这种情况，您只需为服务付费.不确定其余的
3. 如果您尝试使用.NET，则主要由那些不介意许可的企业使用.如果您碰巧为他们工作或是自由职业者，则不必担心许可证问题.

这篇关于带有MySQL的.NET Core 2.0:指定的密钥太长；最大密钥长度为3072字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！