Golang代码中的mysql查询中的自动增量不起作用

Question

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]

Answer 1

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 for 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.