# MySQL 8.0
docker run -d \
  --name mysql8 \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=root123 \
  -e MYSQL_DATABASE=devdb \
  -v $(pwd)/mysql/data:/var/lib/mysql \
  -v $(pwd)/mysql/conf:/etc/mysql/conf.d \
  --restart unless-stopped \
  mysql:8.0

# 查看日志
docker logs -f mysql8

# $(pwd)/mysql/conf/my.cnf
[mysqld]
# 字符集
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci

# 时区
default-time-zone    = '+8:00'

# 慢查询
slow_query_log       = ON
slow_query_log_file  = /var/lib/mysql/slow.log
long_query_time      = 1

# binlog
log_bin              = mysql-bin
binlog_format        = ROW
expire_logs_days     = 7

# InnoDB Buffer Pool（建议设为可用内存的 60-80%）
innodb_buffer_pool_size = 256M

# docker-compose.yml
version: '3.8'
services:
  mysql:
    image: mysql:8.0
    container_name: mysql8
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root123
      MYSQL_DATABASE: devdb
      MYSQL_USER: dev
      MYSQL_PASSWORD: dev123
    volumes:
      - mysql_data:/var/lib/mysql
      - ./conf/my.cnf:/etc/mysql/conf.d/my.cnf
    restart: unless-stopped
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  mysql_data:

docker-compose up -d
docker-compose exec mysql mysql -uroot -proot123

# 连接
mysql -h 127.0.0.1 -P 3306 -u root -p

# 直接执行 SQL
mysql -u root -p devdb -e "SELECT VERSION();"

# 导入 SQL 文件
mysql -u root -p devdb < dump.sql

# 常用 meta 命令
\s          # 查看连接状态
\G          # 结果竖排显示
\c          # 取消当前语句
\q          # 退出
source /tmp/init.sql  # 执行文件

mysqladmin -u root -p status          # 服务器状态
mysqladmin -u root -p processlist     # 当前连接
mysqladmin -u root -p variables       # 查看变量
mysqladmin -u root -p flush-logs      # 刷新日志
mysqladmin -u root -p shutdown        # 关闭服务器

# 备份单个数据库
mysqldump -u root -p --single-transaction devdb > devdb.sql

# 备份所有数据库
mysqldump -u root -p --all-databases --single-transaction > all.sql

# 恢复
mysql -u root -p devdb < devdb.sql

-- 查看所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE IF NOT EXISTS myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- 切换数据库
USE myapp;

-- 查看当前数据库
SELECT DATABASE();

-- 删除数据库（谨慎！）
DROP DATABASE IF EXISTS myapp;

-- 创建用户
CREATE USER 'app'@'%' IDENTIFIED BY 'AppPass123!';

-- 授权（推荐最小权限原则）
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app'@'%';

-- 查看权限
SHOW GRANTS FOR 'app'@'%';

-- 撤销权限
REVOKE DELETE ON myapp.* FROM 'app'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 修改密码（MySQL 8.0）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';

-- 删除用户
DROP USER 'app'@'%';

-- MySQL 版本
SELECT VERSION();

-- 当前时间
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP;

-- 查看系统变量
SHOW VARIABLES LIKE 'innodb_buffer%';
SHOW VARIABLES LIKE '%timeout%';

-- 查看状态变量
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 当前连接
SHOW PROCESSLIST;
-- 或更详细
SELECT * FROM information_schema.PROCESSLIST;

-- 查看表状态
SHOW TABLE STATUS FROM myapp LIKE 'orders'\G

-- 查看字符集
SHOW CHARACTER SET LIKE 'utf8%';

-- 查看排序规则
SHOW COLLATION WHERE Charset = 'utf8mb4';

-- 修改数据库
ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改列
ALTER TABLE users MODIFY name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建时自动维护时间戳
CREATE TABLE orders (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  amount     DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  status     TINYINT NOT NULL DEFAULT 0 COMMENT '0:待付款 1:已付款 2:已发货',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

go get github.com/go-sql-driver/mysql
go get github.com/jmoiron/sqlx          # 增强版 database/sql

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // DSN: user:password@tcp(host:port)/dbname?params
    dsn := "root:root123@tcp(127.0.0.1:3306)/devdb" +
        "?charset=utf8mb4" +
        "&parseTime=True" +        // 自动解析 time.Time
        "&loc=Local" +             // 使用本地时区
        "&timeout=5s" +            // 连接超时
        "&readTimeout=10s" +       // 读超时
        "&writeTimeout=5s"         // 写超时

    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 连接池配置（重要！）
    db.SetMaxOpenConns(100)                // 最大连接数
    db.SetMaxIdleConns(10)                 // 最大空闲连接数
    db.SetConnMaxLifetime(time.Hour)       // 连接最大生命周期
    db.SetConnMaxIdleTime(30 * time.Minute) // 空闲连接最大存活时间

    // 测试连通性
    if err = db.Ping(); err != nil {
        log.Fatal("ping failed:", err)
    }
    fmt.Println("MySQL connected!")

    // 查询示例
    var version string
    err = db.QueryRow("SELECT VERSION()").Scan(&version)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("MySQL version:", version)
}