关于金融系统货币存储与计算的小Tips

1
CREATE TABLE user_wallets (
2
    id BIGSERIAL PRIMARY KEY,
3
    user_id BIGINT NOT NULL,
4
    currency_code VARCHAR(10) NOT NULL,
5
    balance NUMERIC(36, 18) NOT NULL,
6
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
7
    UNIQUE(user_id, currency_code)
8
);

1
CREATE TABLE user_wallets (
2
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
3
    user_id BIGINT NOT NULL,
4
    currency_code VARCHAR(10) NOT NULL,
5
    balance DECIMAL(36, 18) NOT NULL,
6
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
7
    UNIQUE KEY uniq_user_currency (user_id, currency_code)
8
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1
package main
2

3
import (
4
    "database/sql"
5
    "fmt"
6
    "log"
7

8
    "github.com/shopspring/decimal"
9
    // 根据数据库选择驱动
10
    _ "github.com/lib/pq"          // PostgreSQL
11
    // _ "github.com/go-sql-driver/mysql" // MySQL
12
)
13

14
// ... 数据库连接代码 ...
15

16
func addBalance(db *sql.DB, userID int64, currency string, amountStr string) error {
17
    addAmount, err := decimal.NewFromString(amountStr)
18
    if err != nil {
19
        return fmt.Errorf("invalid amount: %w", err)
20
    }
21

22
    tx, err := db.Begin()
23
    if err != nil {
24
        return err
25
    }
26
    defer tx.Rollback() // 安全起见
27

28
    var currentBalance decimal.Decimal
29
    // 注意占位符的差异: PostgreSQL ($1, $2) vs MySQL (?, ?)
30
    query := "SELECT balance FROM user_wallets WHERE user_id = $1 AND currency_code = $2 FOR UPDATE"
31
    // query := "SELECT balance FROM user_wallets WHERE user_id = ? AND currency_code = ? FOR UPDATE" // MySQL
32
    err = tx.QueryRow(query, userID, currency).Scan(&currentBalance)
33
    if err != nil {
34
        return err
35
    }
36

37
    newBalance := currentBalance.Add(addAmount)
38

39
    updateQuery := "UPDATE user_wallets SET balance = $1 WHERE user_id = $2 AND currency_code = $3"
40
    // updateQuery := "UPDATE user_wallets SET balance = ? WHERE user_id = ? AND currency_code = ?" // MySQL
41
    _, err = tx.Exec(updateQuery, newBalance, userID, currency)
42
    if err != nil {
43
        return err
44
    }
45

46
    return tx.Commit()
47
}

1
CREATE TABLE user_wallets_minimal_unit (
2
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
3
    user_id BIGINT NOT NULL,
4
    currency_code VARCHAR(10) NOT NULL,
5
    balance BIGINT NOT NULL, -- 核心变化：整数
6
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
7
    UNIQUE KEY uniq_user_currency (user_id, currency_code)
8
);

1
package main
2

3
import (
4
    "fmt"
5
    "math/big"
6
)
7

8
// 定义每种货币的精度（小数位数）
9
var currencyExponents = map[string]int{
10
    "USD": 2,
11
    "CNY": 2,
12
    "BTC": 8,
13
    "ETH": 18,
14
}
15

16
// Money 封装了金额和货币类型
17
type Money struct {
18
    Amount   int64   // 最小单位的金额
19
    Currency string // 货币代码
20
}
21

22
// NewMoney 从字符串创建 Money，确保转换过程无损
23
func NewMoney(amountStr string, currency string) (*Money, error) {
24
    exp, ok := currencyExponents[currency]
25
    if !ok {
26
        return nil, fmt.Errorf("unsupported currency: %s", currency)
27
    }
28

29
    // 使用 big.Rat 避免转换过程中的精度问题
30
    rat := new(big.Rat)
31
    _, ok = rat.SetString(amountStr)
32
    if !ok {
33
        return nil, fmt.Errorf("invalid amount string: %s", amountStr)
34
    }
35

36
    multiplier := new(big.Int).Exp(big.NewInt(10), big.NewInt(int64(exp)), nil)
37
    rat.Mul(rat, new(big.Rat).SetInt(multiplier))
38

39
    if !rat.IsInt() {
40
        return nil, fmt.Errorf("amount %s has too many decimal places for %s", amountStr, currency)
41
    }
42

43
    return &Money{
44
        Amount:   rat.Num().Int64(),
45
        Currency: currency,
46
    }, nil
47
}
48

49
// ToString 格式化为易读字符串
50
func (m *Money) ToString() string {
51
    exp := currencyExponents[m.Currency]
52
    divisor := new(big.Int).Exp(big.NewInt(10), big.NewInt(int64(exp)), nil)
53
    rat := new(big.Rat).SetFrac(big.NewInt(m.Amount), divisor)
54
    return rat.FloatString(exp)
55
}
56

57
func main() {
58
    btc, _ := NewMoney("0.00000001", "BTC")
59
    fmt.Printf("Created: %s\\n", btc.ToString()) // 输出: Created: 0.00000001
60

61
    add, _ := NewMoney("0.5", "BTC")
62
    newBtc, _ := btc.Add(add)
63
    fmt.Printf("New balance: %s\\n", newBtc.ToString()) // 输出: New balance: 0.50000001
64
}