英文:
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)
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论