sql

Update

UPDATE v9_news SET a=1    # 将字段（a）修改为1
UPDATE v9_news SET a=REPLACE(a,'b','c')   # 将字段（a）里的字符(b)批量替换为字符（c）

where

where 1 = 1 # 动态条件

order by

# mysql大于当前时间置顶并按升序排序，小于当前时间的置尾并按降序排序. 
SELECT * FROM number_generator ORDER BY id < 16, IF(id < 16, 0, id), id DESC  # id 小于16的至尾 ，并按降序排序，id大于16的升序排序，这种sql也支持分页
​
# 如果将age为24的列放到最后并且其他的列按照age的自然顺序排列：
order by if(age=24,0,1) desc, age;   # 你可以把这个 if 语句，看成是一个独立的column。

重复数据处理

  # 添加唯一索引
  alter ignore table exhibitions add unique index(company_name);
  
  
  # 唯一字段去重
  DELETE
  FROM
      people
  WHERE
      peopleName IN (
          SELECT
              peopleName
          FROM
              people
          GROUP BY
              peopleName
          HAVING
              count(peopleName) > 1
      )
  AND peopleId NOT IN (
      SELECT
          min(peopleId)
      FROM
          people
      GROUP BY
          peopleName
      HAVING
          count(peopleName) > 1
  )
​
# 组合唯一索引去重
  DELETE
  FROM
      ai_biz_data
  WHERE
      (`postid`, `type`, `rocketreach_id`) IN(
      SELECT
          `postid`,
          `type`,
          `rocketreach_id`
      FROM
          (
          SELECT
              `postid`,
              `type`,
              `rocketreach_id`
          FROM
              ai_biz_data
          GROUP BY
              `postid`,
              `type`,
              `rocketreach_id`
          HAVING
              COUNT(*) > 1
      ) a
  ) AND id NOT IN(
      SELECT
          id
      FROM
          (
          SELECT
              MIN(id) AS id
          FROM
              ai_biz_data
          GROUP BY
              `postid`,
              `type`,
              `rocketreach_id`
          HAVING
              COUNT(*) > 1
      ) b
  )

日常问题

# 导入sql文件过大时，用命令行
mysql -uhbb_hagro_cn -phbb_hagro_cn hbb_hagro_cn < /www/backup/database/db_admin_hagro_cn_20210104_013001.sql
​
# 将指定主机的数据库拷贝到本地
mysqldump -h 172.17.0.36 -P 3306 -u root -p repet_globalso > repet_globalso.sql
​
mysqldump -h 172.17.0.36 -u root -p repet_globalso > repet_globalso.sql

运维工具

mysqldumpslow

mysqlsla

性能监控小工具之 mytop

profiling Mycli

# mytop: mysql的top
yum install mytop -y
man mytop # mytop -h
-u  --user <USERNAME>：指定 username，预设是 root
-p  --pass  --password <PASSWORD>：指定password，预设是none
-h / --host <HOSTNAME[:PORT]>：指定 MySQL server的hostname，预设是localhost
-P / --port <PORT>：指定连接 MySQL server的port，预设是3306
-s / --delay <SECONDS>：更新的秒数，预设是5秒
-d / --db / --database <DATABASE>：指定连接的资料库，预设是test
-b / --batch / --batchmode：指定为 batch mode，每次更新不会清除旧的显示结果，会将更新资料显示上最上方，预设是unset
-S / --socket <PATH_TO_SOCKET>：指定使用MySQL socket直接连线，而不使用TCP/IP连线，预设是none(当mytop和MySQL在同一台时才能使用)
--header or -noheader：是否要显示表头，预设是header
--color or --nocolor：是否要使用颜色，预设是color
-i / -idle or -noidle：idle 的thread是否要出现在清单上，预设是idle
​
====== mytop 快捷键
  s：设定更新时间 
  p：暂停画面更新
  q：离开
  u：只看某个使用者的thread
  o：反转排列顺序
​
mytop --prompt -d globalso_site -p # 17c4db256079d11d
mytop -uroot -p 17c4db256079d11d -d wordpress -h 127.0.0.1
​
# 内置profiling性能分析工具
show variables like '%profiling%';  # 查看是否开启
​
show variables like "%query%" ; #查看配置
​
​
====== 原生
mysql -u root -p # 17c4db256079d11d
show full processlist;

mysql 命令

管理命令

mysql -u root -p 17c4db256079d11d
create database zabbix character set utf8 collate utf8_bin;
create user zabbix@localhost identified by 'password';
grant all privileges on zabbix.* to zabbix@localhost;
set password for 用户名@localhost = password('新密码');
mysqladmin -u用户名 -p旧密码 password 新密码 
​
show DATABASES;
use hbb_hagro_cn;
SHOW TABLES;
SHOW COLUMNS FROM runoob_tbl;
SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的信息
SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob开头的表的信息
SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查询结果按列打印

宝塔面板，mysql数据库误删除，怎么通过mysql-bin日志文件恢复数据。

# 1、吧多个mysql-bin.xxx 文件导出sql
/www/server/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v /www/server/data/mysql-bin.001518 -d xxx数据库 > 1.sql
# 2、导入sql文件
mysql -uroot -p -f xxx数据库 < 1.sql

mysql for mac m1

docker pull --platform linux/amd64 mysql:5.7.44
docker run --name mysql5.7 -e MYSQL_ROOT_PASSWORD=hbbhbb -d --platform linux/amd64 mysql:5.7.44
docker run --name mysql5.7 -v /Users/hbb/Sites/data/mysql5.7:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=hbbhbb -d -p 3306:3306 --platform linux/amd64 mysql:5.7.44

docker for mac mariadb

# 导入宿主机的sql文件到mysql容器
docker cp /path/to/your/file.sql <container_id>:/tmp/file.sql
docker exec -i <container_name_or_id> mysql -u <username> -p<password> <database_name> < /path/to/data.sql
docker exec -i 52c05d516f5d mariadb -u root -p aicc-pro < ./export_data.sql