NestJS TypeORM开启synchronize后表已存在启动报错，求解决方案

同时开启synchronize: true和autoLoadEntities: true时，若之前已创建过表，执行npm run start会抛出QueryFailedError，目前只能删除所有表才能启动，求无需删表即可正常启动的方法。

错误信息 #

error: Error: text type with an unknown/unsupported collation cannot be hashed
    at Packet.asError (/Users/myname/Documents/works/ski-backend/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Query.execute (/Users/myname/Documents/works/ski-backend/node_modules/mysql2/lib/commands/command.js:29:26)
    at PoolConnection.handlePacket (/Users/myname/Documents/works/ski-backend/node_modules/mysql2/lib/connection.js:456:32)
    at PacketParser.onPacket (/Users/myname/Documents/works/ski-backend/node_modules/mysql2/lib/connection.js:85:12)
    at PacketParser.executeStart (/Users/myname/Documents/works/ski-backend/node_modules/mysql2/lib/packet_parser.js:75:16)
    at TLSSocket.<anonymous> (/Users/myname/Documents/works/ski-backend/node_modules/mysql2/lib/connection.js:360:25)
    at TLSSocket.emit (node:events:390:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at TLSSocket.Readable.push (node:internal/streams/readable:228:10) {
  code: 'ER_INTERNAL_ERROR',
  errno: 1815,
  sqlState: 'HY000',
  sqlMessage: 'text type with an unknown/unsupported collation cannot be hashed',
  sql: "SELECT `TABLE_SCHEMA`, `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'staging-myservice' AND `TABLE_NAME` = 'user' UNION SELECT `TABLE_SCHEMA`, `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'staging-myservice' AND `TABLE_NAME` = 'car'"
}

相关代码 #

app.module.ts #

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      url: 'mysql://<username>:<password>@<host>/<database>?ssl={"rejectUnauthorized":true}',
      synchronize: true,
      autoLoadEntities: true,
      logging: true,
      ssl: {
        rejectUnauthorized: true,
      },
    }),
  ]
})

user.entity.ts #

import { Column, PrimaryGeneratedColumn, Entity } from 'typeorm';
import { Field, ObjectType } from '@nestjs/graphql';

@Entity()
@ObjectType()
export class User {
  @PrimaryGeneratedColumn()
  @Field()
  id: number;

  @Column()
  @Field()
  name: string;
}

car.entity.ts #

import { Column, PrimaryGeneratedColumn, Entity } from 'typeorm';
import { Field, ObjectType } from '@nestjs/graphql';

@Entity()
@ObjectType()
export class Car {
  @PrimaryGeneratedColumn()
  @Field()
  id: number;

  @Column()
  @Field()
  name: string;
}

已尝试方案 #

• 显式指定表名：@Entity({ name: 'user' })
• 显式指定public schema：@Entity({ name: 'user', schema: 'public' })
• 显式禁用migrationsRun：migrationsRun: false
• 删除dist/目录
• 替换mysql2为mysql模块

解决方法 #

这个错误的核心是数据库中已存在的表使用了TypeORM无法识别或不支持的字符集排序规则，导致同步时哈希计算失败。可通过以下步骤解决：

1. 给实体字段显式指定字符集和排序规则
   在实体的@Column装饰器中明确设置字符集和排序规则，确保与数据库默认配置匹配，示例：

@Column({
  charset: 'utf8mb4',
  collation: 'utf8mb4_unicode_ci'
})
@Field()
name: string;

根据数据库实际使用的字符集调整参数，MySQL 8.0+推荐使用utf8mb4及对应排序规则。

2. 全局配置TypeORM连接的默认字符集
   在TypeOrmModule的配置中添加全局字符集和排序规则选项：

TypeOrmModule.forRoot({
  type: 'mysql',
  url: 'mysql://<username>:<password>@<host>/<database>?ssl={"rejectUnauthorized":true}',
  synchronize: true,
  autoLoadEntities: true,
  logging: true,
  ssl: {
    rejectUnauthorized: true,
  },
  charset: 'utf8mb4',
  collation: 'utf8mb4_unicode_ci'
})

3. 手动修复现有表的排序规则
   直接在数据库中执行SQL修改已有表的字符集和排序规则：

ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE car CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

执行完成后再启动项目即可。

4. 检查数据库服务端字符集配置
   确认MySQL服务端的默认字符集配置是否支持，执行以下SQL查看：

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

如果配置了TypeORM不支持的特殊排序规则，需要调整数据库的默认配置。

内容的提问来源于stack exchange，提问作者kusumoto_teruya