Go语言之进阶篇连接mysql

一、Go连接mysql

1、mysql驱动

地址：https://github.com/Go-SQL-Driver/MySQL

说明：

sql.Open()函数用来打开一个注册过的数据库驱动，Go-MySQL-Driver中注册了 mysql这个数据库驱动，第二个参数是DNS,它是Go-MySQL-Driver定义的一些数据库链接和配置信息。

2、下载mysql驱动包方法

在LiteIDE中，选中"github.com/go-sql-driver/mysql" 这行代码，再点编译--->"Get",等待一会，就会自动下载完成。

代码：

package main

import (
	"fmt"

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

或

#在cmd中运行安装命令
go get github.com/go-sql-driver/mysql

3、创建数据库

1、创建数据库
mysql> create database nulige character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nulige             |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use nulige
Database changed

2、建表
mysql> create table user_info(id int(11), username varchar(20), departname varch
ar(20), create_time timestamp default "0000-00-00 00:00:00", primary key(id));

3、查看表
mysql> show tables;
+------------------+
| Tables_in_nulige |
+------------------+
| user_info        |
+------------------+
1 row in set (0.00 sec)

4、查看表结构
mysql> desc user_info;
+-------------+-------------+------+-----+---------------------+-------+
| Field       | Type        | Null | Key | Default             | Extra |
+-------------+-------------+------+-----+---------------------+-------+
| id          | int(11)     | NO   | PRI | 0                   |       |
| username    | varchar(20) | YES  |     | NULL                |       |
| departname  | varchar(20) | YES  |     | NULL                |       |
| create_time | timestamp   | NO   |     | 0000-00-00 00:00:00 |       |
+-------------+-------------+------+-----+---------------------+-------+
4 rows in set (0.01 sec)

5、往数据库中插入数据

示例1：

package main

import (
	"database/sql"
	"fmt"

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

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

	//fmt.Println(db.Ping())  检查是否连接成功数据库
	stmt, err := db.Prepare("INSERT INTO user_info SET username=?,departname=?,create_time=?")
	if err != nil {
		fmt.Println(err)
		return
	}
	res, err := stmt.Exec("nulige", "商务部", "2019-1-28")
	id, err := res.LastInsertId()
	if err != nil {
		panic(err)
	}

	fmt.Println(id)
}

执行结果：

mysql> select * from user_info;
+----+----------+------------+---------------------+
| id | username | departname | create_time         |
+----+----------+------------+---------------------+
|  0 | nulige   | 商务部      | 2019-01-28 00:00:00 |
+----+----------+------------+---------------------+
1 row in set (0.00 sec)

示例2：登录mysql，手工插入

mysql> INSERT INTO user_info (id,username,departname,create_time) VALUES ("2","n
ulige","行政部","2019-1-11 16:23:00");
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_info;
+----+----------+------------+---------------------+
| id | username | departname | create_time         |
+----+----------+------------+---------------------+
|  0 | nulige   | 商务部    | 2019-01-28 00:00:00 |
|  1 | nulige   | 技术部    | 2019-01-28 00:00:00 |
|  2 | nulige   | 行政部      | 2019-01-11 16:23:00 |
+----+----------+------------+---------------------+
3 rows in set (0.00 sec)