Spring Boot中H2内存数据库SQL脚本插入失效求助

在Spring Boot项目中，通过resource目录下的SQL脚本初始化H2内存数据库，启动日志显示脚本已执行，所有INSERT语句返回更新计数1，但数据库始终为空，仅能通过H2控制台手动插入数据，以下是具体排查方向：

排查方向 #

• 确认H2连接URL一致性
  检查application.properties/yaml中配置的JDBC URL与H2控制台使用的URL是否完全一致。内存模式下，不同URL对应独立的数据库实例，比如jdbc:h2:mem:testdb和jdbc:h2:mem:test是两个不同的内存库，如果脚本初始化的实例和控制台连接的实例不匹配，自然看不到数据。

• 验证事务提交状态
  Spring Boot默认会提交初始化脚本的事务，但如果存在自定义事务配置（如全局事务管理器设置默认回滚），可能导致插入的数据被回滚。可以在脚本末尾添加COMMIT;强制提交，或检查项目中是否有影响初始化脚本事务的配置。

• 调整脚本命名与加载顺序
  Spring Boot默认优先加载schema.sql（建表），再加载data.sql（插数据）。如果将建表和插数据都放在schema.sql中，可能因加载时机问题导致数据未持久化。建议拆分脚本：建表语句放schema.sql，插入语句单独放data.sql。

• 检查H2连接参数
  在JDBC URL中添加;AUTOCOMMIT=TRUE参数（如jdbc:h2:mem:testdb;AUTOCOMMIT=TRUE），强制开启自动提交。同时确认事务隔离级别设置为READ_COMMITTED等合适级别。

• 修正JSON字段格式
  脚本中JSON字段使用了转义双引号["Spring",...]，H2支持标准JSON格式，直接使用["Spring", "Summer"]即可，转义字符可能导致JSON格式不合法，虽日志返回更新计数1但数据未正确写入。

• 核对Spring Boot初始化配置
  确认spring.sql.init.mode设置为always或embedded（默认仅嵌入式数据库执行），若设为never会跳过脚本执行。同时将spring.sql.init.continue-on-error暂时设为false，排查是否有脚本执行错误被掩盖。

提供的SQL脚本 #

DROP TABLE IF EXISTS BACKPACK_DATA;
CREATE TABLE BACKPACK_DATA(id int PRIMARY KEY AUTO_INCREMENT,
                           name VARCHAR(250),
                           quantity INT,
                           kilometers INT,
                           season JSON
);

INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('backpack', 1, 1, '["Spring", "Summer", "Autumn", "Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('pair of boots', 1, 1, '["Spring", "Summer", "Autumn", "Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('pair of boots', 2, 50, '["Spring", "Summer", "Autumn", "Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('sunscreen', 1, 20, '["Summer"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('umbrella', 1, 15, '["Spring", "Autumn"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('gloves', 1, 1, '["Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('snacks', 3, 15, '["Spring", "Summer", "Autumn", "Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('snacks', 6, 30, '["Spring", "Summer", "Autumn", "Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('raincoat', 1, 50, '["Spring", "Autumn"]');

启动日志 #

Executing SQL script from file [backpack-api\build\resources\main\schema.sql]
2024-03-01T18:05:29.984+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: DROP TABLE IF EXISTS BACKPACK_DATA
2024-03-01T18:05:29.988+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE TABLE BACKPACK_DATA( id int PRIMARY KEY AUTO_INCREMENT, name VARCHAR(250), quantity INT, kilometers INT, season JSON )
2024-03-01T18:05:29.991+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('backpack', 1, 1, '["Spring", "Summer", "Autumn", "Winter"]')
2024-03-01T18:05:29.991+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('pair of boots', 1, 1, '["Spring", "Summer", "Autumn", "Winter"]')
2024-03-01T18:05:29.992+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('pair of boots', 2, 50, '["Spring", "Summer", "Autumn", "Winter"]')
2024-03-01T18:05:29.992+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('sunscreen', 1, 20, '["Summer"]')
2024-03-01T18:05:29.993+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('umbrella', 1, 15, '["Spring", "Autumn"]')
2024-03-01T18:05:29.993+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('gloves', 1, 1, '["Winter"]')
2024-03-01T18:05:29.993+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('snacks', 3, 15, '["Spring", "Summer", "Autumn", "Winter"]')
2024-03-01T18:05:29.993+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('snacks', 6, 30, '["Spring", "Summer", "Autumn", "Winter"]')
2024-03-01T18:05:29.994+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('raincoat', 1, 50, '["Spring", "Autumn"]')
2024-03-01T18:05:29.994+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from file [backpack-api\build\resources\main\schema.sql] in 14 ms.

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