在Spring Boot中加载Schema.SQL文件时获取SQL语法错误

Question

原因：org.springframework.beans.factory.beanCreationException：创建类路径资源[org/springframework/boot/autoconfigure/jdbc/datasourceConfiguration$hikari.class]中定义的名为“data source”的bean时出错：bean初始化失败；嵌套异常为org.springframework.beans.factory.beanCreationException：创建名为“org.springframework.boot.autocigure.jdbc.DataSourceInitializerInvoker”的bean时出错：调用init方法失败；嵌套异常为org.springframework.jdbc.datasource.init.scriptstatementfailedexception：无法执行URL的SQL脚本语句#1[file:/users/schachte/documents/habicus-core/build/resources/main/schema.SQL]:CREATE TABLEgoal_metrics（；嵌套异常为org.h2.jdbc.jdbcsqlexception：SQL语句“CREATE TABLE”“goal_metrics”“([*]”；应为“

CREATE TABLE `goal_metrics` (
  `goal_metrics_id` int(11) NOT NULL,
  `goal_complete` varchar(45) DEFAULT NULL,
  `goal_in_progress` varchar(45) DEFAULT NULL,
  `money_made_on_goal` varchar(45) DEFAULT NULL,
  `time_until_due_date` decimal(10,0) DEFAULT NULL,
  `goals_goal_id` int(11) NOT NULL,
  PRIMARY KEY (`goal_metrics_id`,`goals_goal_id`),
  KEY `fk_goal_metrics_goals1_idx` (`goals_goal_id`),
  CONSTRAINT `fk_goal_metrics_goals1` FOREIGN KEY (`goals_goal_id`) REFERENCES `goals` (`goal_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `metrics` (
  `goalsInProgress` int(11) NOT NULL,
  PRIMARY KEY (`goalsInProgress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `dob` datetime DEFAULT NULL,
  `gender` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

我遇到了同样的问题，只是意识到h2db不喜欢你使用保留字。不要使用保留字段命名数据库或表，甚至字段：

• 组
• 内存
• 选择
• 授权

...等等...