英文:
In golang how to support multiple sql syntax (mysql vs postgres)
问题
我的Go应用程序应支持多个数据库。也就是说,使用相同的二进制文件和不同的数据库运行应用程序,应用程序所使用的数据库将由配置确定。
问题是,每个数据库都有自己的预处理语句语法。
例如:
db.Prepare("select p, f, t from mytable where p = $1")
这个语句适用于PostgreSQL,但对于MySQL则不适用。
db.Prepare("select p, f, t from mytable where p = ?")
这个语句适用于MySQL,但对于PostgreSQL则不适用。
当然,我可以通过在运行时编辑字符串或维护多个查询来解决这个问题。
有没有更好的方法?
我不想使用一个庞大的抽象层和外部库来控制我的所有数据库访问,但如果有一个轻量级的库可以自动修复语法问题,我可以接受。
编辑:
总结我之前说的,让我困扰的是对于MySQL,你必须使用"?",而对于PostgreSQL,你必须使用$1、$2等。
谢谢。
英文:
My go app shall support multiple databases. Meaning, running the same binary with different databases, the database the app is working with will be determined by configuration.
The problem is, each database has it's own prepared statements syntax.
Example:
db.Prepare("select p, f, t from mytable where p = $1")
Will work for postgres but will not work for mysql.
db.Prepare("select p, f, t from mytable where p = ?")
Will work for mysql but will not work for postgres.
Off curse I can solve it by editing the string on runtime or maintaining multiple queries.
Is there a better way?
I do not want to have some huge abstraction with an external library that will take control on all my db access, but if there is some light weight library that just magically fix the syntax, I am good with that.
EDIT:
Summarising what I have said before, the part that bothers me is that for mysql you will have to use "?" while for postgres you will have to use $1, $2...
Cheers
答案1
得分: 1
我发现db.Rebind()
可以帮助解决这个问题。所以代码如下:
name := "my name"
var p = property{}
// language=SQL
s := "SELECT * FROM property WHERE name=?"
err := db.Get(&p, db.Rebind(s), name)
顶部的语言注释是为了让IntelliJ在UI中对SQL语句进行语法检查。
我还需要为每个数据库编写单独的CREATE语句(我的应用程序同时支持mysql、postgres和sqlite)。
我还发现mysql和sqlite之间的UPDATE语句语法是相同的,但是postgres需要特殊处理。由于我的UPDATE语句非常一致,我可以编写一个函数将mysql方言转换为postgres方言。这当然不是一个通用的解决方案,但对于我的单元测试和集成测试来说已经足够了。具体情况可能有所不同。
// RebindMore接受一个MySQL SQL字符串,并在必要时将其转换为Postgres。
// db.Rebind()会将'?'转换为'$1',但不处理Postgres需要的SQL语句语法更改。
//
// 将: "UPDATE table_name SET a = ?, b = ?, c = ? WHERE d = ?"
// 转换为: "UPDATE table_name SET (a, b, c) = ROW (?, ?, ?) WHERE d = ?"
func RebindMore(db *sqlx.DB, s string) string {
if db.DriverName() != "postgres" {
return s
}
if !strings.HasPrefix(strings.ToLower(s), "update") {
return db.Rebind(s)
}
// 将MySQL的UPDATE语句转换为Postgres的UPDATE语句。
var idx int
idx = strings.Index(strings.ToLower(s), "set")
if idx < 0 {
log.Fatal().Msg("no SET clause in RebindMore (" + s + ")")
}
prefix := s[:idx+3]
s2 := s[idx+3:]
idx = strings.Index(strings.ToLower(s2), "where")
if idx < 0 {
log.Fatal().Msg("no WHERE clause in RebindMore (" + s + ")")
}
suffix := s2[idx:]
s3 := s2[:idx]
s4 := strings.TrimSpace(s3)
arr := strings.Split(s4, ",")
var names = ""
var values = ""
for i := 0; i < len(arr); i++ {
nameEqValue := arr[i]
s5 := strings.ReplaceAll(nameEqValue, " ", "")
nvArr := strings.Split(s5, "=")
if names != "" {
names += ","
}
names += nvArr[0]
if values != "" {
values += ","
}
values += nvArr[1]
}
s6 := prefix + " (" + names + ") = ROW (" + values + ") " + suffix
return db.Rebind(s6)
}
调用方式如下:
// language=SQL
s := RebindMore(db, "UPDATE table_name SET a = ?, b = ? WHERE c = ?")
db.MustExec(s, value1, value2)
在某个时候,我需要添加迁移,并且预计只需根据每个数据库添加单独的代码来处理差异(就像CREATE一样)。
最后值得指出的一点是,MySQL和Postgres对大小写处理方式非常不同。我最终将每个表和列名转换为lower_case
,以避免不必要的复杂性。
英文:
I found db.Rebind() to help with this. So:
name := "my name"
var p = property{}
// language=SQL
s := "SELECT * FROM property WHERE name=?"
err := db.Get(&p, db.Rebind(s), name)
The language comment at the top is so that IntelliJ can still syntax-check the SQL statement for me in the UI.
I also had to write separate CREATE statements for each database (my application is simultaneously supporting mysql, postgres, and sqlite).
I also found the UPDATE statement syntax between mysql and sqlite to be the same, but postgres required special handling. Since my UPDATE statements are very consistent, I was able to write a function to just kludge-translate from the mysql dialect to the postgres dialect. This is definitely not a generic solution but worked well enough for my unit and integration tests to pass. YMMV.
// RebindMore takes a MySQL SQL string and convert it to Postgres if necessary.
// The db.Rebind() handles converting '?' to '$1', but does not handle SQL statement
// syntactic changes needed by Postgres.
//
// convert: "UPDATE table_name SET a = ?, b = ?, c = ? WHERE d = ?"
// to: "UPDATE table_name SET (a, b, c) = ROW (?, ?, ?) WHERE d = ?"
func RebindMore(db *sqlx.DB, s string) string {
if db.DriverName() != "postgres" {
return s
}
if !strings.HasPrefix(strings.ToLower(s), "update") {
return db.Rebind(s)
}
// Convert a MySQL update statement into a Postgres update statement.
var idx int
idx = strings.Index(strings.ToLower(s), "set")
if idx < 0 {
log.Fatal().Msg("no SET clause in RebindMore (" + s + ")")
}
prefix := s[:idx+3]
s2 := s[idx+3:]
idx = strings.Index(strings.ToLower(s2), "where")
if idx < 0 {
log.Fatal().Msg("no WHERE clause in RebindMore (" + s + ")")
}
suffix := s2[idx:]
s3 := s2[:idx]
s4 := strings.TrimSpace(s3)
arr := strings.Split(s4, ",")
var names = ""
var values = ""
for i := 0; i < len(arr); i++ {
nameEqValue := arr[i]
s5 := strings.ReplaceAll(nameEqValue, " ", "")
nvArr := strings.Split(s5, "=")
if names != "" {
names += ","
}
names += nvArr[0]
if values != "" {
values += ","
}
values += nvArr[1]
}
s6 := prefix + " (" + names + ") = ROW (" + values + ") " + suffix
return db.Rebind(s6)
}
Call it this way:
// language=SQL
s := RebindMore(db, "UPDATE table_name SET a = ?, b = ? WHERE c = ?")
db.MustExec(s, value1, value2)
At some point I will need to add migration, and expect to just add separate code per DB to handle the differences (like for CREATE).
One final thing worth pointing out is that MySQL and Postgres handle capitalization very differently. I ended up just converting every table and column name to lower_case
to avoid unnecessary complexity.
答案2
得分: 0
在这种特殊情况下,在SQL语句的末尾使用一个占位符{{ph}},然后使用strings.Replace()函数将其替换为?或$1,具体取决于数据库驱动程序。
英文:
In this particular case use a place holder {{ph}} in the end of the SQL and use strings.Replace() to replace it with ? Or $1 according to the db driver.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论