Mysql数据库EFCore中文字段按照拼音排序问题

最新推荐文章于 2024-07-09 11:22:52 发布

doudou478

最新推荐文章于 2024-07-09 11:22:52 发布

阅读量170

点赞数 2

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/u014550902/article/details/138913115

版权

前提条件

1.mysql数据库

2.EFcore code first

解决问题

1.解决对于中文字段无法按照拼音排序

解决方法

1.修改数据库

可以直接在数据库中修改

ALTER TABLE 库名.表名
MODIFY COLUMN 列名 VARCHAR(255) CHARACTER SET GBK COLLATE gbk_chinese_ci ;

2.在code中修改（第一次创建表）

efcore只能指定列的数据类型，但是无法直接指定列的编码格式，只可以通过修改或者增加自定义迁移操作来实现，修改编码格式为gbk，排序方式为gbk_chinese_ci。

Name = table.Column<string>(type: "varchar(255)", nullable: true,collation: "gbk_chinese_ci"
.Annotation("MySql:CharSet", "gbk")

 public partial class ini : Migration
 {
     /// <inheritdoc />
     protected override void Up(MigrationBuilder migrationBuilder)
     {
         migrationBuilder.AlterDatabase()
             .Annotation("MySql:CharSet", "utf8mb4");

         migrationBuilder.CreateTable(
             name: "表名",
             columns: table => new
             {
                 Id = table.Column<Guid>(type: "char(36)", nullable: false, collation: "ascii_general_ci"),
                
                 Name = table.Column<string>(type: "varchar(255)", nullable: true,collation: "gbk_chinese_ci"
                     .Annotation("MySql:CharSet", "gbk"), 
             },

     }

     /// <inheritdoc />
     protected override void Down(MigrationBuilder migrationBuilder)
     {
         migrationBuilder.DropTable(
             name: "表名");
     }
 }

3.在code中修改（已经创建表）

如果已经创建过表了，则修改下CharSet和collation

 public partial class updateUnicode : Migration
 {
     /// <inheritdoc />
     protected override void Up(MigrationBuilder migrationBuilder)
     {
         migrationBuilder.AlterColumn<string>(
             name: "列名",
             table: "表名",
             type: "varchar(255)",
             nullable: true,
             collation: "gbk_chinese_ci",
             oldClrType: typeof(string),
             oldType: "longtext",
             oldNullable: true,
             oldCollation: "utf8mb4_0900_ai_ci")
             .Annotation("MySql:CharSet", "gbk")
             .OldAnnotation("MySql:CharSet", "utf8mb4");
     }

     /// <inheritdoc />
     protected override void Down(MigrationBuilder migrationBuilder)
     {
        
         migrationBuilder.AlterColumn<string>(
             name: "列名",
             table: "表名",
             type: "varchar(255)",
             nullable: true,
             collation: "utf8mb4_0900_ai_ci",
             oldClrType: typeof(string),
             oldType: "varchar(255)",
             oldNullable: true,
             oldCollation: "gbk_chinese_ci")
             .Annotation("MySql:CharSet", "utf8mb4")
             .OldAnnotation("MySql:CharSet", "gbk");
     }
 }