Mysql

数据库操作

批量 kill 掉查询

有时候需要批量 kill 掉查询进程，有几种方式，比如生成 sql 文件执行：

  1. mysql> select concat('KILL ',id,';') from information_schema.processlist
  2. where user='root' and time > 200 into outfile '/tmp/a.txt';
  4. mysql> source /tmp/a.txt;

有时候没法生成文件（权限原因），可以直接生成 sql 语句 copy 下来复制到命令行也可以，或者连接成一行方便复制：

  1. mysql > select concat('KILL ',id,';') from information_schema.processlist where db='dbname';`
  3. mysql > select GROUP_CONCAT(stat SEPARATOR ' ') from (select concat('KILL ',id,';') as stat from information_schema.processlist where db='dbname') as stats;
  5. # 按客户端 IP 分组，看哪个客户端的链接数最多
  6. select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from processlist ) as connect_info group by client_ip order by client_num desc;
  8. # 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
  9. select * from information_schema.processlist where Command != 'Sleep' order by Time desc;
  11. # 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
  12. select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

也可以通过 python 脚本来完成，原理也是查询进程 id 然后删除：

  1. # https://stackoverflow.com/questions/1903838/how-do-i-kill-all-the-processes-in-mysql-show-processlist
  3. import pymysql # pip install pymysql
  5. connection = pymysql.connect(host='',
  6.                              user='',
  7.                              db='',
  8.                              password='',
  9.                              cursorclass=pymysql.cursors.DictCursor)
  11. with connection.cursor() as cursor:
  12.     cursor.execute('SHOW PROCESSLIST')
  13.     for item in cursor.fetchall():
  14.         if item.get('db') == 'dbname': # 过滤条件
  15.             _id = item.get('Id')
  16.             print('kill %s' % item)
  17.             cursor.execute('kill %s', _id)
  18.     connection.close()

删除大表(借助一个临时表)

  1. # https://stackoverflow.com/questions/879327/quickest-way-to-delete-enormous-mysql-table
  2. CREATE TABLE new_foo LIKE foo;
  3. RENAME TABLE foo TO old_foo, new_foo TO foo;
  4. DROP TABLE old_foo;

统计表的大小并排序

  1. # https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database
  2. SELECT
  3.      table_schema as `Database`,
  4.      table_name AS `Table`,
  5.      round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
  6. FROM information_schema.TABLES
  7. ORDER BY (data_length + index_length) DESC;

查看表信息

  1. mysql > show table status;
  2. mysql > show table status where Rows>100000;

纵向显示

有时候表字段比较多的时候，查询结果显示会很乱，可以使用竖屏显示的方式，结尾加上 \G

  1. mysql > select * from user limit 10 \G

导出和导入表的数据

  1. shell > mysqldump -u user -h host -p pass db_name table_name > out.sql
  2. mysql > source /path/to/out.sql

Python Mysql 操作

Sqlalchemy 示例

  1. # -*- coding: utf-8 -*-
  3. """
  4. sqlalchemy 快速读取 mysql 数据示例
  6. pip install SQLAlchemy -i https://pypi.doubanio.com/simple --user
  7. pip install pymysql -i https://pypi.doubanio.com/simple --user
  8. """
  10. import sqlalchemy as db
  12. """
  13. # 本机 mysql 创建一个测试表
  15. CREATE TABLE `area_code` (
  16.   `id` int(11) NOT NULL AUTO_INCREMENT,
  17.   `code` bigint(12) NOT NULL DEFAULT '0' COMMENT '行政区划代码',
  18.   `name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',
  19.   PRIMARY KEY (`id`),
  20.   KEY `idx_code` (`code`)
  21. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  23. """
  25. def sqlalchemy_demo():
  26.     # https://towardsdatascience.com/sqlalchemy-python-tutorial-79a577141a91
  27.     url = "mysql+pymysql://root:wnnwnn@127.0.0.1:3306/testdb"  # 测试地址
  28.     engine = db.create_engine(url)
  29.     connection = engine.connect()
  30.     metadata = db.MetaData()
  31.     table = db.Table('area_code', metadata, autoload=True, autoload_with=engine)
  33.     # 插入单个数据
  34.     query = db.insert(table).values(code=10010, name="北京")
  35.     connection.execute(query)
  37.     # 插入多个数据
  38.     query = db.insert(table)
  39.     values = [
  40.         {'code': 10020, 'name': '上海'},
  41.         {'code': 10030, 'name': '杭州'},
  42.     ]
  43.     connection.execute(query, values)
  45.     # 查询
  46.     query = db.select([table]).order_by(db.desc(table.columns.id)).limit(10)
  47.     rows = connection.execute(query).fetchall()
  48.     for row in rows:
  49.         print(row.id, row.code, row.name)
  51.     # 修改
  52.     query = db.update(table).values(name="帝都").where(table.columns.code == 10010)
  53.     connection.execute(query)
  55.     # 删除行
  56.     query = db.delete(table).where(table.columns.code == 10010)
  57.     connection.execute(query)
  60. if __name__ == "__main__":
  61.     sqlalchemy_demo()

Go Mysql 操作

go 可以使用 gorm 或者 database/sql

  1. package main
  3. import (
  4.     "database/sql"
  5.     "fmt"
  6.     "log"
  7.     "time"
  9.     _ "github.com/go-sql-driver/mysql"
  10. )
  12. func main() {
  13.     db, err := sql.Open("mysql", "root:root@(127.0.0.1:3306)/root?parseTime=true")
  14.     if err != nil {
  15.         log.Fatal(err)
  16.     }
  17.     if err := db.Ping(); err != nil {
  18.         log.Fatal(err)
  19.     }
  21.     { // Create a new table
  22.         query := `
  23.             CREATE TABLE users (
  24.                 id INT AUTO_INCREMENT,
  25.                 username TEXT NOT NULL,
  26.                 password TEXT NOT NULL,
  27.                 created_at DATETIME,
  28.                 PRIMARY KEY (id)
  29.             );`
  31.         if _, err := db.Exec(query); err != nil {
  32.             log.Fatal(err)
  33.         }
  34.     }
  36.     { // Insert a new user
  37.         username := "johndoe"
  38.         password := "secret"
  39.         createdAt := time.Now()
  41.         result, err := db.Exec(`INSERT INTO users (username, password, created_at) VALUES (?, ?, ?)`, username, password, createdAt)
  42.         if err != nil {
  43.             log.Fatal(err)
  44.         }
  46.         id, err := result.LastInsertId()
  47.         fmt.Println(id)
  48.     }
  50.     { // Query a single user
  51.         var (
  52.             id        int
  53.             username  string
  54.             password  string
  55.             createdAt time.Time
  56.         )
  58.         query := "SELECT id, username, password, created_at FROM users WHERE id = ?"
  59.         if err := db.QueryRow(query, 1).Scan(&id, &username, &password, &createdAt); err != nil {
  60.             log.Fatal(err)
  61.         }
  63.         fmt.Println(id, username, password, createdAt)
  64.     }
  66.     { // Query all users
  67.         type user struct {
  68.             id        int
  69.             username  string
  70.             password  string
  71.             createdAt time.Time
  72.         }
  74.         rows, err := db.Query(`SELECT id, username, password, created_at FROM users`)
  75.         if err != nil {
  76.             log.Fatal(err)
  77.         }
  78.         defer rows.Close()
  80.         var users []user
  81.         for rows.Next() {
  82.             var u user
  84.             err := rows.Scan(&u.id, &u.username, &u.password, &u.createdAt)
  85.             if err != nil {
  86.                 log.Fatal(err)
  87.             }
  88.             users = append(users, u)
  89.         }
  90.         if err := rows.Err(); err != nil {
  91.             log.Fatal(err)
  92.         }
  94.         fmt.Printf("%#v", users)
  95.     }
  97.     {
  98.         _, err := db.Exec(`DELETE FROM users WHERE id = ?`, 1)
  99.         if err != nil {
  100.             log.Fatal(err)
  101.         }
  102.     }
  103. }