(errcheck and some details omitted)
I opened the database like this:
d, err = sql.Open("mysql", "user:passwd@tcp(127.0.0.1:3306)/")
_, err = d.Exec("CREATE DATABASE IF NOT EXISTS myblog")
_, err = d.Exec("USE myblog")
But if I change it to this, everything is fine:
d, err = sql.Open("mysql", "user:passwd@tcp(127.0.0.1:3306)/myblog")
I have two tables:
articles
ID, ArticleID, Title, CreateDate, PreviewContent, Content, PRIMARY KEY (ID)
categories
ArticleID, CategoryName, PRIMARY KEY (ArticleID, CategoryName)
In one function, I select 10 rows from articles:
selectAtcs := `
SELECT ArticleID, Title, CreateDate, PreviewContent
FROM articles
ORDER BY ID DESC
LIMIT ?
OFFSET ?`
var rows *sql.Rows
rows, err = db.DB.Query(selectAtcs, 10, 0)
While reading each one of them, I performed another query to get the category related to ArticleID:
for rows.Next() {
var a Article
err = rows.Scan(&a.ArticleID, &a.Title, &a.CreateDate, &a.PreviewContent)
selectCtg := `
SELECT CategoryName
FROM categories
WHERE ArticleID = ?`
var ctgRows *sql.Rows
ctgRows, err = db.DB.Query(selectCtg, a.ArticleID)
}
The last line in the above code returns an error: 1046 no database selected
sql.DB is not a single connection to the database but a pool of connections, therefore there is no guarantee that the same connection will be used in subsequent queries (unless you do them within a transaction).
From documentation:
DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines.
The sql package creates and frees connections automatically; it also maintains a free pool of idle connections. If the database has >a concept of per-connection state, such state can only be reliably observed within a transaction
Another word of warning - a connection on which you run queries that change the state of the connection (eg. running directly SQL statements such as USE <db_name>, BEGIN, etc.) will return to the pool of connections used to connect to the DB, and this state may corrupt other queries (where you expect the state to be clean).