本文源于我在团队内部的一次技术分享,我整理后把它放来这里。
在一些 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)
在开发环境,有的伙伴要清空一张表的数据,习惯用 DELETE FROM xx;
这种方式。
如果要清空表的全部数据,我们更推荐使用 TRUNCATE TABLE xx;
,这语句相当于先 drop xx,再 create xx,它可以释放被占用的表空间。
MySQL 提供的内置函数有数值函数、日期函数、字符串函数、窗口函数(MySQL 8.0 后出现)、逻辑函数等,这些函数在官方文档中都有详细的说明。
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()
计算给定的字符串的字符长度。
我们经常会用到的一些特殊函数有: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)
-- 写法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 ;
-- 写法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 ;
-- 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 ;
一些伙伴在写有 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 无异了,这点大家一定要注意。
-- 统计表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 ;
有些表的字段存储了 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 程序来解析了。
分批次删除。
我们要向一张已存在的表中某个字段存入带有 emoji 表情的文本,明明已经将表的编码改成 uf8mb4
了,为什么在写入的时候仍旧提示编码不支持呢?
因为每个文本类型字段都有字符编码,如果我们只是改了表的编码,字段的编码并不会改变。所以,正确的做法是改变字段的编码。
假设我们手头上有一张图片表 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 字段创建索引吗?比如用下面的脚本:
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 ;
经过前面的介绍,我们直接对 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
。