Shulou(Shulou.com)06/01 Report--

Using the MySQL database

At present, the popular way of website architecture on Internet is LAMP/LNMP, in which M is MySQL. As a database, MySQL has become the back-end database storage engine of many Web development with the advantages of free, open source and easy to use.

At present, there are many drivers that support MySQL in Go. Some of them support database/sql standard, while others use their own implementation interfaces. The commonly used ones are as follows:

Https://github.com/go-sql-driver/mysql supports database/sql, all written in go.

Https://github.com/ziutek/mymysql supports database/sql and custom interfaces, all written in go.

Https://github.com/Philio/GoMySQL does not support database/sql, custom interface, all written in go.

The following example mainly takes the first driver as an example, and the main reasons for the recommendation are:

This driver is relatively new and well maintained.

Full support for database/sql interface

Supports keepalive and maintains a long connection.

In the next few sections, we will all use the same database table structure: database test, user table userinfo, associated user information table userdetail.

1CREATE TABLE `userinfo` (

2 `uid` INT (10) NOT NULL AUTO_INCREMENT

3 `username` VARCHAR (64) NULL DEFAULT NULL

4 `departname` VARCHAR (64) NULL DEFAULT NULL

5 `created` DATE NULL DEFAULT NULL

6 PRIMARY KEY (`uid`)

7)

eight

9CREATE TABLE `userdetail` (

10 `uid` INT (10) NOT NULL DEFAULT'0'

11 `intro` TEXT NULL

12 `profile` TEXT NULL

13 PRIMARY KEY (`uid`)

14)

The following example demonstrates how to use API database/sql to add, delete, modify and query database tables:

1Universe / sample code 11-15

2package main

three

4import (

5 "database/sql"

6 "fmt"

7 / / "time"

eight

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

10)

eleven

12func main () {

13 db, err: = sql.Open ("mysql", "zuolan:zuolan@/test?charset=utf8")

14 checkErr (err)

fifteen

16 / insert data

17 stmt, err: = db.Prepare ("INSERT userinfo SET username=?,departname=?,created=?")

18 checkErr (err)

nineteen

20 res, err: = stmt.Exec ("Zhang San", "R & D Department", "2017-09-09")

21 checkErr (err)

twenty-two

23 id, err: = res.LastInsertId ()

24 checkErr (err)

twenty-five

26 fmt.Println (id)

27 / / Update data

28 stmt, err = db.Prepare ("update userinfo set username=? where uid=?")

29 checkErr (err)

thirty

31 res, err = stmt.Exec ("zuolanupdate", id)

32 checkErr (err)

thirty-three

34 affect, err: = res.RowsAffected ()

35 checkErr (err)

thirty-six

37 fmt.Println (affect)

thirty-eight

39 / / query data

40 rows, err: = db.Query ("SELECT * FROM userinfo")

41 checkErr (err)

forty-two

43 for rows.Next () {

44 var uid int

45 var username string

46 var department string

47 var created string

48 err = rows.Scan (& uid, & username, & department, & created)

49 checkErr (err)

50 fmt.Println (uid)

51 fmt.Println (username)

52 fmt.Println (department)

53 fmt.Println (created)

54}

fifty-five

56 / / Delete data

57 stmt, err = db.Prepare ("delete from userinfo where uid=?")

58 checkErr (err)

fifty-nine

60 res, err = stmt.Exec (id)

61 checkErr (err)

sixty-two

63 affect, err = res.RowsAffected ()

64 checkErr (err)

sixty-five

66 fmt.Println (affect)

sixty-seven

68 db.Close ()

sixty-nine

70}

seventy-one

72func checkErr (err error) {

73 if err! = nil {

74 panic (err)

75}

76}

From the above code, we can see that Go is very convenient to manipulate the MySQL database.

The sql.Open () function is used to open a registered database driver. Mysql is registered as a database driver in go-sql-driver. The second parameter is DSN (Data Source Name), which is some database link and configuration information defined by go-sql-driver. It supports the following formats:

1user@unix (/ path/to/socket) / dbname?charset=utf8

2user:password@tcp (localhost:5555) / dbname?charset=utf8

3user:password@/dbname

4user:password@tcp ([de:ad:be:ef::ca:fe]: 80) / dbname

The db.Prepare () function returns the sql operation that is ready to be performed, and then returns the state of execution that is ready.

The db.Query () function is used to directly execute Sql and return Rows results.

The stmt.Exec () function is used to execute SQL statements prepared by stmt.

We can see that the parameters we passed in are all =? The corresponding data can be done in a way that prevents SQL injection to some extent.

Using the SQLite database

SQLite is an open source embedded relational database, which implements a SQL database engine with self-containment, zero configuration and transaction support. The utility model is characterized by high portability, convenient use, compact structure, high efficiency and reliability. Unlike other database management systems, SQLite is easy to install and run, and in most cases, you can start creating, connecting, and using databases as long as you make sure that SQLite binaries exist.

