基于MySQL环境下的数据处理技巧

白日梦想家

发布于 2021-09-15 16:31:14

1.2K0

发布于 2021-09-15 16:31:14

文章被收录于专栏：SQL实现SQL实现

本文源于我在团队内部的一次技术分享，我整理后把它放来这里。

1 表管理

1.1 复制表结构

在一些 MySQL GUI 工具里面，它们提供了创建相同表结构的新表的快捷操作。如果我们用的工具没有提供这些功能，我们也可以把源表的表结构的 SQL 语句拷贝出来，修改成要创建的目标表的表结构，再执行建表语句。

不过，在 MySQL 中也没这么麻烦，MySQL 提供了 CREATE TABLE target_table LIKE source_table; 命令用来复制表结构，一步到位。

举个例子，我们有个 emp 表，它的表结构如下：

CREATE TABLE `emp` (
  `empno` int unsigned NOT NULL,
  `ename` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mgr` int DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` int DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `idx_deptno` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

使用 LIKE 语法的 SQL CREATE TABLE emp_v2 LIKE emp; 创建的空表 emp_v2 的过程如下：

mysql> create table emp_v2 like emp;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table emp_v2;
+--------+------------------------------+
| Table  | Create Table                 |
+--------+------------------------------+
| emp_v2 | CREATE TABLE `emp_v2` (
  `empno` int unsigned NOT NULL,
  `ename` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mgr` int DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` int DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `idx_deptno` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+--------+------------------------------+
1 row in set (0.00 sec)

如果是用 create table emp_v3 as select * from emp where 1 = 2; 这种方式建表呢，它会把源表的索引信息丢弃，即新建的 emp_v3 表没有索引，而源表 emp 是索引的。

mysql> create table emp_v3 as select * from emp where 1 = 2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp_v3;
+--------+------------------------------+
| Table  | Create Table                 |
+--------+------------------------------+
| emp_v3 | CREATE TABLE `emp_v3` (
  `empno` int unsigned NOT NULL,
  `ename` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mgr` int DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+--------+------------------------------+
1 row in set (0.00 sec)

1.2 清空表数据

在开发环境，有的伙伴要清空一张表的数据，习惯用 DELETE FROM xx; 这种方式。

如果要清空表的全部数据，我们更推荐使用 TRUNCATE TABLE xx; ，这语句相当于先 drop xx，再 create xx，它可以释放被占用的表空间。

2 常用函数

MySQL 提供的内置函数有数值函数、日期函数、字符串函数、窗口函数（MySQL 8.0 后出现）、逻辑函数等，这些函数在官方文档中都有详细的说明。

2.1 数值函数

CEIL(X)/CEILING(X) 向上取整
FLOOR(X) 向下取整
ROUND(X,D) 四舍五入
TRUNCATE(X,D) 截取数值的前几位
FORMAT(X,D) 格式化显示数值

2.2 日期函数

DATE_ADD(date,INTERVAL expr unit) , DATE_SUB(date,INTERVAL expr unit) 日期加减
DATEDIFF(expr1,expr2) expr1 和 expr2 两个日期相差的天数
LAST_DAY(date) 当月的最后一天
DATE_FORMAT(date,format) 日期格式化
CURDATE() 返回今天的日期，可能是 'YYYY-MM-DD' or 'YYYYMMDD' 格式

2.3 字符串函数

CONCAT(str1,str2,...) 字符串连接
INSTR(str,substr) 子串 substr 在 str 中出现的位置
REPLACE(str,from_str,to_str) 用 to_str 替换出现在 str 中的 from_str
SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len) 截取字符串 str从 pos 位置往后的子字符串。如果有 len 参数，表示从 pos 起，截取 len 长度的字符串
SUBSTRING_INDEX(str,delim,count) count 可以是正数或者复数。如果是正数，将返回字符串 str 的第 count 分隔符 delim 前的子串；如果是负数，返回字符串 str 的倒数第 count 分隔符 delim 之后的子串。
LENGTH(str) 返回字符串 str 的长度，单位为字节
LEFT(str,len) 返回字符串 str 左边的 len 个字符
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)返回去除所有 remstr 前缀或后缀的字符串。如果没有给出 BOTH、LEADING 或 TRAILING 中的任何一个，则假定为 BOTH。remstr 是可选的，如果没有指定，则删除空格。

CONCAT() 和 CONCAT_WS() 的示例：

mysql> SELECT CONCAT('中国','加油');
+---------------------------+
| CONCAT('中国','加油')     |
+---------------------------+
| 中国加油                  |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS(',','中国','加油');
+----------------------------------+
| CONCAT_WS(',','中国','加油')     |
+----------------------------------+
| 中国,加油                        |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('中国','加油',NULL);
+--------------------------------+
| CONCAT('中国','加油',NULL)     |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set (0.00 sec)

LENGTH()和 CHAR_LENGTH()的区别：

mysql> SELECT LENGTH('yyds');
+----------------+
| LENGTH('yyds') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH('中国yyds');
+----------------------+
| LENGTH('中国yyds')   |
+----------------------+
|                   10 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR_LENGTH('中国yyds');
+---------------------------+
| CHAR_LENGTH('中国yyds')   |
+---------------------------+
|                         6 |
+---------------------------+
1 row in set (0.00 sec)

LENGTH() 计算给定的字符串的字节数，由于“中国”用的 UTF-8 编码，一个中文字符占 3 个字节。所以，LENGTH('中国yyds') 的结果是 10 。

CHAR_LENGTH() 计算给定的字符串的字符长度。

2.4 特殊函数

我们经常会用到的一些特殊函数有：GROUP_CONCAT()、FIND_IN_SET()、IF()、IFNULL()等。

GROUP_CONCAT() 可以将同一组内的某个文本类型的字段的值拼接到一起。

mysql> SELECT deptno,GROUP_CONCAT(ename) FROM emp GROUP BY deptno;
+--------+--------------------------------------+
| deptno | GROUP_CONCAT(ename)                  |
+--------+--------------------------------------+
|     10 | CLARK,KING,MILLER                    |
|     20 | SMITH,JONES,SCOTT,ADAMS,FORD         |
|     30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
+--------+--------------------------------------+
3 rows in set (0.00 sec)

在 GROUP_CONCAT() 中，可以用 ORDER BY 使文本以某种方式排序，还可以用 DISTINCT 排除文本中的重复值。

不过，GROUP_CONCAT() 默认是以“,”作为连接符，没办法指定任意字符为连接符。

另外，GROUP_CONCAT() 有长度限制，默认是 1024 个字节的长度。要突破这个限制，可以使用 SET [GLOBAL | SESSION] group_concat_max_len = val; 命令设置它的最大长度。

mysql> SHOW VARIABLES LIKE 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> SET SESSION group_concat_max_len = 10240;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 10240 |
+----------------------+-------+
1 row in set (0.00 sec)

使用函数 FIND_IN_SET() 可以查询某个多值字段的具体值，比如文本 "abc,af" 中的 “abc” 和 “af” 两个值组成，如果用 LIKE 语句或者 INSTR() 函数查询文本是否包含 "ab"，没办法获取到准确的结果。

CREATE TABLE `x0` (
  `id` int NOT NULL,
  `v` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

insert  into `x0`(`id`,`v`) values (1,'abc,af'),(2,'ab,cd');

mysql> SELECT * FROM x0 WHERE v LIKE '%ab%';
+----+--------+
| id | v      |
+----+--------+
|  1 | abc,af |
|  2 | ab,cd  |
+----+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM x0 WHERE FIND_IN_SET('ab',v) > 0;
+----+-------+
| id | v     |
+----+-------+
|  2 | ab,cd |
+----+-------+
1 row in set (0.00 sec)

3 表关联操作

3.1 表关联的语法

3.1.1 关联查询

-- 写法1
SELECT 
  t1.name,
  t2.salary 
FROM
  employee t1,
  info t2 
WHERE t1.name = t2.name ;

-- 写法2
SELECT 
  t1.name,
  t2.salary 
FROM
  employee t1 
  INNER JOIN info t2 
    ON t1.name = t2.name ;

3.1.2 关联更新

-- 写法1
UPDATE 
  t1 
  INNER JOIN t2 
    ON t1.id = t2.id SET t1.price = t2.price 
WHERE t2.flag = 'Y' ;

--写法2
UPDATE 
  t1,
  t2 
SET
  t1.price = t2.price 
WHERE t2.flag = 'Y' 
  AND t1.id = t2.id ;

3.1.3 关联删除

-- 1.同时删除表t1和表t2能关联上的记录
DELETE 
  t1,
  t2 
FROM
  t1 
  INNER JOIN t2 
WHERE t1.id = t2.id ;

-- 2.删除表t1中那些能关联上表t2的记录
DELETE 
  t1 
FROM
  t1 
  INNER JOIN t2 
WHERE t1.id = t2.id ;

-- 3.删除表t1中那些不存在于表t2的记录
DELETE 
  t1 
FROM
  t1 
  LEFT JOIN t2 
    ON t1.id = t2.id 
WHERE t2.id IS NULL ;

3.2 Left Join 的陷阱

一些伙伴在写有 Left Join 操作的 SQL 语句时，偶尔会把右表的过滤条件放到 Where 子句中，从而没有得到预期的效果。

下面我们就看右表的过滤条件放在 ON 后面和 WHERE 子句里面的区别。

右表的过滤条件放在 ON 后面：

mysql> SELECT 
    ->   e.empno,
    ->   e.deptno,
    ->   d.dname 
    -> FROM
    ->   emp e 
    ->   LEFT JOIN dept d 
    ->     ON d.deptno = e.deptno 
    ->     AND d.loc = 'NEW YORK' ;
+-------+--------+------------+
| empno | deptno | dname      |
+-------+--------+------------+
|  7782 |     10 | ACCOUNTING |
|  7839 |     10 | ACCOUNTING |
|  7934 |     10 | ACCOUNTING |
|  7369 |     20 | NULL       |
|  7566 |     20 | NULL       |
|  7788 |     20 | NULL       |
|  7876 |     20 | NULL       |
|  7902 |     20 | NULL       |
|  7499 |     30 | NULL       |
|  7521 |     30 | NULL       |
|  7654 |     30 | NULL       |
|  7698 |     30 | NULL       |
|  7844 |     30 | NULL       |
|  7900 |     30 | NULL       |
+-------+--------+------------+
14 rows in set (0.00 sec)

右表的过滤条件放在 Where 子句里面：

mysql> SELECT 
    ->   e.empno,
    ->   e.deptno,
    ->   d.dname 
    -> FROM
    ->   emp e 
    ->   LEFT JOIN dept d 
    ->     ON d.deptno = e.deptno 
    -> WHERE d.loc = 'NEW YORK' ;
+-------+--------+------------+
| empno | deptno | dname      |
+-------+--------+------------+
|  7782 |     10 | ACCOUNTING |
|  7839 |     10 | ACCOUNTING |
|  7934 |     10 | ACCOUNTING |
+-------+--------+------------+
3 rows in set (0.01 sec)

实际上，右表的过滤条件如果放在 Where 子句里面，Left Join 的效果就和 Inner Join 无异了，这点大家一定要注意。

4 数据处理实例

4.1 检查数据质量

-- 统计表t的数据量
SELECT 
  COUNT(*) 
FROM
  t ;

-- 统计字段a在表t中的唯一值的数量
SELECT 
  COUNT(DISTINCT a) 
FROM
  t ;

-- 获取存在重复的xx字段的值
SELECT 
  xx,
  COUNT(*) 
FROM
  t 
GROUP BY xx 
HAVING COUNT(*) >= 2 ;

-- 获取表a中不存在于表b的记录
SELECT 
  a.* 
FROM
  a 
  LEFT JOIN b 
    ON b.id = a.id 
WHERE b.id IS NULL ;

4.2 解析 Json 字段的值

有些表的字段存储了 Json 文本，在 MySQL 5.7 之后，我们用专门的 Json 函数提取。但在之前更早的版本，我们只能用嵌套多个字符串函数处理。

SET @addr='{"addrId":1024,"provinceCode":"100008","province":"广东省",
"cityCode":"10000801","city":"广州市","countyCode":"1000080102","county":"荔湾区",
"detailAddress":"广东省广州市荔湾区xxx街道","name":"张三"}';
SELECT 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(@addr, '"province":"', - 1),
    '",',
    1
  ) AS province,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(@addr, '"city":"', - 1),
    '",',
    1
  ) AS city,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(@addr, '"county":"', - 1),
    '",',
    1
  ) AS county,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(@addr, '"detailAddress":"', - 1),
    '",',
    1
  ) AS detailAddress;
  
province   city       county     detailAddress                         
---------  ---------  ---------  --------------------------------------
广东省        广州市        荔湾区        广东省广州市荔湾区xxx街道

对于更复杂的 Json 结构，比如 Json 列表，那就得写 Python/Java 程序来解析了。

4.3 删除表中大量数据

分批次删除。

4.4 存储 emoji 表情

我们要向一张已存在的表中某个字段存入带有 emoji 表情的文本，明明已经将表的编码改成 uf8mb4 了，为什么在写入的时候仍旧提示编码不支持呢？

因为每个文本类型字段都有字符编码，如果我们只是改了表的编码，字段的编码并不会改变。所以，正确的做法是改变字段的编码。

5 表设计优化实例

假设我们手头上有一张图片表 img，它现在里面有 100w 的图片数据。它的表结构如下（已略去不相干的字段）：

CREATE TABLE `img` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `img_name` varchar(36) NOT NULL,
  `img_url` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

其中，img_name 是由 UUID() 函数生成。

img 表的示例数据：

    id  img_name                          img_url                                                       
------  --------------------------------  --------------------------------------------------------------
     1  8006c76e0d3411ec93b700163e12cb97  https://www.xxx.com/wp-content/uploads/20200423100357456.png  
     2  8006c7b00d3411ec93b700163e12cb97  https://xxx.yyy.com/news/res/2100386142261.png

现在我们要做的事情有两件：

索引 img_name 字段；
设计一套规则，避免往 img 表中插入重复的 img_url。

5.1 索引 UUID 值的字段

是直接对整个 img_name 字段创建索引吗？比如用下面的脚本：

ALTER TABLE `img`   
  ADD  INDEX `idx_img_name` (`img_name`);

其实没必要哦，因为 img_name 由 UUID() 函数生成，它的前 8 位就有很高的辨识度，即只截取前 8 位就很少有重复的了。

依据前缀索引的原理，只对文本字段的值的前缀建索引也是可以的。因此，我们只对 img_name 的前 8 位建索引。

ALTER TABLE `img`   
  ADD  INDEX `idx_img_name` (`img_name`(8));

大家可以用下面的脚本去比较对整个字段建索引和对字段的前缀建索引的区别，下面的脚本用来统计每个索引的大小。

SELECT 
  SUM(stat_value) pages,
  table_name part,
  index_name,
  CONCAT(
    ROUND(
      SUM(stat_value) * (@@innodb_page_size / 1024),
      0
    ),
    ' KB'
  ) AS size 
FROM
  mysql.innodb_index_stats 
WHERE table_name = 'img' 
  AND database_name = 'test' 
  AND stat_name LIKE 'size' 
GROUP BY table_name,
  index_name ;

5.2 url 字段去重

经过前面的介绍，我们直接对 url 字段建索引是很不明智的，并且 url 没有明显的规则，无法只提取其一部分用作索引。

我们可以添加一个计算字段，该字段存储 url 的哈希值。MySQL 提供了 CRC32() 函数计算文本的哈希值，计算的结果是整数，因此我们可以用整型存储该字段的值。

mysql> select CRC32(img_url) from img where id <= 2;
+----------------+
| CRC32(img_url) |
+----------------+
|      403375428 |
|     2911543490 |
+----------------+
2 rows in set (0.01 sec)

需要注意的是，CRC32() 函数计算出来的值有可能会超过 int 类型的最大值 2147483647，因此要将字段的类型设置为无符号整型，即 UNSIGNED INT。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-09-04，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法