在Golang代码中,MySQL查询中的自增功能不起作用。

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

auto increment in mysql query in golang code not working

问题

我正在使用以太坊区块链开发一个项目,我想用区块数据填充数据库,但是对于block_id,自动递增功能不起作用。

下面是创建查询的代码:

stmt, err := db.Prepare("CREATE TABLE IF NOT EXISTS block( block_id bigint NOT NULL AUTO_INCREMENT, block_num varchar(200), block_hash varchar(200), tx_count int, PRIMARY KEY (block_id) );")

下面的代码用于插入数据:

func InsertBlock(db *sql.DB, block_num string, block_hash string, tx_count int) {
	stmt, err := db.Prepare("INSERT INTO block VALUES(?, ?, ?)")
	if err != nil {
		fmt.Println(err.Error())
	} else {
		fmt.Println("Preparation successfull for block insert:")
	}

	_, err = stmt.Exec(block_num, block_hash, tx_count)
	if err != nil {
		fmt.Println(err.Error())
	} else {
		fmt.Println("Entry is block table is successfull:")
	}
}

如何使其自动递增?

这是我得到的错误:

Error 1136: Column count doesn't match value count at row 1
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x38 pc=0x4e1930]
英文:

I am working on a project using ethereum blockchain and I want to populate db with blocks data but for block_id autoincrement is not working.

code below is the create query

stmt, err := db.Prepare("CREATE TABLE IF NOT EXISTS block( block_id bigint NOT NULL AUTO_INCREMENT, block_num varchar(200), block_hash varchar(200), tx_count int, PRIMARY KEY (block_id) );")

code below is used to insert the data

func InsertBlock(db *sql.DB, block_num string, block_hash string, tx_count int) {
	stmt, err := db.Prepare("INSERT INTO block VALUES(?, ?, ?)")
	if err != nil {
		fmt.Println(err.Error())
	} else {
		fmt.Println("Preparation successfull for block insert: ")
	}

	_, err = stmt.Exec(block_num, block_hash, tx_count)
	if err != nil {
		fmt.Println(err.Error())
	} else {
		fmt.Println("Entry is block table is successfull: ")
	}
}

How can I make it auto increment?

Here is the error i am getting:

Error 1136: Column count doesn't match value count at row 1
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x38 pc=0x4e1930]

答案1

得分: 2

根据文档中的说明:

如果在INSERT ... VALUES或INSERT ... SELECT中没有指定列名列表,则必须通过VALUES列表或SELECT语句提供表中每一列的值。如果您不知道表中列的顺序,请使用DESCRIBE tbl_name来查找。

这意味着你的查询 INSERT INTO block VALUES(?, ?, ?) 会始终失败,因为你只指定了三个值中的四个。

所以你需要指定列的列表,像这样:

INSERT INTO block (block_num, block_hash, tx_count) VALUES(?, ?, ?)

这样,MySQL就不会再抱怨了,因为它知道缺少的列 block_id 是自动递增的,所以不需要为其提供值。

英文:

As specified in the documentation:

> If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out.

That means that your query INSERT INTO block VALUES(?, ?, ?) will always fail because you are specifying only three out of four values.

So you need to specify the list of the columns, like this:

INSERT INTO block (block_num, block_hash, tx_count) VALUES(?, ?, ?)

At that point MySql will not complain anymore because it knows that the missing column block_id is auto incremented so it doesn't need a value for that.

huangapple
  • 本文由 发表于 2019年8月2日 05:24:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/57317180.html
匿名

发表评论

匿名网友

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

确定