1.表结构
1.文章表（低版本数据库在create_time和update_time这块可能报错，不支持一张表中插入两个timestamp默认值）
CREATE TABLE `article` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '文章id',
`category_id` bigint(20) UNSIGNED NOT NULL COMMENT '分类id',
`content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文章内容',
`title` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文章标题',
`view_count` int(255) UNSIGNED NOT NULL COMMENT '阅读次数',
`comment_count` int(255) UNSIGNED NOT NULL COMMENT '评论次数',
`username` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '作者',
`status` int(10) UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态',
`summary` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文章摘要',
`create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
`update_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_view_count`(`view_count`) USING BTREE COMMENT '阅读次数索引',
INDEX `idx_comment_count`(`comment_count`) USING BTREE COMMENT '评论数索引',
INDEX `idx_category_id`(`category_id`) USING BTREE COMMENT '分类id索引'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
2.分类表
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`category_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '分类',
`category_no` int(10) UNSIGNED NOT NULL COMMENT "分类排序",
`create time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
PRIMARY KEY(`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET =utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT=Dynamic;
3.评论表
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评论id',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '评论内容',
`username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '评论作者',
`create_time` timestamp(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '评论发布时间',
`status` int(255) UNSIGNED NOT NULL COMMENT '评论状态：0，删除;1， 正常',
`article id` bigint(20) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
4.留言表
DROP TABLE IF EXISTS `leave`;
CREATE TABLE `leave` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '留言账户名',
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '留言邮箱',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '留言内容',
`create time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
2.模型设计
1.文章
import "time"
type ArticleInfo struct {
Id int64 `db:"id"`
CategoryId int64 `db:"category_id"`
Summary string `db:"summary"`
Title string `db:"title"`
ViewCount uint32 `db:"view_count"`
CreateTime time.Time `db:"create_time"`
CommentCount uint32 `db:"comment_count"`
Username string `db:"username"`
}
type ArticleDetail struct {
ArticleInfo
Content string `db:"content"`
Category
}
type ArticleRecord struct {
ArticleInfo
Category
}
2.留言
package model
import "time"
type Leave struct {
Id int64 `db:"id"`
Content string `db:"content"`
Username string `db:"username"`
CreateTime time.Time `db:"create_time"`
Email string `db:"email"`
}
3.分类
type Category struct {
CategoryId int64 `db:"id"`
CategoryName string `db:"category_name"`
CategoryNo string `db:"category_no"`
}
4.评论
type Comment struct {
Id int64 `db:"id"`
Content string `db:"content"`
Username string `db:"username"`
CreateTime time.Time `db:"create_time"`
Status int `db:"status"`
ArticleId int64 `db:"article_id"`
}
3.数据库连接
package db
import (
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var DB *sqlx.DB
func InitDB(dns string) error {
var err error
DB, err = sqlx.Open("mysql", dns)
if err != nil {
return err
}
err = DB.Ping()
if err != nil {
return err
}
DB.SetMaxOpenConns(100)
DB.SetMaxIdleConns(16)
return nil
}
4.数据库封装
package db
import (
"dcpuffer/dcpuffer/blog/model"
"fmt"
)
func InsertArticle(article *model.ArticleDetail) (articleId int64, err error) {
if article == nil {
err = fmt.Errorf("invalid article parameter")
return
}
sqlStr := `insert into article(content, summary, title, username, category_id, view_count, comment_count) values(?,?,?,?,?,?,?)`
result, err := DB.Exec(sqlStr, article.Content, article.Summary, article.Title,
article.Username, article.Category.CategoryId, article.ArticleInfo.ViewCount,
article.ArticleInfo.CommentCount)
if err != nil {
return
}
articleId, err = result.LastInsertId()
return
}
func ListArticle(pageNum, pageSize int) (articleList []*model.ArticleInfo, err error) {
if pageNum < 0 || pageSize < 0 {
err = fmt.Errorf("invalid parameter, page_num: %d, page_size: %d", pageNum, pageSize)
return
}
sqlStr := `select id, summary, title, view_count, create_time, comment_count, username
from
article
where
status = 1
order by create_time desc
limit ?, ?`
err = DB.Select(&articleList, sqlStr, pageNum, pageSize)
return
}
package db
import (
"dcpuffer/dcpuffer/blog/model"
"fmt"
)
func ListCategory(categoryIds []int64) (categoryList []*model.Category, err error) {
sqlStr, args, err := sqlx.In("select id, category_name, category_no from category where id in (?)", categoryIds)
if err != nil {
return
}
err = DB.Select(&categoryList, sqlStr, args...)
return
}
5.测试用例
blog/dal/db/article_test.go
package db
import (
"dcpuffer/dcpuffer/blog/model"
"fmt"
"testing"
"time"
)
func init() {
dns := "root:password@tcp(192.168.226.137:3306)/test?parseTime=true"
err := InitDB(dns)
if err != nil {
panic(err)
}
}
// 文章插入接口
func TestInsertArticle(t *testing.T) {
article := &model.ArticleDetail{}
article.Content = "低版本数据库在create_time和update_time这块可能报错，不支持一张表中插入两个timestamp默认值"
article.ArticleInfo.CategoryId = 1
article.ArticleInfo.CommentCount = 0
article.ArticleInfo.CreateTime = time.Now()
article.ArticleInfo.Summary = "这是一个测试"
article.ArticleInfo.Title = "golang连接mysql时区问题"
article.ArticleInfo.Username = "puffer"
article.ArticleInfo.ViewCount = 1
article.Category.CategoryId = 1
articleId, err := InsertArticle(article)
if err != nil {
t.Errorf("insert article failed: %v\n", err)
}
fmt.Println(articleId)
}
// 文章列表接口
func TestListArticle(t *testing.T) {
articleList, err := ListArticle(0, 10)
if err != nil {
t.Errorf("List article failed: %v\n", err)
}
fmt.Println(articleList)
}
blog/dal/db/article_test.go
package db
import (
"fmt"
"testing"
)
// 分类列表接口
func TestListCategory(t *testing.T) {
categoryIds := []int64{1, 2, 3}
categoryList, err := ListCategory(categoryIds)
if err != nil {
t.Errorf("List category failed: %v\n", err)
}
fmt.Println(categoryList)
}
6.运行测试用例
cd blog/dal/db/
go test
// go test -run TestListCategory
浙公网安备 33010602011771号