使用RegisterReaderHandler在MySQL中插入行。

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

Using RegisterReaderHandler to insert rows in mysql

问题

我正在使用go-sql-drivergo连接到mysql,并且我想使用一个读取器来插入大量的行(34M+)。

我在使用io.Reader加载数据时遇到了困难,具体请参考RegisterReaderHandler文档

我有一个定义如下的表:

CREATE TABLE `test` (
  `Id` int(11) NOT NULL,
  `One` varchar(45) DEFAULT NULL,
  `Two` varchar(45) DEFAULT NULL,
  `Three` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我尝试通过以下方式插入一行:

func (r MyReader) Read(p []byte) (n int, err error){
    buf := new(bytes.Buffer)
    wr := csv.NewWriter(buf)

    // just generate a csv line to see if this works
    // I've verified that the size of the result is lower than p's capacity
    wr.Write([]string{"1", "Something", "NULL", "Something"})
    wr.Flush()

    p = buf.Bytes()    
    return len(p), io.EOF
}

func main() {
    mysql.RegisterReaderHandler("data", func() io.Reader {
        // pass my reader
        return MyReader{}
    })
    
    db, err := sql.Open("mysql", "user:pass@/db")
    checkErr(err)
    
    _, err = db.Exec("LOAD DATA LOCAL INFILE 'Reader::data' INTO TABLE test FIELDS TERMINATED BY ','")
    checkErr(err)
}

我看到我的Read操作被调用了,运行后会创建一个新元素。然而,它的Id为0,所有其他元素都为NULL,所以它没有正确处理数据。

顺便说一下,我在mysql中运行LOAD DATA LOCAL命令(使用与读取器生成的内容相同)时,行被成功插入。

我漏掉了什么?

英文:

I'm using go-sql-driver to connect to mysql from go, and I'd love to use a reader to insert a big number of rows (34M+).

I'm having a hard time using an io.Reader to load the data as shown on the RegisterReaderHandler documentation.

I have a table defined like this

CREATE TABLE `test` (
  `Id` int(11) NOT NULL,
  `One` varchar(45) DEFAULT NULL,
  `Two` varchar(45) DEFAULT NULL,
  `Three` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And I'm trying to insert a row by doing

func (r MyReader) Read(p []byte) (n int, err error){
    buf := new(bytes.Buffer)
    wr := csv.NewWriter(buf)

    // just generate a csv line to see if this works
    // I've verified that the size of the result is lower than p's capacity
    wr.Write([]string{"1", "Something", "NULL", "Something"})
    wr.Flush()

    p = buf.Bytes()    
    return len(p), io.EOF
}

func main() {
    mysql.RegisterReaderHandler("data", func() io.Reader {
        // pass my reader
        return MyReader{}
    })
    
    db, err := sql.Open("mysql", "user:pass@/db")
    checkErr(err)
    
    _, err = db.Exec("LOAD DATA LOCAL INFILE 'Reader::data' INTO TABLE test FIELDS TERMINATED BY ','")
    checkErr(err)
}

I see that my Read operation is called, and after running this a new element is created. However, it has Id 0 and all the other elements are NULL, so it's not processing the data correctly.

FWIW, I run the LOAD DATA LOCAL command in mysql (with the same contents the reader generates) and the row is successfully inserted.

What am I missing?

答案1

得分: 0

显然,在发布问题后的3分钟内,我找到了答案。

我正在替换MyReader中的输出缓冲区。所以将以下行

p = buf.Bytes()

改为

out = buf.Bytes()
copy(p, out)

就解决了问题。

英文:

Obviously, 3 minutes after posting the question I figured out the answer.

I was replacing the output buffer in MyReader. So changing the line

p = buf.Bytes()

With

out = buf.Bytes()
copy(p, out)

did the trick

huangapple
  • 本文由 发表于 2017年5月22日 04:26:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/44101462.html
匿名

发表评论

匿名网友

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

确定