专栏首页SQL实现基于MySQL环境下的数据处理技巧

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

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

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  

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

  1. 索引 img_name 字段;
  2. 设计一套规则,避免往 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

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-09-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 基于REST的数据处理流程_开发环境踩坑

    昨天捋了捋思路,今天着手开始准备构建基础工程,spring boot是我一直比较偏爱的,现在在国内开发领域也越发的火了起来。另外准备试试kotlin,之...

    麒思妙想
  • CENTOS7环境下MYSQL数据库忘记ROOT密码处理办法

    修改配置文件:vi /etc/my.cnf 添加 skip-grant-tables 保存退出

    用户4988376
  • 零基础搭建Hadoop大数据处理环境

    由于hadoop需要运行在Linux环境中,而且是分布式的,因此个人学习只能装虚拟机,本文都以VMware Workstation为准,安装CentOS7,具体...

    企鹅号小编
  • 0基础搭建Hadoop大数据处理-环境

       由于Hadoop需要运行在Linux环境中,而且是分布式的,因此个人学习只能装虚拟机,本文都以VMware Workstation为准,安装CentOS7...

    欢醉
  • 零基础搭建Hadoop大数据处理环境

    VMware的安装,装好一个虚拟机后利用复制虚拟机的方式创建后面几个虚拟机,省时省力,需要注意的是需要修改每个虚拟机的IP与主机名。

    华章科技
  • 小数据处理的 7 个技巧

    本文作者是 Kanda 的机器学习工程师 Daniel Rothmann,他在和客户合作的过程中总结出的小数据处理方法。

    加米谷大数据
  • pandas数据处理的一些技巧

    loc只能使用字符型标签来索引数据,不能使用数字来索引数据。但是标签本身是数字,则可以用数字来索引;

    小末快跑
  • Hadoop环境中管理大数据存储八大技巧

    传统化集中式存储存在已有一段时间。但大数据并非真的适合集中式存储架构。Hadoop设计用于将计算更接近数据节点,同时采用了HDFS文件系统的大规模横向扩展功能。...

    加米谷大数据
  • Hadoop环境中管理大数据存储八大技巧

    传统化集中式存储存在已有一段时间。但大数据并非真的适合集中式存储架构。Hadoop设计用于将计算更接近数据节点,同时采用了HDFS文件系统的大规模横向扩展功能。

    加米谷大数据
  • Hadoop环境中管理大数据存储八大技巧

    传统化集中式存储存在已有一段时间。但大数据并非真的适合集中式存储架构。Hadoop设计用于将计算更接近数据节点,同时采用了HDFS文件系统的大规模横向扩展功能。

    加米谷大数据
  • Hadoop环境中管理大数据存储八大技巧

    随着IT互联网信息技术的飞速发展和进步。目前大数据行业也越来越火爆,从而导致国内大数据人才也极度缺乏,下面介绍一下关于Hadoop环境中管理大数据存储技巧。

    一起学习大数据
  • 零基础学习大数据,搭建Hadoop处理环境

    由于Hadoop需要运行在Linux环境中,而且是分布式的,因此个人学习只能装虚拟机,本文都以VMware Workstation为准,安装CentOS7,具体...

    用户2292346
  • 零基础学习大数据,搭建Hadoop处理环境

    由于Hadoop需要运行在Linux环境中,而且是分布式的,因此个人学习只能装虚拟机,本文都以VMware Workstation为准,安装CentOS7,具...

    挖掘大数据
  • 基于 TiSpark 的海量数据批量处理技术

    熟悉 TiSpark 的人都知道,TiSpark 是 Spark 的一个插件,它其实就是给予了 Spark 能够去访问 TiDB 底层分布式存储引擎 TiKV ...

    PingCAP
  • 处理非平衡数据的七个技巧

    用户1737318
  • 基于tensorflow的图像处理(四) 数据集处理

    除队列以外,tensorflow还提供了一套更高的数据处理框架。在新的框架中,每一个数据来源被抽象成一个“数据集”,开发者可以以数据集为基本对象,方便地进行ba...

    狼啸风云
  • 信息收集后的数据处理小技巧

    批量处理数据还是不错的,列如收集到了别的大佬的一些子域名的表,但是还想自己跑一遍,提取主域名,或者批量加https http 当然小米饭也可以,只是也局限性

    天钧
  • Mall电商实战项目专属学习路线,主流技术一网打尽!

    由于mall项目涵盖了现阶段主流技术,如果你是个Java初学者的话,最好先看下面的资料打个基础,资料具体介绍可以参考mall学习所需知识点。

    macrozheng
  • 【干货合辑】你有什么独家数据库优化技巧?

    数据库优化是每个开发、运维同学成长过程中的必修课。今天我们整理了腾讯云的数据库专家们的技术干货,分享给你。

    云加社区

扫码关注云+社区

领取腾讯云代金券