MySQL填坑手册

发表于 更新于 分类于 ， ， 本文字数： 3k 阅读时长 ≈ 3 分钟

记录一下工作中遇到的MySQL查询的坑

计算磁盘占用量

1
2
3
4
5
6
7
8
SELECT TABLE_SCHEMA,
       CONCAT(TRUNCATE(SUM(data_length) / 1024 / 1024, 2), ' MB') AS data_size,

       CONCAT(TRUNCATE(SUM(index_length) / 1024 / 1024, 2), 'MB') AS index_size

FROM information_schema.tables
GROUP BY TABLE_SCHEMA
ORDER BY data_size DESC;

计算同比费用

MySQL 临时表 | 菜鸟教程

mysql实现full outer join的方法_东来东往的数据与产品的博客-CSDN博客

MySQL实现全关联 full outer join - Code~Rush - 博客园

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 创建历史与当前临时表
CREATE TEMPORARY TABLE history
    (SELECT line_item_product_code AS product, SUM(line_item_unblended_cost) AS cost
     FROM `athena`.`2021-05`
     GROUP BY product
     ORDER BY cost DESC);
CREATE TEMPORARY TABLE current
    (SELECT line_item_product_code AS product, SUM(line_item_unblended_cost) AS cost
     FROM `athena`.`2022-05`
     GROUP BY product
     ORDER BY cost DESC);

# 合并计算临时表全连接
CREATE TEMPORARY TABLE left_join (SELECT history.product, history.cost AS history, current.cost AS current
                                  FROM history
                                           LEFT JOIN current ON history.product = current.product);

CREATE TEMPORARY TABLE right_join(SELECT current.product, history.cost AS history, current.cost AS current
                                  FROM history
                                           RIGHT JOIN current ON history.product = current.product);
# 展示结果
SELECT *
FROM left_join
UNION
SELECT *
FROM right_join;

计算某个时间段的费用和历史同期费用

mysql中sql语句使日期增加一年_琅琊山二当家的博客-CSDN博客_sql当前日期增加一年

1
2
3
4
5
6
7
8
# 查询历史同期费用
SET @START := '2021-12-01';
SET @END := '2022-05-01';
SELECT `month`, aws_china
FROM total.cloud_summary
WHERE (`month` <= @END AND `month` >= @START)
   OR (`month` <= DATE_ADD(@END, INTERVAL -1 YEAR) AND `month` >= DATE_ADD(@START, INTERVAL -1 YEAR));

使用变量作为表名进行查询操作

MySQL中SELECT * FROM [变量] 如何实现? - 知乎

1
2
3
4
5
6
7
8
9
10
11
12
13
# 2022-07-05 10:30:00 数据管理部费用细化拆分
SET @CALC_MONTH := '2022-01';
SET @TABLE_NAME := CONCAT(@CALC_MONTH, '-jtbb-szhzx-sjglb');
# POC费用
SET @SQL = CONCAT(
        '
        SELECT SUM(line_item_unblended_cost) AS "POC"
        FROM split.`', @TABLE_NAME, '`
        WHERE line_item_usage_account_id = \'************\'
          OR line_item_usage_account_id = \'************\'
');
PREPARE stmt FROM @SQL;
EXECUTE stmt;

对两个查询结果进行计算

sql 查询结果增加一列,列名固定,值也固定（mysql可行，其他未知）_九洲江的博客-CSDN博客_mysql查询固定值

How can I sum columns across multiple tables in MySQL? - Stack Overflow

一个sql很多个not like的简化语句_not like多个条件-吾爱编程网

1
2
3
4
5
6
7
8
9
10
11
SELECT 'MD4X' AS project,
       (SELECT SUM(line_item_unblended_cost) AS cost
        FROM split.`2022-05-jtbb-szhzx-sjglb`
        WHERE line_item_usage_account_id = '************'
          AND Region = 'cn-north-1') -
       (SELECT SUM(line_item_unblended_cost) AS cost
        FROM split.`2022-05-jtbb-szhzx-sjglb`
        WHERE line_item_usage_account_id = '************'
          AND Region = 'cn-north-1'
          AND resource_tags_user_project REGEXP 'kns|catalog') AS cost
;

NOT LIKE / NOT REGEX 使用的坑

LIKE/ REGEX操作默认会忽略值=NULL的行

LIKE/ REGEX操作默认会忽略值=NULL的行

LIKE/ REGEX操作默认会忽略值=NULL的行

mysql中关于 like ，not like 的用法时不能显示空值的数据（空值不参与判断，直接过滤空值） - songyinan - 博客园

orcle not like不建议使用（not like所踩过的坑！） - Marydon - 博客园

1
2
3
4
5
6
7
8
SELECT SUM(line_item_unblended_cost)
FROM split.`2022-05-jtbb-szhzx-sjglb`
WHERE line_item_usage_account_id = '************'
  AND Region = 'cn-north-1'
  AND (
            resource_tags_user_project NOT REGEXP 'kns|catalog'
        OR
            resource_tags_user_project IS NULL);

如果不添加IS NULL的条件, 数据结果就是错的!