# How to use MySQL

The LAMP stack has been very popular on the internet in recent years, and the M in LAMP stand for MySQL. MySQL is famous because it's open source and easy to use. As such, it has become the de-facto database in the back-ends of many websites.

## MySQL drivers

There are a couple of drivers that support MySQL in Go. Some of them implement the `database/sql` interface, and others use their own interface standards.

* <https://github.com/go-sql-driver/mysql> supports `database/sql`, written in pure Go.
* <https://github.com/ziutek/mymysql> supports `database/sql` and user defined interfaces, written in pure Go.

I'll use the first driver in the following examples (I use this one in my personal projects too), and I also recommend that you use it for the following reasons:

* It's a new database driver and supports more features.
* It fully supports `database/sql` interface standards.
* Supports keep-alive, long connections with thread-safety.

## Samples

In the following sections, I'll use the same database table structure for different databases, then create SQL as follows:

```sql
    CREATE TABLE `userinfo` (
        `uid` INT(10) NOT NULL AUTO_INCREMENT,
        `username` VARCHAR(64) NULL DEFAULT NULL,
        `department` VARCHAR(64) NULL DEFAULT NULL,
        `created` DATE NULL DEFAULT NULL,
        PRIMARY KEY (`uid`)
    );
```

The following example shows how to operate on a database based on the `database/sql` interface standards.

```go
    package main

    import (
        _ "github.com/go-sql-driver/mysql"
        "database/sql"
        "fmt"
    )

    func main() {
        db, err := sql.Open("mysql", "astaxie:astaxie@/test?charset=utf8")
        checkErr(err)

        // insert
        stmt, err := db.Prepare("INSERT userinfo SET username=?,department=?,created=?")
        checkErr(err)

        res, err := stmt.Exec("astaxie", "研发部门", "2012-12-09")
        checkErr(err)

        id, err := res.LastInsertId()
        checkErr(err)

        fmt.Println(id)
        // update
        stmt, err = db.Prepare("update userinfo set username=? where uid=?")
        checkErr(err)

        res, err = stmt.Exec("astaxieupdate", id)
        checkErr(err)

        affect, err := res.RowsAffected()
        checkErr(err)

        fmt.Println(affect)

        // query
        rows, err := db.Query("SELECT * FROM userinfo")
        checkErr(err)

        for rows.Next() {
            var uid int
            var username string
            var department string
            var created string
            err = rows.Scan(&uid, &username, &department, &created)
            checkErr(err)
            fmt.Println(uid)
            fmt.Println(username)
            fmt.Println(department)
            fmt.Println(created)
        }

        rows.Close()

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

        res, err = stmt.Exec(id)
        checkErr(err)

        affect, err = res.RowsAffected()
        checkErr(err)

        fmt.Println(affect)

        db.Close()

    }

    func checkErr(err error) {
        if err != nil {
            panic(err)
        }
    }
```

Let me explain a few of the important functions here:

* `sql.Open()` opens a registered database driver. The Go-MySQL-Driver registered the mysql driver here. The second argument is the DSN (Data Source Name) that defines information pertaining to the database connection. It supports following formats:

  ```
    user@unix(/path/to/socket)/dbname?charset=utf8
    user:password@tcp(localhost:5555)/dbname?charset=utf8
    user:password@/dbname
    user:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname
  ```
* `db.Prepare()` returns a SQL operation that is going to be executed. It also returns the execution status after executing SQL.
* `db.Query()` executes SQL and returns a Rows result.
* `stmt.Exec()` executes SQL that has been prepared and stored in Stmt.

Note that we use the format `=?` to pass arguments. This is necessary for preventing SQL injection attacks.

## Links

* [Directory](/preface.md)
* Previous section: [database/sql interface](/05.0/05.1.md)
* Next section: [SQLite](/05.0/05.3.md)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://golang.repodevs.com/05.0/05.2.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.