在JPA项目启动时新增MySQL字段

前言

  本来用了JPA，直接实体类加参数就可以新增字段了，但是架不住垃圾项目在启动项目时会加载数据库SQL文件去插入数据，那没办法了，只能再加一些弱智操作去修复一些弱智操作。

起因

  项目启动的时候会先执行schema.sql，再执行data.sql，然后再执行JPA建表新增字段等操作。然而在新版本中在配置表中加了某个字段，假设为status，并且在data.sql中对status进行了赋值操作。此时旧版本升级新版本，启动时执行了data.sql文件，就会报错，报错信息，配置如下：

java.sql.SQLSyntaxErrorException: Unknown column 'status' in 'field list'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2958)
	at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2473)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2956)
	at com.alibaba.druid.wall.WallFilter.statement_execute(WallFilter.java:415)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2956)
	at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2473)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2956)
	at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.execute(StatementProxyImpl.java:147)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

配置：

spring:
  datasource:
    # 工程启动加载数据库结构sql脚本
    schema:
    - classpath:/mysql/schema.sql
    # 工程启动加载数据库默认数据sql脚本
    data:
    - classpath:/mysql/data.sql
1
2
3
4
5
6
7
8

解决方案

  如果要解决这个问题，那么就需要在 schema.sql 中新增字段，下面是几种解决方案：

方案1

方案说明

  在 schema.sql 删除这个表，然后重新建表的时候带上要新增的字段

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `your_table_name`;
CREATE TABLE `hxj_report_template_index` (
     `ID` bigint(20) NOT NULL AUTO_INCREMENT,
     `your_column_name` varchar(255) DEFAULT NULL,
     PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=1;
1
2
3
4
5
6
7
8

应用场景

  该方案简单粗暴，仅限于这个表存的数据一直不变，不会被更新。

可能出现的问题

  如果该表被修改了，删除再新增初始化会回到初始状态。

方案2

方案说明

  在 schema.sql 判断该字段是否存在，不存在则新增：

-- 替换 table_name 和 column_name 为实际的表名和列名
SET @table_name = 'your_table';
SET @column_name = 'new_column';
SET @column_definition = 'VARCHAR(255)';  -- 替换为你需要的列定义

-- 查询列是否存在
SELECT COUNT(*)
INTO @column_exists
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name = @table_name
AND column_name = @column_name;

-- 根据查询结果决定是否新增列
SET @sql = IF(@column_exists = 0,
              CONCAT('ALTER TABLE ', @table_name, ' ADD COLUMN ', @column_name, ' ', @column_definition),
              'SELECT "Column already exists"');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

应用场景

  没遇到不适用的，评论区补充吧

可能出现的问题

  如果配置了阿里 Durid，那么Druid 连接池的 SQL 防火墙机制会阻止动态 SQL 语句 PREPARE stmt FROM @sql 的执行。Druid 的 SQL 防火墙严格限制某些类型的 SQL 语句，以防止 SQL 注入攻击。错误如下（本来是一行的，我加下换行方便查看）：

org.springframework.beans.factory.UnsatisfiedDependencyException: 
Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration': 
Unsatisfied dependency expressed through constructor parameter 0; 
nested exception is org.springframework.beans.factory.BeanCreationException: 
Error creating bean with name 'druidDataSource' defined in class path resource [com/xxx/config/DruidConfig.class]: Initialization of bean failed; 
nested exception is org.springframework.beans.factory.BeanCreationException: 
Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': 
Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: 
Failed to execute SQL script statement #26 of class path resource [mysql/schema.sql]: PREPARE stmt FROM @sql; 
nested exception is java.sql.SQLException: sql injection violation, class com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlPrepareStatement not allow : PREPARE stmt FROM @sql
1
2
3
4
5
6
7
8
9
10

  主要看最后一行，如果确实需要使用 PREPARE 语句，可以考虑在 Druid 配置中放宽对该语句的限制。但这可能会带来安全风险，因此需要谨慎处理。

spring:
  datasource:
    druid:
      wall:
        config:
          update-allow: true
          delete-allow: true
          insert-allow: true
          select-allow: true
          truncate-allow: true
          replace-allow: true
          drop-allow: true
          create-table-allow: true
          alter-table-allow: true
          prepared-stmt-allow: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

方案3

方案说明

  搜索引擎结果表示在 MySQL8 有直接的语句可以判断字段是否存在并新增

ALTER TABLE `your_table_name` ADD COLUMN IF NOT EXISTS `your_column_name` varchar(50);
1

应用场景

  适用于 MySQL8.0 以上版本（我在5.7.26和8.0.34版本都试过，直接报错不知道什么原因）

可能出现的问题

  可能的问题就是我试过我不行，没时间去看为啥不行了，大佬可直接评论区说一下，谢谢

总结

  我用的方案1，如果你也是这种项目，那么我也推荐你用方案1，简单粗暴又方便；方案2是问了chatgpt得出来的答案，感觉不错，但是报错了又不想改；但是你需要知道为何会出现这种问题，以及如何避免出现这种问题，以下是我的理解：

1. 不建议使用 schema.sql 和 data.sql 对数据库结构及数据进行初始化，因为在每一次项目启动，这两个文件都必须执行一次，大大减慢了项目的启动速度，替代方案可以是使用 flyway 进行数据库版本控制。
2. JPA 真的不是初学者快捷构建项目的首选，如果没有对 JPA 有正确的理解，在遇到复杂场景时会手足无措，这边还是建议使用 Mybatis 捏，初学者多写写 SQL 语句有助于进步捏，高手另说哈。

感谢您的阅读