# 导出除指定表外的所有数据 要在mysql bin目录下 cmd mysqldump -h 数据库地址 -u 用户名 -p 数据库 --ignore-table=表名 --ignore-table=表名 > output.sql # 追加5个表的结构 mysqldump -h 数据库地址 -u 用户名 -p --no-data 数据库 表名 表名 表名 表名 表名 >> output.sql
# 统计数据库表空间占用情况 SELECT table_name, ROUND(data_length/1024/1024, 2) AS '数据大小(MB)', ROUND(index_length/1024/1024, 2) AS '索引大小(MB)', ROUND((data_length + index_length)/1024/1024, 2) AS '总大小(MB)', table_rows AS '行数', ROUND((data_length + index_length)/1024/1024/1024, 2) AS '总大小(GB)' FROM information_schema.TABLES WHERE table_schema = '数据库' ORDER BY (data_length + index_length) DESC;
# 查询数据库进程 SELECT * FROM information_schema.INNODB_TRX; # 杀掉进程 kill 进程;
-- 创建临时表存储要删除的重复数据 CREATE TABLE temp_duplicate_ids AS SELECT * FROM (SELECT *, ROW_NUMBER() OVER ( PARTITION BY LOG_TYPE, LOG_DETAIL, REMARK ORDER BY id ) AS row_num FROM sys_operation_log WHERE OPERATION_TIME > '2025-07-28 10:54:00') t WHERE row_num > 1;
匹配不存在数据 SELECT m.* FROM rec_coupang_manifest_history m WHERE not EXISTS (SELECT 1 FROM rec_waybill_head w WHERE w.ENTERPRISE_CODE = 'HLSHMY' and w.create_date > '2026-03-22 08:00:00' AND m.track_no = w.TRACK_NO) and m.create_date > '2026-03-22 08:00:00'; 根据json刷字段 UPDATE rec_coupang_manifest_history SET track_no = JSON_UNQUOTE(JSON_EXTRACT(json, '$.hblList[0].hblNumber')) WHERE json IS NOT NULL AND JSON_VALID(json);