If you are looking for an embedded database project or solution, SQLite is definitely worth considering. SQLite can mean open source Access.

There are also many drivers that support sqlite in Go, but many of them do not support database/sql interface:

Https://github.com/mattn/go-sqlite3 supports the database/sql interface, based on cgo (for more information about cgo, please see the official documentation).

Https://github.com/feyeleanor/gosqlite3 does not support database/sql interface, which is based on cgo.

Https://github.com/phf/go-sqlite3 does not support database/sql interface, which is based on cgo.

At present, there are few SQLite database drivers that support database/sql, and the use of standard interfaces is conducive to migration when there is a better driver in the future, so the first driver is used later in this section.

The database table structure of the example is as follows, and the corresponding table creation SQL:

1CREATE TABLE `userinfo` (

2 `uid` INTEGER PRIMARY KEY AUTOINCREMENT

3 `username` VARCHAR (64) NULL

4 `departname` VARCHAR (64) NULL

5 `created` DATE NULL

6)

seven

8CREATE TABLE `userdeatail` (

9 `uid` INT (10) NULL

10 `intro` TEXT NULL

11 `profile` TEXT NULL

12 PRIMARY KEY (`uid`)

13)

Take a look at the following Go program how to operate database tables to add, delete, modify and query data:

1Universe / sample code 11-16

2package main

three

4import (

5 "database/sql"

6 "fmt"

7 "time"

eight

9 _ "github.com/mattn/go-sqlite3"

10)

eleven

12func main () {

13 db, err: = sql.Open ("sqlite3", ". / demo.db")

14 checkErr (err)

fifteen

16 / insert data

17 stmt, err: = db.Prepare ("INSERT INTO userinfo (username, departname, created) values")

18 checkErr (err)

nineteen

20 res, err: = stmt.Exec ("Zhang San", "R & D Department", "2017-09-09")

21 checkErr (err)

twenty-two

23 id, err: = res.LastInsertId ()

24 checkErr (err)

twenty-five

26 fmt.Println (id)

27 / / Update data

28 stmt, err = db.Prepare ("update userinfo set username=? where uid=?")

29 checkErr (err)

thirty

31 res, err = stmt.Exec ("zuolanupdate", id)

32 checkErr (err)

thirty-three

34 affect, err: = res.RowsAffected ()

35 checkErr (err)

thirty-six

37 fmt.Println (affect)

thirty-eight

39 / / query data

40 rows, err: = db.Query ("SELECT * FROM userinfo")

41 checkErr (err)

forty-two

43 for rows.Next () {

44 var uid int

45 var username string

46 var department string

47 var created time.Time

48 err = rows.Scan (& uid, & username, & department, & created)

49 checkErr (err)

50 fmt.Println (uid)

51 fmt.Println (username)

52 fmt.Println (department)

53 fmt.Println (created)

54}

fifty-five

56 / / Delete data

57 stmt, err = db.Prepare ("delete from userinfo where uid=?")

58 checkErr (err)

fifty-nine

60 res, err = stmt.Exec (id)

61 checkErr (err)

sixty-two

63 affect, err = res.RowsAffected ()

64 checkErr (err)

sixty-five

66 fmt.Println (affect)

sixty-seven

68 db.Close ()

sixty-nine

70}

seventy-one

72func checkErr (err error) {

73 if err! = nil {

74 panic (err)

75}

76}

We can see that the above code is almost the same as the code in the MySQL example, the only change is that the imported driver has changed, and then the call to sql.Open is opened in the way of SQLite.

Using the PostgreSQL database

PostgreSQL is a free object-relational database server (database management system) that is distributed under a flexible BSD-style license. It provides an alternative to other open source database systems such as MySQL and Firebird, and an option for proprietary systems such as Oracle, Sybase, IBM's DB2 and Microsoft SQL Server.

PostgreSQL is a little bigger than MySQL because it is designed to replace Oracle. So it is a wise choice to adopt PostgreSQL in enterprise applications.

MySQL is being gradually closed after it was acquired by Oracle (all versions since MySQL 5.5.31 will no longer follow the GPL protocol). In view of this, we may choose PostgreSQL instead of MySQL as the back-end database of the project in the future.

Go implements a lot of drivers that support PostgreSQL, because many foreign people use this database in their development.

Https://github.com/lib/pq supports database/sql driver, written by pure Go

Https://github.com/jbarham/gopgsqldriver supports database/sql driver, written by pure Go

Https://github.com/lxn/go-pgsql supports database/sql driver, written by pure Go.

The first driver is used in the following example because it is currently used by the most people and is active on Github.

Database creation table statement:

1CREATE TABLE userinfo

2 (

3 uid serial NOT NULL

4 username character varying (100) NOT NULL

5 departname character varying (500) NOT NULL

6 Created date

7 CONSTRAINT userinfo_pkey PRIMARY KEY (uid)

8)

9WITH (OIDS=FALSE)

ten

11CREATE TABLE userdeatail

