Spring Boot实现MySQL仅在表创建时执行一次性数据库初始化

Hey there! I totally get your frustration—having data.sql run every time your Spring Boot app starts can be a real pain when you only want initial data inserted once when the table is first created. Let's walk through a few solid solutions to fix this:

Solution 1: Use Spring Boot's Initialization Configs (For Simple Scenarios) #

Starting from Spring Boot 2.5.x, the framework provides more granular control over database initialization. You can tweak your config file (application.properties or application.yml) to only insert data when the table is first created:

First, make sure your table creation statement uses CREATE TABLE IF NOT EXISTS (so the table is only created if it doesn't exist already). Then add these configs:

application.properties Example: #

# Let the app continue even if initialization scripts throw errors (like duplicate data inserts)
spring.sql.init.continue-on-error=true
# Keep initialization mode as always, but pair it with conditional inserts
spring.sql.init.mode=always

Next, modify your data.sql to include a condition that only inserts data if it doesn't already exist:

-- Insert initial user data only if the admin user doesn't exist
INSERT INTO user (id, username, password)
SELECT 1, 'admin', 'encrypted_password'
WHERE NOT EXISTS (SELECT * FROM user WHERE id = 1);

On the first startup, the table will be created (if missing) and the data will insert. On subsequent startups, the WHERE NOT EXISTS clause will skip the insert since the data is already there—even though data.sql runs, no duplicate data is added.

For older Spring Boot versions (2.4 and below), use the legacy configs: spring.datasource.initialization-mode=always and spring.datasource.continue-on-error=true—the logic stays the same.

Solution 2: Use Database Migration Tools (Recommended For Production) #

If your app needs long-term database structure and data management, Flyway or Liquibase are the way to go. These tools handle versioned database changes, ensuring each script runs exactly once.

Let's Use Flyway As An Example: #

1. Add the Flyway dependency to your pom.xml (Maven):

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

For Gradle, add the corresponding dependency to your build file.

2. Create an initialization script in the src/main/resources/db/migration directory. Follow Flyway's naming convention: V1__create_user_table_and_insert_initial_data.sql

-- Create the user table if it doesn't exist
CREATE TABLE IF NOT EXISTS user (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL
);

-- Insert initial admin data (this runs only once)
INSERT INTO user (id, username, password) VALUES (1, 'admin', 'encrypted_password');

3. Spring Boot will automatically detect Flyway and run the script on first startup. It creates a flyway_schema_history table in your database to track executed scripts—so subsequent startups won't re-run the same script.

This approach is perfect for production because it lets you manage database changes version-by-version, making it easy to add new tables, modify schemas, or add more initial data later.

Solution 3: Custom Initialization Component (Full Control) #

If the above options don't fit your needs, you can build a custom component to check the table and data existence before inserting:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class DataInitializer implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void run(String... args) throws Exception {
        // Check if the user table exists (MySQL-specific query)
        Boolean tableExists = jdbcTemplate.queryForObject(
            "SELECT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user')",
            Boolean.class
        );

        if (tableExists) {
            // Check if the admin user already exists
            Integer adminCount = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM user WHERE id = 1",
                Integer.class
            );

            if (adminCount == 0) {
                // Insert the initial admin data
                jdbcTemplate.update(
                    "INSERT INTO user (id, username, password) VALUES (?, ?, ?)",
                    1, "admin", "encrypted_password"
                );
            }
        }
    }
}

This component runs right after your app starts. It first checks if the table exists, then verifies if the initial data is present—only inserting if both conditions are met. It's fully customizable to fit any edge case you might have.

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