golang[71]-golang操作mysql数据库

安装

https://dev.mysql.com/downloads

Installing MySQL on macOS

推荐使用dmg文件安装
https://dev.mysql.com/doc/refman/5.7/en/osx-installation.html
安装完毕有默认密码

连接

PATH="$PATH":/usr/local/mysql/bin
sudo su
mysql -u root -p 123456

create database test;
use test;
create table user_info(id int(11),username varchar(20),create_time timestamp ,primary key(id));
INSERT INTO user_info(id,username) VALUES(5,“jonson”);
select * from user_info;

create table user_info(id int(11),username varchar(20),create_time timestamp ,primary key(id));

插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

package main

import (
"database/sql"
"fmt"

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

func main() {
db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8")
if err != nil {
panic(err)
}

stmt, err := db.Prepare(`INSERT user_info (id,username) values (?,?)`)
res, err := stmt.Exec(88,"olaya")
id, err := res.LastInsertId()
fmt.Println(id)
}

删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package main

import (
"database/sql"
"fmt"

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

func main() {
db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8")
if err != nil {
panic(err)
}

stmt, err := db.Prepare("delete from user_info where id=?")

res, err := stmt.Exec(88)
id, err := res.RowsAffected()
if err != nil {
panic(err)
}

fmt.Println(id)
}

更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// mysql.go
package main

import (
"database/sql"
"fmt"

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

func main() {
db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8")
if err != nil {
panic(err)
}

stmt, err := db.Prepare("update user_info set id=? where id=?")

res, err := stmt.Exec(5, 99)
id, err := res.RowsAffected()
if err != nil {
panic(err)
}

fmt.Println(id)
}

查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
// mysql.go
package main

import (
"database/sql"
"fmt"

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

func main() {
db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8")
if err != nil {
panic(err)
}

rows, err := db.Query("SELECT * FROM user_info")
if err != nil {
panic(err)
}

for rows.Next() {
var id int
var username string
var create_time string
err = rows.Scan(&id, &username, &create_time)
if err != nil {
panic(err)
}
fmt.Println(id, username, create_time)
}

//for rows.Next() {
// var userId int
// var userName string
// var userAge int
// var userSex int

// rows.Columns()
// err = rows.Scan(&userId, &userName, &userAge, &userSex)
// checkErr(err)

// fmt.Println(userId)
// fmt.Println(userName)
// fmt.Println(userAge)
// fmt.Println(userSex)
//}
}