How to set charset when using dotnet ef migrations to create a database?

Question

I have set my Connection string to use Character Set=utf8mb4:

  "ConnectionStrings": {
    "MyDatabase": "User Id=root;Host=127.0.0.1;Port=3306;Database=my_database;Character Set=utf8mb4;Password=password;"
  }

I ran dotnet ef migrations add InitialCreate to create the initial migration with the database creation, but when I run dotnet ef database update the database is created with latin1 charset.

Answer 1

The character set specified in the DSN/connection string specifies the client character set, it will be used for conversion when sending data to and from the server.

To change the default character set of the server you have the following options:

• Specifying the character set when creating a database (or table): CREATE SCHEMA myschema DEFAULT CHARSET= utf8 or CREATE TABLE mytable (a varchar(100)) CHARSET=utf8

• Changing the server character set for the current session: SET session character_set_server=utf8

• Changing the server configuration by adding (or changing) the entry character-set-server=utf8 in your server configuration file.

SET global character_set_server=utf8 will work too, but after a server restart it will be set to the previous (default) value.

If you create a new table (without specifying the character set) the default character set from the database/schema will be used.