猫宁海亚
Spring Boot 自动执行SQL创建表
Haiya Lv3
  2025-04-11 15:18:00   2025-10-22 16:26:01

本文中以 Spring Boot + PostgreSQL 为例，其他数据库类似

环境准备

使用 docker-compose.yml 本地启动：PostgreSQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
services:
  pgsql:
    container_name: postgres
    # image: postgres:17.4
    image: m.daocloud.io/docker.io/library/postgres:17.4
    restart: always
    environment:
      POSTGRES_PASSWORD: postgres
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  pgdata:

Spring SQL init

Spring SQL init 是 Spring Boot 提供的自动执行 SQL 语句的方案。

⚠️ 特别提醒

  1. 该方法仅适用于新建表
    如果数据库中已经存在对应的表，但 SQL 文件中的表结构发生了改动，则需要在数据库中删除对应表后重启服务；
  2. Spring Boot 版本要求：2.5.0+

准备建表语句

此处以 user 表为例，在 src/main/resources 下新建 schema.sql

1
2
3
4
5
6
7
8
9
10
CREATE SCHEMA IF NOT EXISTS mydb;

CREATE TABLE IF NOT EXISTS mydb.user (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

配置 SQL init mode

在 application.yml 中配置如下内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
spring:
  datasource:
    # 数据库连接地址
    url: jdbc:postgresql://127.0.0.1:5432/postgres?currentSchema=mydb&TimeZone=Asia/Shanghai
    # 用户名
    username: postgres
    # 密码
    password: postgres
    driver-class-name: org.postgresql.Driver
  # 配置SQL init
  sql:
    init:
      mode: always
      schema-locations: classpath:schema.sql

启动项目即可

Spring Flyway

Spring Flyway 是 Spring Boot 提供的另一种自动执行 SQL 语句的方案，其原理与 Spring SQL init 类似，但功能更强大，支持版本管理。

💬 该方式会在数据库中新建一个名为 flyway_schema_history 的表

准备建表语句

创建目录：src/main/resources/db/migration，用于存放数据库迁移文件。
还是以创建用户表为例
在该目录下创建 V1__create_user_table.sql，文件内容如下

1
2
3
4
5
6
7
8
9
10
CREATE SCHEMA IF NOT EXISTS mydb;

CREATE TABLE IF NOT EXISTS mydb.user (
  id BIGSERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

💬 SQL 脚本命名规则

  1. 脚本命名规则：V{version}__{description}.sql
  • 例如：V1__create_user_table.sql
  • 版本号必须是数字，可以包含小数点
  • 两个下划线是必须的
  1. 当需要修改表结构时，只需要创建新的迁移脚本，比如：
  • V2__add_user_status.sql
  • V3__add_user_phone.sql
  1. Flyway 会：
  • 自动检测新的迁移脚本
  • 按版本顺序执行
  • 记录执行历史
  • 确保每个脚本只执行一次
  1. 如果执行失败：
  • 会记录错误信息
  • 不会继续执行后续脚本
  • 需要手动修复问题后重新执行

添加依赖

注意：可能需要单独引入 spring-boot-starter-jdbc 或 spring-boot-starter-data-jpa

pom.xml

1
2
3
4
5
6
7
8
9
10
<!-- Flyway -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

添加配置文件

application.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
spring:
  datasource:
    url: jdbc:postgresql://127.0.0.1:5432/postgres?currentSchema=mydb&TimeZone=Asia/Shanghai
    username: postgres
    password: postgres
    driver-class-name: org.postgresql.Driver
  # 添加flyway配置
  flyway:
    enabled: true
    baseline-on-migrate: true
    locations: classpath:db/migration
    schemas: mydb
    table: flyway_schema_history

启动日志

添加 V1__create_user_table.sql 后，首次启动日志如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
2025-04-11T15:11:33.672+08:00  INFO 48096 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2025-04-11T15:11:34.195+08:00  INFO 48096 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@4aaecabd
2025-04-11T15:11:34.196+08:00  INFO 48096 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2025-04-11T15:11:34.324+08:00  INFO 48096 --- [           main] org.flywaydb.core.FlywayExecutor         : Database: jdbc:postgresql://127.0.0.1:5432/postgres (PostgreSQL 17.4)
2025-04-11T15:11:34.568+08:00  WARN 48096 --- [           main] o.f.c.internal.database.base.Database    : Flyway upgrade recommended: PostgreSQL 17.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 16.
2025-04-11T15:11:34.719+08:00  INFO 48096 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Schema history table "mydb"."flyway_schema_history" does not exist yet
2025-04-11T15:11:34.768+08:00  INFO 48096 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.149s)
2025-04-11T15:11:35.201+08:00  INFO 48096 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table "mydb"."flyway_schema_history" ...
2025-04-11T15:11:35.920+08:00  INFO 48096 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "mydb": << Empty Schema >>
2025-04-11T15:11:35.967+08:00  INFO 48096 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "mydb" to version "1 - create user table"
2025-04-11T15:11:36.064+08:00  WARN 48096 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: schema "mydb" already exists, skipping (SQL State: 42P06 - Error Code: 0)
2025-04-11T15:11:36.802+08:00  INFO 48096 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema "mydb", now at version v1 (execution time 00:00.192s)
2025-04-11T15:11:37.174+08:00  INFO 48096 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port 8080 (http) with context path '/'

添加 V2__add_user_status_and_phone.sql，文件内容及启动日志如下：

1
2
3
4
5
6
7
8
9
10
11
-- 添加状态字段
ALTER TABLE mydb.user ADD COLUMN IF NOT EXISTS status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE';

-- 添加手机号字段
ALTER TABLE mydb.user ADD COLUMN IF NOT EXISTS phone VARCHAR(20);

-- 添加手机号唯一索引
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_phone ON mydb.user(phone) WHERE phone IS NOT NULL;

-- 添加状态索引
CREATE INDEX IF NOT EXISTS idx_user_status ON mydb.user(status);
1
2
3
4
5
6
7
8
9
10
2025-04-11T15:14:01.238+08:00  INFO 48561 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2025-04-11T15:14:01.760+08:00  INFO 48561 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@2407a36c
2025-04-11T15:14:01.761+08:00  INFO 48561 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2025-04-11T15:14:01.888+08:00  INFO 48561 --- [           main] org.flywaydb.core.FlywayExecutor         : Database: jdbc:postgresql://127.0.0.1:5432/postgres (PostgreSQL 17.4)
2025-04-11T15:14:02.133+08:00  WARN 48561 --- [           main] o.f.c.internal.database.base.Database    : Flyway upgrade recommended: PostgreSQL 17.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 16.
2025-04-11T15:14:02.402+08:00  INFO 48561 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 2 migrations (execution time 00:00.215s)
2025-04-11T15:14:02.940+08:00  INFO 48561 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "mydb": 1
2025-04-11T15:14:02.996+08:00  INFO 48561 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "mydb" to version "2 - add user status and phone"
2025-04-11T15:14:03.903+08:00  INFO 48561 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema "mydb", now at version v2 (execution time 00:00.293s)
2025-04-11T15:14:04.246+08:00  INFO 48561 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port 8080 (http) with context path '/'
  1. 1. 环境准备
  2. 2. Spring SQL init
    1. 2.1. 准备建表语句
    2. 2.2. 配置 SQL init mode
  3. 3. Spring Flyway
    1. 3.1. 准备建表语句
    2. 3.2. 添加依赖
    3. 3.3. 添加配置文件
    4. 3.4. 启动日志
© 2023 - 2025    Haiya
由 Hexo 驱动 & 主题 Keep
  1. 1. 环境准备
  2. 2. Spring SQL init
    1. 2.1. 准备建表语句
    2. 2.2. 配置 SQL init mode
  3. 3. Spring Flyway
    1. 3.1. 准备建表语句
    2. 3.2. 添加依赖
    3. 3.3. 添加配置文件
    4. 3.4. 启动日志