在gorm golang中,插入忽略(Insert Ignore)查询出现错误。

huangapple go评论77阅读模式
英文:

Insert Ignore query giving error in gorm golang

问题

我有一个流派的列表,我想要插入到数据库中。这只需要在应用程序第一次运行时执行一次。因此,我尝试使用"ignore"关键字进行插入,但是出现了一些错误。

GenreCategories结构体如下:

type GenreCategories struct {
    Category string `gorm:"unique"`
}

查询代码如下:

func (a *App) setupGenreCategories() {
    for _, value := range handler.GenreCategorySlice {
        a.DB.Clauses(clause.Insert{Modifier: "ignore"}).Create(&models.GenreCategories{
            Category: value,
        })
        // 另一种方法,但是出现了相同的错误:
        if a.DB.Model(&models.GenreCategories{}).Where("category = ?", value).RowsAffected == 0 {
            a.DB.Create(&models.GenreCategories{
                Category: value,
            })
        }
    }
}

以下是我收到的错误信息:

near "ignore": syntax error
[0.019ms] [rows:0] INSERT ignore INTO `genre_categories` (`category`) VALUES ("sunsets")

对于另一种方法,错误信息如下:

UNIQUE constraint failed: genre_categories.category
[0.056ms] [rows:0] INSERT INTO `genre_categories` (`category`) VALUES ("sunsets")

我的语法是否有误或与gorm v2有关,我正在使用的是gorm v1.22版本,希望这些信息足够了。提前感谢您的帮助。

英文:

I have a list of genres that I am trying to insert into the database. This is to be done just once when the application runs for the very first time. Hence, I am trying to insert ignore but it is giving me some error.

The GenreCategoires struct:

type GenreCategories struct {
	Category string `gorm:"unique"`
}

The query is as follows:

func (a *App) setupGenreCategories() {
	for _, value := range handler.GenreCategorySlice {
		a.DB.Clauses(clause.Insert{Modifier: "ignore"}).Create(&models.GenreCategories{
			Category: value,
		})
        // Alternate approach but with the same errors:
		if a.DB.Model(&models.GenreCategories{}).Where("category = ?", value).RowsAffected == 0 {
			a.DB.Create(&models.GenreCategories{
				Category: value,
			})
		}
	}
}

Here is the error that I am receiving:

near "ignore": syntax error
[0.019ms] [rows:0] INSERT ignore INTO `genre_categories` (`category`) VALUES ("sunsets")

For the alternate approach, the error is as follows:

UNIQUE constraint failed: genre_categories.category
[0.056ms] [rows:0] INSERT INTO `genre_categories` (`category`) VALUES ("sunsets")

Is my syntax wrong or related to gorm v2, I am using gorm v1.22, hope this information is sufficient. Thanks in advance.

答案1

得分: 2

你正在使用MySQL语法来操作Sqlite3,这显然会导致问题。在Sqlite中,你需要使用INSERT OR IGNORE而不是INSERT IGNORE,所以你很可能只需要将以下代码进行修改:

a.DB.Clauses(clause.Insert{Modifier: "ignore"})

修改为:

a.DB.Clauses(clause.Insert{Modifier: "or ignore"})
英文:

You are using MySQL syntax for Sqlite3, which will obviously cause issues. In sqlite you need to do INSERT OR IGNORE, not INSERT IGNORE, so you most likely simply need to change

a.DB.Clauses(clause.Insert{Modifier: "ignore"})

to

a.DB.Clauses(clause.Insert{Modifier: "or ignore"})

huangapple
  • 本文由 发表于 2022年2月3日 02:40:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/70961062.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定