英文:
Using RegisterReaderHandler to insert rows in mysql
问题
我正在使用go-sql-driver
从go
连接到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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论