批量Insert时Omit出错 #1143

Closed
opened 2018-11-13 02:06:27 +00:00 by eleztian · 5 comments
eleztian commented 2018-11-13 02:06:27 +00:00 (Migrated from github.com)

在操作mysql时,我在update中去掉end_time字段插入, 下面第一种可以正常进行。

for _, v := range in { _, err := dstSession.Omit("end_time").Insert(v) if err != nil { panic(err) } }

但是,这样会报 Error 1048: Column 'end_time' cannot be null

_, err := dstSession.Omit("end_time").Insert(in....) if err != nil { panic(err) }

这是为啥???

在操作mysql时,我在update中去掉end_time字段插入, 下面第一种可以正常进行。 ` for _, v := range in { _, err := dstSession.Omit("end_time").Insert(v) if err != nil { panic(err) } } ` 但是,这样会报 Error 1048: Column 'end_time' cannot be null ` _, err := dstSession.Omit("end_time").Insert(in....) if err != nil { panic(err) } ` 这是为啥???
Member

可以提供下详细的日志信息吗,可以通过 Engine.ShowSQL(true)打开日志输出

可以提供下详细的日志信息吗,可以通过 `Engine.ShowSQL(true)`打开日志输出
eleztian commented 2018-11-13 13:36:18 +00:00 (Migrated from github.com)

sql:

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `end_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 41 CHARACTER SET = utf8 COLLATE = utf8_croatian_ci ROW_FORMAT = Dynamic;

程序

package main

import (
	_ "github.com/go-sql-driver/mysql"
	"github.com/go-xorm/xorm"
	"time"
)

type Test struct {
	Id      int
	Name    string
	EndTime time.Time
}

func main() {
	x, err := xorm.NewEngine("mysql", "root:123456@(xxx.xxxx.xxx:3306)/test")
	if err != nil {
		panic(err)
	}
	x.ShowSQL(true)
	err = x.Ping()
	if err != nil {
		panic(err)
	}

	im := []interface{}{
		Test{Name: "tab"},
		Test{Name: "jack"},
		Test{Name: "barry"},
	}
	_, err = x.Omit("end_time").Insert(im...)
	if err != nil {
		panic(err)
	}
}

结果:

[xorm] [info]  2018/11/13 21:30:03.375083 [SQL] INSERT INTO `test` (`id`,`name`) VALUES (?, ?) []interface {}{0, "tab"}
[xorm] [info]  2018/11/13 21:30:03.377092 [SQL] INSERT INTO `test` (`id`,`name`,`end_time`) VALUES (?, ?, ?) []interface {}{0, "jack", interface {}(nil)}

@BetaCat0

sql: ```SQL DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `end_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 41 CHARACTER SET = utf8 COLLATE = utf8_croatian_ci ROW_FORMAT = Dynamic; ``` 程序 ```go package main import ( _ "github.com/go-sql-driver/mysql" "github.com/go-xorm/xorm" "time" ) type Test struct { Id int Name string EndTime time.Time } func main() { x, err := xorm.NewEngine("mysql", "root:123456@(xxx.xxxx.xxx:3306)/test") if err != nil { panic(err) } x.ShowSQL(true) err = x.Ping() if err != nil { panic(err) } im := []interface{}{ Test{Name: "tab"}, Test{Name: "jack"}, Test{Name: "barry"}, } _, err = x.Omit("end_time").Insert(im...) if err != nil { panic(err) } } ``` 结果: ```bash [xorm] [info] 2018/11/13 21:30:03.375083 [SQL] INSERT INTO `test` (`id`,`name`) VALUES (?, ?) []interface {}{0, "tab"} [xorm] [info] 2018/11/13 21:30:03.377092 [SQL] INSERT INTO `test` (`id`,`name`,`end_time`) VALUES (?, ?, ?) []interface {}{0, "jack", interface {}(nil)} ``` @BetaCat0
eleztian commented 2018-11-13 13:57:49 +00:00 (Migrated from github.com)
func (session *Session) exec(sqlStr string, args ...interface{}) (sql.Result, error) {
	defer session.resetStatement() // 这里把它清空了?

	session.queryPreprocess(&sqlStr, args...)

	if session.engine.showSQL {
		if session.engine.showExecTime {
			b4ExecTime := time.Now()
			defer func() {
				execDuration := time.Since(b4ExecTime)
				if len(args) > 0 {
					session.engine.logger.Infof("[SQL] %s %#v - took: %v", sqlStr, args, execDuration)
				} else {
					session.engine.logger.Infof("[SQL] %s - took: %v", sqlStr, execDuration)
				}
			}()
		} else {
			if len(args) > 0 {
				session.engine.logger.Infof("[SQL] %v %#v", sqlStr, args)
			} else {
				session.engine.logger.Infof("[SQL] %v", sqlStr)
			}
		}
	}

	if !session.isAutoCommit {
		return session.tx.Exec(sqlStr, args...)
	}

	if session.prepareStmt {
		stmt, err := session.doPrepare(session.DB(), sqlStr)
		if err != nil {
			return nil, err
		}

		res, err := stmt.Exec(args...)
		if err != nil {
			return nil, err
		}
		return res, nil
	}

	return session.DB().Exec(sqlStr, args...)
}

```go func (session *Session) exec(sqlStr string, args ...interface{}) (sql.Result, error) { defer session.resetStatement() // 这里把它清空了? session.queryPreprocess(&sqlStr, args...) if session.engine.showSQL { if session.engine.showExecTime { b4ExecTime := time.Now() defer func() { execDuration := time.Since(b4ExecTime) if len(args) > 0 { session.engine.logger.Infof("[SQL] %s %#v - took: %v", sqlStr, args, execDuration) } else { session.engine.logger.Infof("[SQL] %s - took: %v", sqlStr, execDuration) } }() } else { if len(args) > 0 { session.engine.logger.Infof("[SQL] %v %#v", sqlStr, args) } else { session.engine.logger.Infof("[SQL] %v", sqlStr) } } } if !session.isAutoCommit { return session.tx.Exec(sqlStr, args...) } if session.prepareStmt { stmt, err := session.doPrepare(session.DB(), sqlStr) if err != nil { return nil, err } res, err := stmt.Exec(args...) if err != nil { return nil, err } return res, nil } return session.DB().Exec(sqlStr, args...) } ```
Member

我仔细看了下,在代码里你使用了

im := []interface{}{
Test{Name: "tab"},
Test{Name: "jack"},
Test{Name: "barry"},
}
_, err = x.Omit("end_time").Insert(im...)
if err != nil {
panic(err)
}

这看起来是个bug,将会在 https://github.com/go-xorm/xorm/pull/1146 修复。可以先通过x.Omit("end_time").Insert(im)(去掉...)来规避这个错误

我仔细看了下,在代码里你使用了 > im := []interface{}{ Test{Name: "tab"}, Test{Name: "jack"}, Test{Name: "barry"}, } _, err = x.Omit("end_time").Insert(im...) if err != nil { panic(err) } 这看起来是个bug,将会在 https://github.com/go-xorm/xorm/pull/1146 修复。可以先通过`x.Omit("end_time").Insert(im)`(去掉...)来规避这个错误

Insert(im) will fix the problem.

`Insert(im)` will fix the problem.
Sign in to join this conversation.
No Milestone
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#1143
No description provided.