英文:
How to delete range of items from database with gorm
问题
每次我运行一个gorm查询来删除表中的一系列行时，我都会得到以下错误。
(/home/gregf/code/go/src/github.com/gregf/gormtest/main.go:39)
[2015-06-24 18:55:56] [0.34ms] SELECT id FROM "podcasts"
(/home/gregf/code/go/src/github.com/gregf/gormtest/main.go:50)
[2015-06-24 18:55:56] near "LIMIT": syntax error
(/home/gregf/code/go/src/github.com/gregf/gormtest/main.go:50)
[2015-06-24 18:55:56] [0.82ms] DELETE FROM "episodes" WHERE (podcast_id = '1') LIMIT 4 OFFSET 2
2015/06/24 18:55:56 &{{0 0 false } near "LIMIT": syntax error 0 <nil> 0xc20802c280 0xc20802c140 0xc20802f900 2 <nil> <nil> false map[gorm:started_transaction0xc2080380c00xc20805a1c0:true] map[]}
直接在sqlite3中运行查询返回的结果很好
DELETE FROM "episodes" WHERE (podcast_id = '1') LIMIT 4 OFFSET 2;
Run Time: real 0.000 user 0.000000 sys 0.000000
示例代码
package main
import (
"log"
"github.com/jinzhu/gorm"
_ "github.com/mattn/go-sqlite3"
)
type Podcast struct {
Id int
Title string
RssUrl string `sql:"unique_index"`
Episodes []Episode
}
type Episode struct {
Id int
PodcastID int
Title string
Url string `sql:"unique_index"`
Downloaded bool
Guid string `sql:"unique_index"`
}
func main() {
db, err := gorm.Open("sqlite3", "cache.db")
if err != nil {
log.Fatal(err)
}
db.LogMode(true)
db.CreateTable(&Podcast{})
db.CreateTable(&Episode{})
rows, err := db.Table("podcasts").Select("id").Rows()
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var podcastId int
rows.Scan(&podcastId)
err := db.Table("episodes").Where("podcast_id = ?", podcastId).
Limit(4).
Offset(2).
Delete(Episode{})
if err != nil {
log.Printf("%s\n", err)
}
}
}
英文:
Every time I run a gorm query to delete a range of rows from a table I get the following error.
(/home/gregf/code/go/src/github.com/gregf/gormtest/main.go:39)
[2015-06-24 18:55:56] [0.34ms] SELECT id FROM "podcasts"
(/home/gregf/code/go/src/github.com/gregf/gormtest/main.go:50)
[2015-06-24 18:55:56] near "LIMIT": syntax error
(/home/gregf/code/go/src/github.com/gregf/gormtest/main.go:50)
[2015-06-24 18:55:56] [0.82ms] DELETE FROM "episodes" WHERE (podcast_id = '1') LIMIT 4 OFFSET 2
2015/06/24 18:55:56 &{{0 0 false } near "LIMIT": syntax error 0 <nil> 0xc20802c280 0xc20802c140 0xc20802f900 2 <nil> <nil> false map[gorm:started_transaction0xc2080380c00xc20805a1c0:true] map[]}
Running the query in sqlite3 directly returns just fine
DELETE FROM "episodes" WHERE (podcast_id = '1') LIMIT 4 OFFSET 2;
Run Time: real 0.000 user 0.000000 sys 0.000000
Example code
package main
import (
"log"
"github.com/jinzhu/gorm"
_ "github.com/mattn/go-sqlite3"
)
type Podcast struct {
Id int
Title string
RssUrl string `sql:"unique_index"`
Episodes []Episode
}
type Episode struct {
Id int
PodcastID int
Title string
Url string `sql:"unique_index"`
Downloaded bool
Guid string `sql:"unique_index"`
}
func main() {
db, err := gorm.Open("sqlite3", "cache.db")
if err != nil {
log.Fatal(err)
}
db.LogMode(true)
db.CreateTable(&Podcast{})
db.CreateTable(&Episode{})
rows, err := db.Table("podcasts").Select("id").Rows()
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var podcastId int
rows.Scan(&podcastId)
err := db.Table("episodes").Where("podcast_id = ?", podcastId).
Limit(4).
Offset(2).
Delete(Episode{})
if err != nil {
log.Printf("%s\n", err)
}
}
}
答案1
得分: 1
看起来这是sqlite3驱动程序的问题。
请查看这个线程：https://stackoverflow.com/questions/1824490/how-do-you-enable-limit-for-delete-in-sqlite
你需要以某种方式传递标志SQLITE_ENABLE_UPDATE_DELETE_LIMIT给这个驱动程序。不幸的是，我不知道如何做到这一点，因为该驱动程序使用了SQLite源代码的"合并"版本，并且文档中说你不能在其中使用SQLITE_ENABLE_UPDATE_DELETE_LIMIT（https://www.sqlite.org/compile.html）。
英文:
Looks like it's a problem of sqlite3 driver.
Please check this thread: https://stackoverflow.com/questions/1824490/how-do-you-enable-limit-for-delete-in-sqlite
You need to pass flag SQLITE_ENABLE_UPDATE_DELETE_LIMIT to this driver somehow. Unfortunately, I have no idea how, because the driver uses "amalgamation" of SQLite source code and documentation says that you cannot use SQLITE_ENABLE_UPDATE_DELETE_LIMIT with it (https://www.sqlite.org/compile.html).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论