12 (

13 uid integer

14 intro character varying

15 profile character varying

16)

17WITH (OIDS=FALSE)

Take a look at the following Go how to manipulate database tables to add, delete, modify and query data:

1Universe / sample code 11-17

2package main

three

4import (

5 "database/sql"

6 "fmt"

seven

8 _ "github.com/lib/pq"

9)

ten

11func main () {

12 db, err: = sql.Open ("postgres", "user=zuolan password=zuolan dbname=test sslmode=disable")

13 checkErr (err)

fourteen

15 / / insert data

16 stmt, err: = db.Prepare ("INSERT INTO userinfo (username,departname,created) VALUES")

17 checkErr (err)

eighteen

19 res, err: = stmt.Exec (Zhang San, R & D Department, 2017-09-09)

20 checkErr (err)

twenty-one

22 / / pg does not support this function because it does not have a self-incrementing ID like MySQL

23 / / id, err: = res.LastInsertId ()

24 / / checkErr (err)

25 / / fmt.Println (id)

twenty-six

27 var lastInsertId int

28 err = db.QueryRow ("INSERT INTO userinfo (username,departname,created) VALUES ($1, zuolan," R & D "," 2017-09-09 ") .Scan (& lastInsertId)

29 checkErr (err)

30 fmt.Println ("Last insert id =", lastInsertId)

thirty-one

thirty-two

33 / / Update data

34 stmt, err = db.Prepare ("update userinfo set username=$1 where uid=$2")

35 checkErr (err)

thirty-six

37 res, err = stmt.Exec ("zuolanupdate", 1)

38 checkErr (err)

thirty-nine

40 affect, err: = res.RowsAffected ()

41 checkErr (err)

forty-two

43 fmt.Println (affect)

forty-four

45 / / query data

46 rows, err: = db.Query ("SELECT * FROM userinfo")

47 checkErr (err)

forty-eight

49 for rows.Next () {

50 var uid int

51 var username string

52 var department string

53 var created string

54 err = rows.Scan (& uid, & username, & department, & created)

55 checkErr (err)

56 fmt.Println (uid)

57 fmt.Println (username)

58 fmt.Println (department)

59 fmt.Println (created)

60}

sixty-one

62 / / Delete data

63 stmt, err = db.Prepare ("delete from userinfo where uid=$1")

64 checkErr (err)

sixty-five

66 res, err = stmt.Exec (1)

67 checkErr (err)

sixty-eight

69 affect, err = res.RowsAffected ()

70 checkErr (err)

seventy-one

72 fmt.Println (affect)

seventy-three

74 db.Close ()

seventy-five

76}

seventy-seven

78func checkErr (err error) {

79 if err! = nil {

80 panic (err)

81}

82}

From the above code, we can see that PostgreSQL specifies the parameters to be passed through 1, 2, rather than in MySQL. In addition, the format of dsn information in sql.Open is also different from that in MySQL's driver, so please pay attention to their differences when using them.

And pg does not support the LastInsertId function, because PostgreSQL does not implement a self-increasing ID return similar to MySQL, and the other code is almost exactly the same.

NoSQL database operation

NoSQL (Not Only SQL) refers to a non-relational database. With the rise of Web2.0, the traditional relational database has been unable to cope with Web2.0 websites, especially the super-large-scale and highly concurrent Web2.0 pure dynamic websites, which has exposed a lot of insurmountable problems, while the non-relational database has developed rapidly because of its own characteristics.

As the C language of the 21st century, Go supports NOSQL very well. At present, the popular NOSQL mainly includes redis, mongoDB, Cassandra, Membase and so on. These databases have the characteristics of high performance and high concurrent reading and writing, and have been widely used in a variety of applications.

1. Redis

Redis is a key-value storage system. Similar to Memcached, it supports relatively more value types to be stored, including string (string), list (linked list), set (collection), and zset (ordered collection).

At present, Sina Weibo is the most widely used redis platform, followed by instagram, a photo social networking site acquired by Facebook. Go currently supports redis drivers as follows:

Https://github.com/garyburd/redigo

Https://github.com/go-redis/redis

Https://github.com/hoisie/redis

Https://github.com/alphazero/Go-Redis

Https://github.com/simonz05/godis

It is recommended to use the first one, the usage is not much different from the above driver, limited to the space is no longer expanded.

2. MongoDB

MongoDB is a high-performance, open source, schemalless document database, a product between relational database and non-relational database, which is the most functional and most like relational database in non-relational database. The data structure he supports is very loose, using a bjson format similar to json to store data, so more complex data types can be stored. The most important feature of Mongo is that the query language it supports is very powerful, and its syntax is somewhat similar to the object-oriented query language. It can almost achieve most of the functions similar to the single table query of relational database, and also supports the establishment of data indexing.

At present, the best driver for Go to support mongoDB is mgo (http://labix.org/mgo), which is most likely to become the official pkg. The command to install mgo is:

1go get gopkg.in/mgo.v2

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.