数据库操作
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 

955 lines
20 KiB

package dbquery
import (
"database/sql"
"log"
"strconv"
"errors"
"strings"
"time"
"git.tetele.net/tgo/helper"
_ "github.com/go-sql-driver/mysql"
)
/**
* 创建数据
*/
func Insert(dbName, table string, data map[string]string) (int64, error) {
var insertId int64
var err error
if dbName == "" && table == "" {
return insertId, errors.New("没有数据表")
}
if strings.Contains(table, "select ") {
dbName = table
} else {
dbName = getTableName(dbName, table)
}
if len(data) < 1 {
return insertId, errors.New("没有要写入的数据")
}
keyList := make([]string, len(data))
keyStr := make([]string, len(data))
valueList := make([]interface{}, len(data))
var i int = 0
for key, value := range data {
keyList[i] = key
keyStr[i] = "?"
valueList[i] = value
i++
}
result, err := DB.Exec("insert into "+dbName+" ("+strings.Join(keyList, ",")+") value("+strings.Join(keyStr, ",")+")", valueList...)
if err != nil {
log.Println("ERROR|插入", dbName, "数据失败,", err)
return insertId, err
} else {
insertId, _ = result.LastInsertId()
time.Sleep(time.Second * 2)
return insertId, nil
}
}
/**
* 修改数据
*/
func Update(dbName, table string, data map[string]string, where map[string]string) (int64, error) {
var rowsAffected int64
var err error
if dbName == "" && table == "" {
return rowsAffected, errors.New("没有数据表")
}
if strings.Contains(table, "select ") {
dbName = table
} else {
dbName = getTableName(dbName, table)
}
if len(data) < 1 {
return rowsAffected, errors.New("同有更新的数据")
}
if len(where) < 1 {
return rowsAffected, errors.New("没有更新条件")
}
keyList := make([]string, len(data))
valueList := make([]interface{}, len(data), len(data)+len(where))
whereStr := make([]string, len(where))
var i int = 0
empty := false
for key, value := range data {
keyList[i] = key + "=?"
valueList[i] = value
i++
}
i = 0
for key, value := range where {
if value == "" {
empty = true
break
}
whereStr[i] = key + "=?"
valueList = append(valueList, value)
i++
}
if empty {
log.Println("ERROR|修改数据表", dbName, "时条件中有空数据,条件:", where, "数据:", data)
return rowsAffected, errors.New("条件中有空数据")
}
result, err := DB.Exec("update "+dbName+" set "+strings.Join(keyList, " , ")+" where "+strings.Join(whereStr, " and "), valueList...)
if err != nil {
log.Println("ERROR|修改", dbName, "数据失败,", err)
return rowsAffected, err
} else {
rowsAffected, _ = result.RowsAffected()
return rowsAffected, nil
}
}
/**
* 删除数据
* @param count 删除数量
*/
func Delete(dbName, table string, data map[string]string, del_count ...string) (int64, error) {
var count int64
var err error
if dbName == "" && table == "" {
return count, errors.New("没有数据表")
}
if strings.Contains(table, "select ") {
dbName = table
} else {
dbName = getTableName(dbName, table)
}
if len(data) < 1 {
return count, errors.New("没有要删除的数据")
}
keyList := make([]string, len(data))
valueList := make([]interface{}, len(data))
var i int = 0
empty := false
for key, value := range data {
if value == "" {
empty = true
break
}
keyList[i] = key + "=?"
valueList[i] = value
i++
}
if empty {
log.Println("ERROR|删除数据表", dbName, "时条件中有空数据,条件:", data)
return count, errors.New("条件中有空数据")
}
var limitStr string = ""
if len(del_count) > 0 {
limitStr = " limit " + del_count[0]
}
result, err := DB.Exec("delete from "+dbName+" where "+strings.Join(keyList, " and ")+limitStr, valueList...)
if err != nil {
log.Println("ERROR|删除", dbName, "数据失败,", err)
return count, err
} else {
count, _ = result.RowsAffected()
return count, nil
}
}
/**
* 查找一条记录
* @param dbName 数据表名
* @param title 查询字段名
*/
func GetData(dbName, table string, title string, where map[string]string, limit map[string]string) (int, map[string]string, error) {
var count int = 0
info := make(map[string]string)
if dbName == "" && table == "" {
return count, info, errors.New("没有数据表")
}
dbName = getTableName(dbName, table)
if len(title) < 1 {
return count, info, errors.New("没有指定查询内容")
}
var limitStr string = ""
if limit != nil && len(limit) > 0 {
var from string = "0" //开始
if _, ok := limit["order"]; ok {
limitStr += " order by " + limit["order"]
}
if _, ok := limit["from"]; ok {
from = limit["from"]
}
limitStr += " limit " + from + ",1"
} else {
limitStr = " limit 1"
}
if len(where) < 1 {
return count, info, errors.New("Query condition is empty")
}
keyList := make([]string, len(where))
valueList := make([]interface{}, len(where))
var i int = 0
empty := false
for key, value := range where {
if value == "" {
empty = true
break
}
keyList[i] = key + " = ? "
valueList[i] = value
i++
}
if empty {
return count, info, errors.New("Query condition is empty")
}
var rows *sql.Rows
var err error
var queryNum int = 0
for queryNum < 3 { //如发生错误,继续查询3次,防止数据库连接断开问题
rows, err = DB.Query("SELECT "+title+" FROM "+dbName+" where "+strings.Join(keyList, " and ")+" "+limitStr, valueList...)
if err == nil {
break
} else {
log.Println("ERROR", "Query from", dbName, "failed,", err, "| SELECT "+title+" FROM "+dbName+" where "+strings.Join(keyList, " and ")+" "+limitStr)
time.Sleep(time.Millisecond * 500)
}
queryNum++
}
defer rows.Close()
if err != nil {
return count, info, err
}
columns, _ := rows.Columns()
scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for i := range values {
scanArgs[i] = &values[i]
}
var index string
var rowerr error
for rows.Next() {
rowerr = rows.Scan(scanArgs...)
if rowerr == nil {
for i, col := range values {
if col != nil {
index = helper.StrFirstToUpper(columns[i])
info[index] = helper.ToString(col)
}
}
count++
} else {
log.Println("ERROR", "rows scan error", rowerr, dbName, keyList, valueList)
}
}
if rowerr != nil {
return count, info, rowerr
}
return count, info, nil
}
/**
* 查找一条记录
* @param dbName 数据表名
* @param title 查询字段名
*/
func GetRow(dbName, table_name, alias string, titles string, join [][]string, where, where_or []string, valueList []interface{}, orderby, groupby string, debug bool) (int, map[string]string, error) {
var count int = 0
info := make(map[string]string)
if dbName == "" && table_name == "" {
return count, info, errors.New("没有数据表")
}
table := ""
if strings.Contains(table_name, "select ") {
table = table_name
} else {
table = getTableName(dbName, table_name)
}
var sql_str, title string
if titles != "" {
title = titles
} else {
title = "*"
}
sql_str = helper.StringJoin("/*slave*/ select ", title)
if alias != "" {
table = helper.StringJoin(table, " as ", alias)
}
sql_str = helper.StringJoin(sql_str, " from ", table)
if len(join) > 0 {
for _, joinitem := range join {
if len(joinitem) < 2 {
continue
}
if len(joinitem) == 3 {
sql_str = helper.StringJoin(sql_str, " ", joinitem[2], " join ", getTableName(dbName, joinitem[0]), " on ", joinitem[1])
} else { //默认左连接
sql_str = helper.StringJoin(sql_str, " left join ", getTableName(dbName, joinitem[0]), " on ", joinitem[1])
}
}
}
if len(where) > 0 || len(where_or) > 0 {
sql_str = helper.StringJoin(sql_str, " where ")
}
if len(where) > 0 {
sql_str = helper.StringJoin(sql_str, " (", strings.Join(where, " and "), " ) ")
}
if len(where_or) > 0 {
if len(where) > 0 {
sql_str = helper.StringJoin(sql_str, " or ", strings.Join(where_or, " or "))
} else {
sql_str = helper.StringJoin(sql_str, strings.Join(where_or, " or "))
}
}
if groupby != "" {
sql_str = helper.StringJoin(sql_str, " group by ", groupby)
}
if orderby != "" {
sql_str = helper.StringJoin(sql_str, " order by ", orderby)
}
if debug {
log.Println("query sql:", sql_str, valueList)
}
condition_len := 0 //所有条件数
for _, ch2 := range sql_str {
if string(ch2) == "?" {
condition_len++
}
}
if condition_len != len(valueList) {
return 0, nil, errors.New("参数错误,条件值错误")
}
var rows *sql.Rows
var err error
var queryNum int = 0
sql_str = helper.StringJoin(sql_str, " limit 1")
var db *sql.DB
if SLAVER_DB != nil {
db = SLAVER_DB
} else {
db = DB
}
for queryNum < 2 { //如发生错误,继续查询2次,防止数据库连接断开问题
rows, err = db.Query(sql_str, valueList...)
if err == nil {
break
} else {
log.Println(err)
time.Sleep(time.Millisecond * 500)
}
queryNum++
}
if err != nil {
rows.Close()
return count, info, err
}
columns, _ := rows.Columns()
scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for i := range values {
scanArgs[i] = &values[i]
}
var index string
var rowerr error
for rows.Next() {
rowerr = rows.Scan(scanArgs...)
if rowerr == nil {
for i, col := range values {
if col != nil {
index = helper.StrFirstToUpper(columns[i])
info[index] = helper.ToString(col)
}
}
count++
} else {
log.Println("ERROR", rowerr)
}
}
rows.Close()
if rowerr != nil {
return count, info, rowerr
}
return count, info, nil
}
/**
* 查找多条记录
* @param dbName 数据表名
* @param title 查询字段名
*/
func FetchRows(dbName, table_name, alias string, titles string, join [][]string, where, where_or []string, valueList []interface{}, orderby, groupby string, page int, page_size int, debug bool) (int, []map[string]string, error) {
var count int = 0
list := make([]map[string]string, 0)
if dbName == "" && table_name == "" {
return count, list, errors.New("没有数据表")
}
table := ""
if strings.Contains(table_name, "select ") {
table = table_name
} else {
table = getTableName(dbName, table_name)
}
var sql_str, title string
if titles != "" {
title = titles
} else {
title = "*"
}
sql_str = helper.StringJoin("/*slave*/ select ", title)
if alias != "" {
table = helper.StringJoin(table, " as ", alias)
}
sql_str = helper.StringJoin(sql_str, " from ", table)
if len(join) > 0 {
for _, joinitem := range join {
if len(joinitem) < 2 {
continue
}
if len(joinitem) == 3 {
sql_str = helper.StringJoin(sql_str, " ", joinitem[2], " join ", getTableName(dbName, joinitem[0]), " on ", joinitem[1])
} else { //默认左连接
sql_str = helper.StringJoin(sql_str, " left join ", getTableName(dbName, joinitem[0]), " on ", joinitem[1])
}
}
}
if len(where) > 0 || len(where_or) > 0 {
sql_str = helper.StringJoin(sql_str, " where ")
}
if len(where) > 0 {
sql_str = helper.StringJoin(sql_str, " (", strings.Join(where, " and "), " ) ")
}
if len(where_or) > 0 {
if len(where) > 0 {
sql_str = helper.StringJoin(sql_str, " or ", strings.Join(where_or, " or "))
} else {
sql_str = helper.StringJoin(sql_str, strings.Join(where_or, " or "))
}
}
if groupby != "" {
sql_str = helper.StringJoin(sql_str, " group by ", groupby)
}
if orderby != "" {
sql_str = helper.StringJoin(sql_str, " order by ", orderby)
}
if page > 0 || page_size > 0 {
if page < 1 {
page = 1
}
if page_size < 1 {
page_size = 10
}
from := strconv.Itoa((page - 1) * page_size)
offset := strconv.Itoa(page_size)
if from != "" && offset != "" {
sql_str = helper.StringJoin(sql_str, " limit ", from, " , ", offset)
}
}
if debug {
log.Println("query sql:", sql_str, valueList)
}
condition_len := 0 //所有条件数
for _, ch2 := range sql_str {
if string(ch2) == "?" {
condition_len++
}
}
if condition_len != len(valueList) {
return 0, list, errors.New("参数错误,条件值错误")
}
var db *sql.DB
if SLAVER_DB != nil {
db = SLAVER_DB
} else {
db = DB
}
var rows *sql.Rows
var err error
var queryNum int = 0
for queryNum < 2 { //如发生错误,继续查询2次,防止数据库连接断开问题
rows, err = db.Query(sql_str, valueList...)
if err == nil {
break
} else {
log.Println(err)
time.Sleep(time.Millisecond * 500)
}
queryNum++
}
if err != nil {
rows.Close()
return 0, list, err
}
columns, _ := rows.Columns()
scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for i := range values {
scanArgs[i] = &values[i]
}
var index string
var rowerr error
var info map[string]string
for rows.Next() {
rowerr = rows.Scan(scanArgs...)
info = make(map[string]string)
if rowerr == nil {
for i, col := range values {
if col != nil {
index = helper.StrFirstToUpper(columns[i])
info[index] = helper.ToString(col)
}
}
count++
} else {
log.Println("ERROR", rowerr)
}
if len(info) > 0 {
list = append(list, info)
}
}
rows.Close()
return count, list, nil
}
func GetInfo(dbName, table string, title string, where map[string]string) (map[string]string, error) {
count, info, gzErr := GetData(dbName, table, title, where, nil)
if gzErr != nil {
return info, gzErr
} else {
if count < 1 {
return info, errors.New("No data")
}
return info, nil
}
}
/**
* 查询列表
* 2018/04/19
*/
func GetList(dbName, table string, title string, where map[string]string, limit map[string]string) ([]map[string]string, error) {
var list []map[string]string
if dbName == "" && table == "" {
return list, errors.New("没有数据表")
}
if strings.Contains(table, "select ") {
dbName = table
} else {
dbName = getTableName(dbName, table)
}
var rows *sql.Rows
var err error
var queryNum int = 0
var limitStr string = ""
if len(limit) > 0 {
var offset string = "0" //偏移量,个数
var from string = "" //开始
if _, ok := limit["order"]; ok {
limitStr += " order by " + limit["order"]
}
if _, ok := limit["offset"]; ok {
offset = limit["offset"]
}
if _, ok := limit["from"]; ok {
from = limit["from"]
}
if offset != "0" && from != "" {
limitStr += " limit " + from + "," + offset
}
}
if len(where) > 0 {
valueList := make([]interface{}, len(where))
whereStr := make([]string, len(where))
i := 0
var keys []string
for key, value := range where {
key = strings.Trim(key, " ")
value = strings.Trim(value, " ")
if value == "" || key == "" {
continue
// return list, errors.New("Query condition is empty")
}
if strings.Contains(key, " ") {
//key中包含空格,判断是否不等于条件
keys = strings.Split(key, " ")
if helper.IsInStringArray(judg(), strings.Trim(keys[1], " ")) {
whereStr[i] = strings.Trim(keys[0], " ") + " " + strings.Trim(keys[1], " ") + " ?"
}
} else {
whereStr[i] = key + " = ?"
valueList[i] = value
}
i++
}
if len(whereStr) == 0 || len(valueList) == 0 {
return list, errors.New("Query condition is empty")
}
for queryNum < 5 { //如发生错误,继续查询5次,防止数据库连接断开问题
rows, err = DB.Query("select "+title+" from "+dbName+" where "+strings.Join(whereStr, " and ")+" "+limitStr, valueList...)
if err == nil {
break
} else {
time.Sleep(time.Millisecond * 500)
}
queryNum++
}
} else {
for queryNum < 5 { //如发生错误,继续查询5次,防止数据库连接断开问题
rows, err = DB.Query("select " + title + " from " + dbName + " " + limitStr)
if err == nil {
break
} else {
time.Sleep(time.Millisecond * 500)
}
queryNum++
}
}
if err != nil {
return list, err
}
defer rows.Close()
columns, _ := rows.Columns()
scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for i := range values {
scanArgs[i] = &values[i]
}
var record map[string]string
var index string
for rows.Next() {
//将行数据保存到record字典
err = rows.Scan(scanArgs...)
record = make(map[string]string)
for i, col := range values {
if col != nil {
index = helper.StrFirstToUpper(columns[i])
record[index] = helper.ToString(col)
}
}
list = append(list, record)
}
return list, nil
}
/**
* 查询总数
* 2018/04/19
*/
func GetTotal(dbName, table string, args ...string) (total int) {
if dbName == "" && table == "" {
return
}
if strings.Contains(table, "select ") {
dbName = table
} else {
dbName = getTableName(dbName, table)
}
var title string = "*"
if len(args) > 0 {
title = args[0]
}
var rows *sql.Rows
var err error
var queryNum int = 0
for queryNum < 5 { //如发生错误,继续查询5次,防止数据库连接断开问题
rows, err = DB.Query("select count(" + title + ") as count from " + dbName + " limit 1")
if err == nil {
break
} else {
time.Sleep(time.Millisecond * 500)
}
queryNum++
}
if err != nil {
log.Println("ERROR|get", dbName, "total error", err)
return
}
defer rows.Close()
var count int
for rows.Next() {
//将行数据保存到record字典
err = rows.Scan(&count)
if err != nil {
log.Println("ERROR|get", dbName, "total error", err)
} else {
total = count
}
}
return
}
/**
* 查询总数
* 2020/06/04
*/
func GetCount(dbName, table string, where map[string]string, args ...string) (total int) {
if dbName == "" && table == "" {
return
}
if strings.Contains(table, "select ") {
dbName = table
} else {
dbName = getTableName(dbName, table)
}
var title string = "*"
if len(title) > 0 {
title = args[0]
}
var rows *sql.Rows
var err error
var queryNum int = 0
if len(where) > 0 {
valueList := make([]interface{}, len(where))
whereStr := make([]string, len(where))
i := 0
var keys []string
for key, value := range where {
key = strings.Trim(key, " ")
value = strings.Trim(value, " ")
if value == "" || key == "" {
continue
// return list, errors.New("Query condition is empty")
}
if strings.Contains(key, " ") {
//key中包含空格,判断是否不等于条件
keys = strings.Split(key, " ")
if helper.IsInStringArray(judg(), strings.Trim(keys[1], " ")) {
whereStr[i] = strings.Trim(keys[0], " ") + " " + strings.Trim(keys[1], " ") + " ?"
}
} else {
whereStr[i] = key + " = ?"
valueList[i] = value
}
i++
}
for queryNum < 5 { //如发生错误,继续查询5次,防止数据库连接断开问题
rows, err = DB.Query("select count("+title+") as count from "+dbName+" where "+strings.Join(whereStr, " and ")+" limit 1", valueList...)
if err == nil {
break
} else {
time.Sleep(time.Millisecond * 500)
}
queryNum++
}
} else {
for queryNum < 5 { //如发生错误,继续查询5次,防止数据库连接断开问题
rows, err = DB.Query("select count(" + title + ") as count from " + dbName + " limit 1")
if err == nil {
break
} else {
time.Sleep(time.Millisecond * 500)
}
queryNum++
}
}
if err != nil {
log.Println("ERROR|get", dbName, "count error", err)
return
}
defer rows.Close()
var count int
for rows.Next() {
//将行数据保存到record字典
err = rows.Scan(&count)
if err != nil {
log.Println("ERROR|get", dbName, "count error", err)
} else {
total = count
}
}
return
}
func DoQuery(args ...interface{}) ([]map[string]string, error) {
var list []map[string]string
if len(args) < 1 {
return list, errors.New("Query condition is empty")
}
queryStr := helper.ToString(args[0])
if queryStr == "" {
return list, errors.New("Query condition is empty")
}
var rows *sql.Rows
var err error
var queryNum int = 0
for queryNum < 3 { //如发生错误,继续查询5次,防止数据库连接断开问题
if len(args) > 1 {
rows, err = DB.Query(queryStr, args[1:]...) //strings.Join(args[1:], ",")
if err != nil {
log.Println("ERROR|DoQuery error:", err)
}
} else {
rows, err = DB.Query(queryStr)
if err != nil {
log.Println("ERROR|DoQuery error:", err)
}
}
if err == nil {
break
} else {
time.Sleep(time.Millisecond * 500)
}
queryNum++
}
if err != nil {
return list, err
}
defer rows.Close()
columns, _ := rows.Columns()
scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for i := range values {
scanArgs[i] = &values[i]
}
var record map[string]string
var index string
for rows.Next() {
//将行数据保存到record字典
err = rows.Scan(scanArgs...)
record = make(map[string]string)
for i, col := range values {
if col != nil {
index = helper.StrFirstToUpper(columns[i])
record[index] = helper.ToString(col)
}
}
list = append(list, record)
}
return list, nil
}