SpringBoot连接MySQL报Unknown column field in list错误排查与解决

Hey there, let's break down this issue you're facing with your Spring Boot REST API.

The Unknown column 'xxx' in 'field list' error is 100% a mapping mismatch between your JPA entity class and the actual database table. It has little to do with your application.properties (unless you're connecting to the wrong database, but you mentioned you checked that, so we can rule that out first). Common triggers include:

• A field in your Vendor.java doesn't have a matching column in your database table (e.g., entity uses vendorName but the table has vendor_name, and camel-case to underscore conversion isn't enabled)
• You added a new field to the Vendor entity but forgot to add the corresponding column to the database table
• You used the @Column annotation with a column name that doesn't exist in the table
• If Vendor inherits from a parent class, the parent's fields might not have matching columns in the vendor table

1. Double-Check Entity-to-Table Field Mapping #

First, pull up your Vendor.java and list out all fields, then cross-reference with your database table:

@Entity
public class Vendor {
    @Id
    private Long id;
    private String vendorName; // Verify this matches your table's column name
    private String contactEmail;
    // Getters/Setters...
}

• If your table uses snake_case (like vendor_name) but your entity uses camelCase (vendorName), add this line to application.properties to enable automatic conversion:

  spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy
  

2. Validate @Column Annotations #

If you're explicitly specifying column names with @Column, make sure the name value matches exactly what's in the database:

// Wrong: if your table doesn't have a column named "vendor_full_name"
@Column(name = "vendor_full_name")
private String vendorName;

// Correct: match the actual column name in your table
@Column(name = "vendor_name")
private String vendorName;

3. Sync Database Table Structure #

If you recently added a new field to Vendor.java, you need to update the database table:

• Manual approach: Run an ALTER TABLE command in your database client, e.g.:

  ALTER TABLE vendor ADD COLUMN contact_email VARCHAR(255);
  

• Auto-sync (dev environment only): Enable Hibernate's auto-ddl feature in application.properties to let it update the table automatically:

  spring.jpa.hibernate.ddl-auto=update
  

  Note: Never use this in production—always use database migrations like Flyway or Liquibase for production environments.

4. Check for Inheritance Issues #

If Vendor extends a base class (e.g., BaseEntity with common fields like createdAt), ensure:

• The base class is annotated with @MappedSuperclass so its fields are mapped to the vendor table
• The corresponding columns exist in the vendor table

5. Inspect the Generated SQL #

To pinpoint exactly which column is missing, enable SQL logging in application.properties:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Restart your app, hit the http://localhost:8089/demo/all endpoint, and check the console. You'll see the full SELECT statement Hibernate is trying to run—copy that into your database client and execute it directly. It will immediately show you which column doesn't exist.

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