mysql使用

sql语句

DROP TABLE IF EXISTS `person`;CREATE TABLE `person` (    `id` int(11) NOT NULL AUTO_INCREMENT,     `name` varchar(255) DEFAULT NULL,    `age` int(11) DEFAULT NULL,    `IsBoy` tinyint(4) DEFAULT NULL,     PRIMARY KEY (`id`))DEFAULT CHARSET=utf8;

sql语句使用

Query 返回的 rows,取完数据后需要调用 Close 来释放资源

package mainimport (	"fmt"	"database/sql"	_ "github.com/go-sql-driver/mysql")func main() {	db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test?charset=utf8")	if err != nil {		fmt.Println(err)		return	}	defer db.Close()	var result sql.Result	//向数据库中插入一条数据	result, err = db.Exec("insert into person(name,age,IsBoy) values(?,?, ?)", "张三 ", 19, true)	if err != nil {		fmt.Println(err)		return	}	lastId, _ := result.LastInsertId()	fmt.Println("新插入的数据 ID 为", lastId)	var row *sql.Row	//返回一行数据	row = db.QueryRow("select * from person")	var name string	var id, age int	var isBoy bool	//取数据进行显示	err = row.Scan(&id, &name, &age, &isBoy)	if err != nil {		fmt.Println(err)		return	}	fmt.Println(id, "\t", name, "\t", age, "\t", isBoy) //再插入一条数据	result, err = db.Exec("insert into person(name,age,IsBoy) values(?, ?, ?)", "王红", 18, false)	fmt.Println(" == == == == == == == == == == = ")	var rows *sql.Rows	rows, err = db.Query("select * from person")	if err != nil {		fmt.Println(err)		return	}	for rows.Next() {		var name string		var id, age int		var isBoy bool		rows.Scan(&id, &name, &age, &isBoy)		fmt.Println(id, "\t", name, "\t", age, "\t", isBoy)	}	rows.Close()	//最后,清空表	db.Exec("truncate table person")}

对 SQL 语句进行预处理

package mainimport (	"fmt"	"database/sql"	_ "github.com/go-sql-driver/mysql"	"math/rand"	"time")func main() {	db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test?charset=utf8")	if err != nil {		fmt.Println(err)		return	}	defer db.Close()	var smt *sql.Stmt	smt, err = db.Prepare("insert into person(name,age,IsBoy) values(?,?, ?)")	if err != nil {		fmt.Println(err)		return	}	fmt.Println("开始插入数据....", time.Now())	r := rand.New(rand.NewSource(time.Now().UnixNano()))	for i := 0; i < 10000; i++ {		_, err = smt.Exec(fmt.Sprintf("张%d", r.Int()), r.Intn(50), r.Intn(100) % 2)		if err != nil {			fmt.Println(err)			return		}	}	fmt.Println("数据插入完成!", time.Now())}

事务

package mainimport (	"fmt"	"database/sql"	_ "github.com/go-sql-driver/mysql")func main() {	db,err:=sql.Open("mysql","root:root@tcp(127.0.0.1:3306)/test?charset=utf8")	if err != nil {		fmt.Println(err)		return	}	defer db.Close()	var trans *sql.Tx	trans, err = db.Begin()	if err != nil {		fmt.Println(err)		return	}	result,err := trans.Exec("insert into person(name,age,IsBoy)values('张三',99,false)")	if err != nil {		fmt.Println(err)		trans.Rollback()	}	fmt.Println(result.LastInsertId())	trans.Commit()	//最后,清空表	db.Exec("truncate table person")}