go mysql id为0_go 语言中mysql操作200万数据时应该如何写？

在写一个将 discuzx 的 post 数据的 bbcode 转换成 html 的功能。

但是转换过程中，越到后面，越卡了。

本来想学学并发的，无奈不会啊。。。太菜了。

注释掉的是想要弄的。。。

求个解决方案。代码如下。

package main

import (

"database/sql"

"fmt"

"github.com/frustra/bbcode"

_ "github.com/go-sql-driver/mysql"

//"time"

//"runtime"

"strconv"

)

const (

XnFromPost = "bbs_post"

XnTopost = "bbs_post"

DxFromPost = "pre_forum_post"

)

type Hostinfo struct {

DBUser,

DBPassword,

DBname,

DBHost,

DBPort,

DBChar string

}

type post struct {

pid int

message string

}

var OldDB, NewDB *sql.DB

/**

设置数据库，并连接数据库

*/

func SetDB() (olddata, newdata *sql.DB) {

var localhost, remotehost Hostinfo

localhost = Hostinfo{

"root",

"123456",

"xiuno",

"",

"3306",

"utf8",

}

remotehost = Hostinfo{

"root",

"123456",

"gxvtc",

"",

"3306",

"utf8",

}

olddata, _ = connMysql(&localhost)

newdata, _ = connMysql(&remotehost)

return olddata, newdata

}

/**

连接数据库

*/

func connMysql(host *Hostinfo) (*sql.DB, error) {

//db, err := sql.Open("mysql", "root:123456@/"+dbs+"?charset=utf8")

if host.DBHost != "" {

host.DBHost = "tcp(" + host.DBHost + ":" + host.DBPort + ")"

fmt.Println(host.DBHost)

}

db, err := sql.Open("mysql", host.DBUser+":"+host.DBPassword+"@"+host.DBHost+"/"+host.DBname+"?charset="+host.DBChar)

return db, err

}

func main() {

OldDB, NewDB = SetDB()

defer OldDB.Close()

defer NewDB.Close()

updatePost()

}

func updatePost() {

const total = 100

//selectPost := "SELECT pid,message FROM " + XnFromPost + " ORDER BY pid ASC LIMIT 10"

selectPost := "SELECT pid,message FROM " + DxFromPost + " ORDER BY pid ASC "

/*

Data, err := OldDB.Query(selectPost)

if err != nil {

fmt.Println(err.Error())

}*/

fmt.Println(selectPost)

updatePost := "UPDATE " + XnTopost + " SET message = ? WHERE pid = ? limit 1"

fmt.Println(updatePost)

stmt, err := OldDB.Prepare(updatePost)

//insertPost := "INSERT INTO " + XnTopost + " (message,pid) VALUES (?,?)"

//fmt.Println(insertPost)

//stmt, err := OldDB.Prepare(insertPost)

if err != nil {

fmt.Println(err.Error())

}

//mypost := make(map[int]post)

i := 0

for {

tmpNum := i * total

i++

tmpSQL := selectPost + " LIMIT " + strconv.Itoa(tmpNum) + "," + strconv.Itoa(total)

fmt.Println(tmpSQL)

Data, err := NewDB.Query(tmpSQL)

//Data, err := NewDB.Query(tmpSQL)

if err != nil {

fmt.Println(err.Error())

}

tag := false

//使用并发的方式

for Data.Next() {

tag = true

var pid int

var msg string

Data.Scan(&pid, &msg)

fmt.Println(pid)

//bbcode转码html

compiler := bbcode.NewCompiler(true, true)

msg = compiler.Compile(msg)

//mypost[pid] = post{pid, msg}

_, err = stmt.Exec(msg, pid)

if err != nil {

fmt.Println("pid: ", pid, err.Error())

}

}

if tag == false {

fmt.Println("没有数据了...")

break

}

}

/*

//直接查找并更新

for Data.Next() {

var pid int

var msg string

Data.Scan(&pid, &msg)

//bbcode转码html

compiler := bbcode.NewCompiler(true, true)

msg = compiler.Compile(msg)

mypost[pid] = post{pid, msg}

//fmt.Println(mypost)

fmt.Println(pid)

_, err = stmt.Exec(msg, pid)

if err != nil {

fmt.Println("pid: ", pid, err.Error())

}

}

*/

/*

//使用并发的方式

for Data.Next() {

var pid int

var msg string

Data.Scan(&pid, &msg)

//bbcode转码html

compiler := bbcode.NewCompiler(true, true)

msg = compiler.Compile(msg)

mypost[pid] = post{pid, msg}

}

runtime.GOMAXPROCS(runtime.NumCPU())

c := make(chan post)

for _, v := range mypost {

go ShowMsg(c, v)

/*

go func() {

fmt.Println(v.pid)

c

}()

//time.Sleep(2 * time.Second)

//fmt.Println(v.pid)

}

for _, _ = range mypost {

fmt.Println(&c)

} */

}

func ShowMsg(c chan post, mypost post) {

//fmt.Println(mypost.pid)

c

}