Go Gorm原始SQL创建表然后插入错误

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

Go Gorm raw sql create table then insert error

问题

我在使用Go和Gorm包时遇到了一些问题。我试图使用原始查询创建表和插入表。不幸的是,错误显示我在SQL代码中有语法错误,但是当我尝试使用PhpMyAdmin执行时,一切都正常。

以下是最小可复现代码:

package main

import (
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

const CREATE_TABLE_SQL = "CREATE TABLE `mytable` (`a` int); INSERT INTO `mytable` (`a`) VALUES (1);"

func main() {
	dsn := "sandbox:sandbox@tcp(localhost)/sandbox?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn))
	if err != nil {
		panic(err)
	}

	err2 := db.Exec(CREATE_TABLE_SQL).Error
	if err2 != nil {
		panic(err2)
	}
}

不幸的是,它产生了以下错误:

$ go run .

2023/03/09 13:54:01 go-raw-sql-create-table-error/main.go:17 Error 1064 (42000): 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 'INSERT INTO `mytable` (`a`) VALUES (1)' at line 1       
[0.524ms] [rows:0] CREATE TABLE `mytable` (`a` int); INSERT INTO `mytable` (`a`) VALUES (1);
panic: Error 1064 (42000): 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 'INSERT INTO `mytable` (`a`) VALUES (1)' at line 1

goroutine 1 [running]:
main.main()
        go-raw-sql-create-table-error/main.go:19 +0xef
exit status 2

我目前正在使用Docker运行MySQL容器进行开发。以下是我使用的Docker Compose配置:

version: '3.8'

services:
  mysql:
    image: mysql:8
    container_name: mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: sandboxroot
      MYSQL_DATABASE: sandbox
      MYSQL_USER: sandbox
      MYSQL_PASSWORD: sandbox
    ports:
      - 3306:3306

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: phpmyadmin
    restart: always
    environment:
      PMA_HOST: mysql
      PMA_PORT: 3306
      PMA_USER: sandbox
      PMA_PASSWORD: sandbox
    ports:
      - 8080:80

希望这些信息对你有帮助!

英文:

I got encountered some problem with Go and Gorm package. I was trying to create table and insert table using raw query. Sadly, the error said I got a syntax error in the SQL code, but when I try to execute it by using PhpMyAdmin it's just working fine.

Here is the code minimum reproduce code:

package main

import (
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

const CREATE_TABLE_SQL = "CREATE TABLE `mytable` (`a` int); INSERT INTO `mytable` (`a`) VALUES (1);"

func main() {
	dsn := "sandbox:sandbox@tcp(localhost)/sandbox?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn))
	if err != nil {
		panic(err)
	}

	err2 := db.Exec(CREATE_TABLE_SQL).Error
	if err2 != nil {
		panic(err2)
	}
}

Unfortunately, it's producing the error below:

$ go run .

2023/03/09 13:54:01 go-raw-sql-create-table-error/main.go:17 Error 1064 (42000): 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 'INSERT INTO `mytable` (`a`) VALUES (1)' at line 1       
[0.524ms] [rows:0] CREATE TABLE `mytable` (`a` int); INSERT INTO `mytable` (`a`) VALUES (1);
panic: Error 1064 (42000): 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 'INSERT INTO `mytable` (`a`) VALUES (1)' at line 1

goroutine 1 [running]:
main.main()
        go-raw-sql-create-table-error/main.go:19 +0xef
exit status 2

I'm currently using Docker running MySQL container for dev.
Here is the Docker compose that I'm using:

version: '3.8'

services:
  mysql:
    image: mysql:8
    container_name: mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: sandboxroot
      MYSQL_DATABASE: sandbox
      MYSQL_USER: sandbox
      MYSQL_PASSWORD: sandbox
    ports:
      - 3306:3306

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: phpmyadmin
    restart: always
    environment:
      PMA_HOST: mysql
      PMA_PORT: 3306
      PMA_USER: sandbox
      PMA_PASSWORD: sandbox
    ports:
      - 8080:80

答案1

得分: 0

在你的连接字符串中,你应该添加参数 multiStatements=true

所以你的代码应该像这样:

package main

import (
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

const CREATE_TABLE_SQL = "CREATE TABLE `mytable` (`a` int); INSERT INTO `mytable` (`a`) VALUES (1);"

func main() {
	dsn := "sandbox:sandbox@tcp(localhost)/sandbox?charset=utf8mb4&parseTime=True&loc=Local&multiStatements=true"
	db, err := gorm.Open(mysql.Open(dsn))
	if err != nil {
		panic(err)
	}

	err2 := db.Exec(CREATE_TABLE_SQL).Error
	if err2 != nil {
		panic(err2)
	}
}
英文:

In your connection string you should add parameter multiStatements=true

So your code would look like this:

package main

import (
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

const CREATE_TABLE_SQL = "CREATE TABLE `mytable` (`a` int); INSERT INTO `mytable` (`a`) VALUES (1);"

func main() {
	dsn := "sandbox:sandbox@tcp(localhost)/sandbox?charset=utf8mb4&parseTime=True&loc=Local&multiStatements=true"
	db, err := gorm.Open(mysql.Open(dsn))
	if err != nil {
		panic(err)
	}

	err2 := db.Exec(CREATE_TABLE_SQL).Error
	if err2 != nil {
		panic(err2)
	}
}

huangapple
  • 本文由 发表于 2023年3月9日 15:07:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/75681406.html
匿名

发表评论

匿名网友

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

确定