Grails：在更改hibernate方言时SQL语法错误 [英] Grails: error in SQL syntax when changing the hibernate dialect

问题描述

我正在使用Grails和mySQL数据库，并试图更改数据库引擎。据我研究，这可以做到最好与

  dialect =org.hibernate.dialect。[MyDialect]
  

在DataSource.groovy配置中。但是，当我将方言设置为org.hibernate.dialect.MySQLMyISAMDialect时，表的创建失败，并显示错误消息：

lockquote

SQL语法;
对应于您的MySQL服务器版本的正确语法，以便在第1行使用
'type = MyISAM'附近

我也尝试稍后改变表格：

  sql.execute（ALTER TABLE book ENGINE = MYISAM;）
  

并且这个确实有效，但是在更改表格时会删除所有外键引擎在创建后。

我应该如何避免错误并正常更改引擎？

解决方案

你为什么要使用MyISAM？它不支持外键或交易。而且它比InnoDB更快，因为InnoDB使用行锁和MVCC，而不是MyISAM的全表锁。

话虽如此，你可以得到这个工作。您必须使用较新版本的MySQL，因为类型属性已被弃用一段时间，现在不支持 - 您必须使用 ENGINE 代替。没有支持这种方式的方言（请注意， org.hibernate.dialect.MySQL5InnoDBDialect 对InnoDB是正确的），因此您需要创建自己的。

在src / groovy或src / java中创建这个类（改变包和/或类名）：

  package com.mycompany.myapp 
 
 import org.hibernate.dialect.MySQLMyISAMDialect 
 
 class MySQL5MyISAMDialect extends MySQLMyISAMDialect {
 String getTableTypeString（）{
ENGINE = MyISAM

} 
  

并引用它在你的问题中显示的DataSource.groovy中：

  dialect = com.mycompany.myapp.MySQL5MyISAMDialect 
  code> 




I am using Grails with mySQL database and I am trying to change the database engine. As far as I researched this can be done best with

 dialect = "org.hibernate.dialect.[MyDialect]" 

in the DataSource.groovy config. But when I set the dialect to org.hibernate.dialect.MySQLMyISAMDialect creation of my table fails with error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'type=MyISAM' at line 1

I also tried to alter the table later with:

sql.execute("ALTER TABLE book ENGINE = MYISAM;")

and this actually works but all the foreign keys are dropped for the table when changing the engine after the creation.

How should I avoid the error and change the engine normally?

解决方案

Why would you want to use MyISAM??? It doesn't support foreign keys or transactions. And it's rarely faster than InnoDB since InnoDB uses row locks and MVCC, as opposed to MyISAM's full-table locks.

Having said that, you can get this to work. You must be using a newer version of MySQL since the type attribute was deprecated for a while and is now not supported - you have to use ENGINE instead. There's no dialect that supports this (note that org.hibernate.dialect.MySQL5InnoDBDialect does the right thing for InnoDB) so you need to create your own.

Create this class in src/groovy or src/java (change the package and/or class name):

package com.mycompany.myapp

import org.hibernate.dialect.MySQLMyISAMDialect

class MySQL5MyISAMDialect extends MySQLMyISAMDialect {
   String getTableTypeString() {
      " ENGINE=MyISAM"
   }
}

and reference it in DataSource.groovy as you showed in your question:

dialect = com.mycompany.myapp.MySQL5MyISAMDialect

这篇关于Grails：在更改hibernate方言时SQL语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！