Passing Go var to MySQL – Error when trying to do db.Exec in Go lang program (MySQL db)

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

Passing Go var to MySQL - Error when trying to do db.Exec in Go lang program (MySQL db)

问题

我正在尝试在db.Exec中传递一个Go变量:

	pinakas := "dokimi03"
	crTable := `CREATE TABLE ? (id bigint(20) NOT NULL AUTO_INCREMENT, username varchar(100) NOT NULL, password varchar(100) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY (id));`

	_, errCreate := db.Exec(crTable, pinakas)
	if errCreate != nil {
		log.Println(errCreate)
	}

当我运行这段代码时,MySQL返回的错误是:

Error 1064: 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 '? (id bigint(20) NOT NULL AUTO_INCREMENT, username varchar(100) NOT NULL, passwo' at line 1

如果我用dokimi03替换?,即表名(当然还要删除pinakas变量),代码就可以运行。

我已经查阅了文档,?似乎是在Go-MySQL中表示变量的正确字符。我漏掉了什么?

英文:

I'm trying to pass a Go variable inside db.Exec:

	pinakas := "dokimi03"
	crTable := `CREATE TABLE ? (id bigint(20) NOT NULL AUTO_INCREMENT, username varchar(100) NOT NULL, password varchar(100) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY (id));`

	_, errCreate := db.Exec(crTable, pinakas)
	if errCreate != nil {
		log.Println(errCreate)
	}

The error I get from MySQL when I run the code is:
>Error 1064: 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 '? (id bigint(20) NOT NULL AUTO_INCREMENT, username varchar(100) NOT NULL, passwo' at line 1

The code runs if I substitute ? with dokimi03, the table name (and remove pinakas var of course).

I've searched the docs, ? seems to be the proper character to represent variables on Go-MySQL. What am I missing?

答案1

得分: 2

"?" 是 MySQL 中用于参数占位符的正确字符,但是你不能将参数用于表标识符。

参数只能用作标量值的替代,而不能用作标识符、SQL 关键字、表达式等的替代。

这不仅适用于 MySQL,也不仅适用于 Go 连接器。这是 SQL 语言的每个实现中的标准规定(有一些连接器通过字符串替换来模拟参数,但是 MySQL 的 Go 连接器不这样做)。

在将 SQL 字符串传递给 db.Exec() 之前,表名必须在其中固定。

例如,我会这样编写:

pinakas := "dokimi03"
crTable := fmt.Sprintf("CREATE TABLE `%s` (id bigint(20) NOT NULL AUTO_INCREMENT, username varchar(100) NOT NULL, password varchar(100) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY (id))", pinakas)
英文:

? is the proper character for a parameter placeholder in MySQL, but you can't use a parameter for a table identifier.

Parameters can be used only as a substitute for a scalar value, not an identifier, or an SQL keyword, or an expression, etc.

This is not only relevant to MySQL and not only relevant to the Go connector. It's standard in every implementation of the SQL language (there are some connectors that fake parameters by doing string-substitution, but the Go connector for MySQL does not do that).

The table name must be fixed in the SQL string before passing it to db.Exec().

For example, I would write it this way:

pinakas := "dokimi03"
crTable := fmt.Sprintf("CREATE TABLE `%s` (id bigint(20) NOT NULL AUTO_INCREMENT, username varchar(100) NOT NULL, password varchar(100) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY (id))", pinakas)

huangapple
  • 本文由 发表于 2022年11月9日 22:57:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/74376932.html
匿名

发表评论

匿名网友

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

确定