mysql键太长,如何设置更长的mysql主键，以便1600适合3072

I had an ef core migration fail with a key length too long targetting MySql.

The same migration is successful targetting SqlSever. How do I get around it in MySql?

When I apply the primary key directly using MySql Workbench, I get the same issue.

Both Key components are 767 long and the error claims that the key must be less than 3072 so not sure why I am seeing it. Online searches suggest that this is an encoding issue because on numbers alone a combined length of under 1600 should fit inside 3072. The setting in workbench advised where anything beyond key choice criticism is offered does not appear in my version.

How do I change the encoding so that a composite key of 1600 does not exceed the 3072 limit?

These are the column definitions:

LoginProvidervarchar(767)NO

ProviderKeyvarchar(767)NO

ProviderDisplayNametextYES

UserIdvarchar(767)NO

The combined key length is less than 1600 so should be comfortably inside the 3072 limit but I believe character encoding comes into play to take it beyond that. I cannot figure out how to get the migration to succeed.

Some background

This migration comes from Combined example on the IdentityServer website: here

This is the source code from within the EF Migration from my copy targetting MySQL:modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserLogin", b =>

{

b.Property("LoginProvider")

.HasMaxLength(767);

b.Property("ProviderKey")

.HasMaxLength(767);

b.Property("ProviderDisplayName");

b.Property("UserId")

.IsRequired();

//b.HasKey("LoginProvider", "ProviderKey");

b.HasIndex("UserId");

b.ToTable("AspNetUserLogins");

});

This is the corresponding code from the published example targetting SqlServer:

migrationBuilder.CreateTable(

name: "AspNetUserLogins",

columns: table => new

{

LoginProvider = table.Column(nullable: false),

ProviderKey = table.Column(nullable: false),

ProviderDisplayName = table.Column(nullable: true),

UserId = table.Column(nullable: false)

},

constraints: table =>

{

table.PrimaryKey("PK_AspNetUserLogins", x => new { x.LoginProvider, x.ProviderKey });

table.ForeignKey(

name: "FK_AspNetUserLogins_AspNetUsers_UserId",

column: x => x.UserId,

principalTable: "AspNetUsers",

principalColumn: "Id",

onDelete: ReferentialAction.Cascade);

});

Clearly different but having run it, I extracted the create statement from Sql Server Management Studio

/****** Object: Table [dbo].[AspNetUserLogins] Script Date: 16-Apr-19 6:42:24 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[AspNetUserLogins](

[LoginProvider] [nvarchar](450) NOT NULL,

[ProviderKey] [nvarchar](450) NOT NULL,

[ProviderDisplayName] [nvarchar](max) NULL,

[UserId] [nvarchar](450) NOT NULL,

CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY CLUSTERED

(

[LoginProvider] ASC,

[ProviderKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The key length sums to 900 in SqlServer, but the max I can get MySql to allow is 768.

That is before I even consider the migrations for the other two contexts in the example. They are a total failure under MySQL and right now all I can see myself doing is extracting the create statements and translating the SQL manually

What I have tried:

I have tried both an EF Core migration and directly through MySQL workbench:

Executing:

ALTER TABLE `identityserver_003`.`aspnetuserlogins`

ADD PRIMARY KEY (`LoginProvider`, `ProviderKey`);

;

ERROR 1071: Specified key was too long; max key length is 3072 bytes

SQL Statement:

ALTER TABLE `identityserver_003`.`aspnetuserlogins`

ADD PRIMARY KEY (`LoginProvider`, `ProviderKey`)

Operation failed: There was an error while applying the SQL script to the database.

I can only get past this by reducing the length of the two varchars to 384. The innodb_large_prefix system variable is not available so recommendations around changing it are not applicable.

This is my version info

innodb_version8.0.12

protocol_version10

slave_type_conversions

tls_versionTLSv1,TLSv1.1,TLSv1.2

version8.0.12

version_commentMySQL Community Server - GPL

version_compile_machinex86_64

version_compile_osWin64

version_compile_zlib1.2.11

解决方案This is a truly awful primary key. Why don't you just put a GUID somewhere and create this as an index?