MySQL 窗口函数

MySQL 8.0 引入了 窗口函数(window functions) ,我们看一下都有哪些功能。

NameDescription
CUME_DIST()Cumulative distribution value
DENSE_RANK()Rank of current row within its partition, without gaps
FIRST_VALUE()Value of argument from first row of window frame
LAG()Value of argument from row lagging current row within partition
LAST_VALUE()Value of argument from last row of window frame
LEAD()Value of argument from row leading current row within partition
NTH_VALUE()Value of argument from N-th row of window frame
NTILE()Bucket number of current row within its partition.
PERCENT_RANK()Percentage rank value
RANK()Rank of current row within its partition, with gaps
ROW_NUMBER()Number of current row within its partition

这里我们使用 world 示例数据库进行演示,首先使用 docker 安装 mysql,然后导入 world.sql。

docker run --name db -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker exec -it db mysql -u root -p < C:\Users\tanghengzhi\Downloads\world.sql




比较常用的有排序:rank(), dense_rank() 和 row_number() 。

mysql> select Name, LifeExpectancy, rank() over (w), dense_rank() over(w), row_number() over(w) 
from country 
where Continent = 'Europe' 
window w as (order by LifeExpectancy desc) 
limit 10;
+---------------+----------------+-----------------+----------------------+----------------------+
| Name          | LifeExpectancy | rank() over (w) | dense_rank() over(w) | row_number() over(w) |
+---------------+----------------+-----------------+----------------------+----------------------+
| Andorra       |           83.5 |               1 |                    1 |                    1 |
| San Marino    |           81.1 |               2 |                    2 |                    2 |
| Switzerland   |           79.6 |               3 |                    3 |                    3 |
| Sweden        |           79.6 |               3 |                    3 |                    4 |
| Iceland       |           79.4 |               5 |                    4 |                    5 |
| Gibraltar     |           79.0 |               6 |                    5 |                    6 |
| Italy         |           79.0 |               6 |                    5 |                    7 |
| Spain         |           78.8 |               8 |                    6 |                    8 |
| France        |           78.8 |               8 |                    6 |                    9 |
| Liechtenstein |           78.8 |               8 |                    6 |                   10 |
+---------------+----------------+-----------------+----------------------+----------------------+
10 rows in set (0.00 sec)




还有第 N 行的值,first_value(), last_value() 和 nth_value()。

mysql> select Name, LifeExpectancy, first_value(Name) over(w) as firth, last_value(Name) over(w) as last, nth_value(Name, 2) over(w) as second, nth_value(Name, 4) over (w) as fourth 
from country 
where Continent = 'Europe' 
window w as (order by LifeExpectancy desc) 
limit 10;
+---------------+----------------+---------+------------+------------+--------+
| Name          | LifeExpectancy | firth   | last       | second     | fourth |
+---------------+----------------+---------+------------+------------+--------+
| Andorra       |           83.5 | Andorra | Andorra    | NULL       | NULL   |
| San Marino    |           81.1 | Andorra | San Marino | San Marino | NULL   |
| Switzerland   |           79.6 | Andorra | Sweden     | San Marino | Sweden |
| Sweden        |           79.6 | Andorra | Sweden     | San Marino | Sweden |
| Iceland       |           79.4 | Andorra | Iceland    | San Marino | Sweden |
| Gibraltar     |           79.0 | Andorra | Italy      | San Marino | Sweden |
| Italy         |           79.0 | Andorra | Italy      | San Marino | Sweden |
| Spain         |           78.8 | Andorra | Monaco     | San Marino | Sweden |
| France        |           78.8 | Andorra | Monaco     | San Marino | Sweden |
| Liechtenstein |           78.8 | Andorra | Monaco     | San Marino | Sweden |
+---------------+----------------+---------+------------+------------+--------+
10 rows in set (0.00 sec)




其他的窗口函数不是很常用,就不一一介绍了,需要用到的请参考官方文档。


接下来是一个在实际使用中遇到的 Bug:

由于涉及到具体业务,这里不方便直接展示相关 SQL 语句。

还是使用演示数据库,查询各大洲的总人口,总人口排名,以及该洲人口最多的国家,按照总人口倒叙排列。

mysql> select Continent, sum(Population), rank() over(order by sum(Population) desc) as `rank`, substring_index(group_concat(Name order by Population desc), ',', 1) as `The most populous country` 
from country 
group by Continent 
order by sum(Population) desc;




MySQL 8.0.16:

+---------------+-----------------+------+----------------------------------------------+
| Continent     | sum(Population) | rank | The most populous country                    |
+---------------+-----------------+------+----------------------------------------------+
| Asia          |      3705025700 |    1 | China                                        |
| Africa        |       784475000 |    2 | Nigeria                                      |
| Europe        |       730074600 |    3 | Russian Federation                           |
| North America |       482993000 |    4 | United States                                |
| South America |       345780000 |    5 | Brazil                                       |
| Oceania       |        30401150 |    6 | Australia                                    |
| Antarctica    |               0 |    7 | South Georgia and the South Sandwich Islands |
+---------------+-----------------+------+----------------------------------------------+
7 rows in set (0.00 sec)




MySQL 8.0.18:

+---------------+-----------------+------+----------------------------------------------+
| Continent     | sum(Population) | rank | The most populous country                    |
+---------------+-----------------+------+----------------------------------------------+
| Asia          |      3705025700 |    1 | South Georgia and the South Sandwich Islands |
| Africa        |       784475000 |    2 | South Georgia and the South Sandwich Islands |
| Europe        |       730074600 |    3 | South Georgia and the South Sandwich Islands |
| North America |       482993000 |    4 | South Georgia and the South Sandwich Islands |
| South America |       345780000 |    5 | South Georgia and the South Sandwich Islands |
| Oceania       |        30401150 |    6 | South Georgia and the South Sandwich Islands |
| Antarctica    |               0 |    7 | South Georgia and the South Sandwich Islands |
+---------------+-----------------+------+----------------------------------------------+
7 rows in set (0.00 sec)




可以看到,同样一个 SQL 语句,在 MySQL 8.0.16 和 MySQL 8.0.18 版本的查询结果不一样的。最新的 MySQL 8.0.22 版本也存在这个问题,目前已经反馈给阿里云,阿里云的小伙伴提交了 MySQL Bug,等待后续解决方案吧。

在这个 Bug 被修复之前,只能先临时修改一下 SQL 语句,如下:

mysql> select Continent, sum(Population), rank() over(w) as `rank`, substring_index(group_concat(Name order by Population desc), ',', 1) as `The most populous country` 
from country 
group by Continent 
window w as (order by sum(Population) desc);

参考:

https://hub.docker.com/_/mysql

https://dev.mysql.com/doc/world-setup/en/

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

https://bugs.mysql.com/bug.php?id=101691

 732 total views

【翻译】MySQL 优化概述

原文链接:https://dev.mysql.com/doc/refman/8.0/en/optimize-overview.html


数据库性能取决于数据库层面的多个因素,例如表、查询和配置。这些软件构造会导致硬件层面的 CPU 和 I/O 操作,你必须尽可能最小化并提高效率。当你刚开始优化数据库性能的时候,首先你要学习软件方面的高级规则和准则,和使用系统时间测量性能。当你成为专家后,你将了解更多的内部细节,并开始测量 CPU 周期和 I/O 操作等指标。

普通用户的目标是从现有的软件和硬件配置中获得最佳的数据库性能。高级用户则会寻找机会来改进 MySQL 软件本身,或者开发自己的存储引擎和硬件设备来扩展 MySQL 生态系统。

数据库层面优化

使数据库应用变快的最重要因素是其基本设计:

表结构是否正常?特别是列是否具有正确的数据类型,并且每个表是否具有适用于工作类型的相应列?例如,需要频繁更新的应用程序通常有许多表,但列很少,而分析大量数据的应用程序通常只有很少的表,但有许多列。

是否使用了正确的索引来提高查询效率?

你是否为每个表选择适当的存储引擎,并且充分利用每种存储引擎的优势和功能?特别是选择事务性存储引擎(如 InnoDB)还是非事务性存储引擎(如 MyISAM)对于性能和可伸缩性非常重要。

注意
InnoDB 是新表的默认存储引擎。实际上,先进的 InnoDB 性能特点意味着 InnoDB 表通常优于更简单的 MyISAM 表,尤其是对于繁忙的数据库。

是否每个表都使用适当的行格式?此选项还取决于用于表的存储引擎。特别是,压缩表占用的磁盘空间较少,因此读取和写入数据所需的磁盘 I/O 更少。压缩可用于具有 InnoDB 表的所有类型的工作负载以及只读的 MyISAM 表。

应用程序是否使用适当的锁定策略?例如,在可能的情况下允许共享访问,以便数据库操作可以同时运行,并在适当时请求独占访问,以便关键操作获得最高优先级。同样,存储引擎的选择也非常重要。InnoDB 存储引擎无需您参与即可处理大多数锁定问题,从而在数据库中实现更好的并发性,并减少代码的实验和调优量。

所有用于缓存的内存区域大小是否正确?也就是说,足够大,可以容纳频繁访问的数据,但规模不够大,以至于使物理内存过载并导致分页。要配置的主要内存区域是 InnoDB 缓冲池和 MyISAM 密钥缓存。

硬件层面优化

随着数据库变得越来越繁忙,任何数据库应用程序最终都达到硬件限制。DBA 必须评估是否可以调整应用程序或重新配置服务器以避免这些瓶颈,或者是否需要更多的硬件资源。系统瓶颈通常来自以下来源:

磁盘查找。磁盘查找数据段需要时间。对于现代磁盘,其平均时间通常低于 10 毫秒,因此理论上我们可以做大约 100 个查找秒。此时间使用新磁盘进行缓慢改进,并且很难针对单个表进行优化。优化寻道时间的方法是将数据分发到多个磁盘上。

磁盘读取和写入。当磁盘处于正确位置时,我们需要读取或写入数据。使用现代磁盘,一个磁盘至少可提供 10*20MB/s 的吞吐量。这比查找更容易优化,因为可以从多个磁盘并行读取。

CPU 周期。当数据位于主内存中时,我们必须处理它以获得结果。与内存量相比,使用大型表是最常见的限制因素。但是,对于小桌子,速度通常不是问题。

内存带宽。当 CPU 需要的数据超过 CPU 缓存容量时,主内存带宽将成为瓶颈。对于大多数系统来说,这是一个不常见的瓶颈,但需要注意。

平衡可移植性和性能

要在便携式 MySQL 程序中使用面向性能的 SQL 扩展,你可以把语句中的 MySQL 特有关键字放到 /*! */ 注释分隔符里面。其他 SQL 服务器会忽略注释的关键字。有关撰写注释的信息,请看 Section 9.6, “Comment Syntax”

 16,446 total views,  3 views today

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

使用 PHP 连接 MySQL 8.0 的时候,可能会发生如下错误:

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

错误原因是 MySQL 8 默认使用了新的密码验证插件:caching_sha2_password,而之前的 PHP (mysqlnd) 版本无法支持这种验证。

有两种方法可以解决这个问题。

  1. 升级 PHP 版本
  2. 使用 mysql_native_password

方法一:升级 PHP 版本

PHP 7.2.8 版本已经可以支持 caching_sha2_password,直接连接MySQL 8。

docker run --rm php:7.2.8 -i | grep caching_sha2_password
Loaded plugins => mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_caching_sha2_password,auth_plugin_sha256_password

但是需要注意的是:不知道是 Bug 还是 Feature,最新的 PHP 7.2.12 版本 和 PHP 7.3.0 版本都不支持 caching_sha2_password。

方法二:使用 mysql_native_password

修改 my.cnf 配置文件,设置默认密码验证插件为 mysql_native_password。

default-authentication-plugin=mysql_native_password

或者在 CREATE USER / ALTER USER 的时候设置账户的默认密码验证插件为 mysql_native_password。

CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password;

参考:

https://github.com/laradock/laradock/issues/1390

 1,354 total views,  2 views today

Please Use utf8mb4

最近遇到这样一个问题,MySQL 执行报错:

查询出错 (1366): Incorrect string value: ‘\xF0\x9F\x98\x84\xF0\x9F…’ for column ‘nickName’ at row 1

执行的 SQL 语句是:

insert into user (nickName) values ('😄😄😄');

user 表中 nickName 字段的定义是:

`nickName` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户昵称'

照理说 utf8mb4 编码存储表情符号是没有问题的,于是参照 Stack Overflow 的回答把 vachar(255) 改成 varbinary(255),的确能够执行成功了。

但是问题接踵而来:查询的时候需要不区分大小写,然而 varbinary 类型不支持 case insenstive。

折腾了一番之后再回来看这个问题,一开始就不是 varchar 和 varbinary 的问题,而是 MySQL 连接的编码设置错了。

'db' => [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=127.0.0.1;dbname=xxx',
    'username' => 'xxx',
    'password' => 'xxx',
    'charset' => 'utf8',
],

把 MySQL 连接的 charset 从 utf8 改成 utf8mb4 才是正确姿势。


通过这个问题,暴露了我对 UTF8 编码的了解还不足,所以在这里做下补充。

什么是 Unicode 和 UTF-8?

Unicode is a worldwide character-encoding standard. 

Unicode provides a unique number for every character,
no matter what the platform,
no matter what the program,
no matter what the language.

UTF-8 is a variable widthcharacter encoding capable of encoding all 1,112,064[1] valid code points in Unicode using one to four 8-bit bytes.[2] The encoding is defined by the Unicode standard, and was originally designed by Ken Thompson and Rob Pike.[3][4] The name is derived from Unicode (or Universal Coded Character SetTransformation Format – 8-bit.[5]

MySQL 中 utf8 和 utf8mb4 的区别。

  • utf8mb4: A UTF-8 encoding of the Unicode character set using one to four bytes per character.
  • utf8mb3: A UTF-8 encoding of the Unicode character set using one to three bytes per character.
  • utf8: An alias for utf8mb3.
  • ucs2: The UCS-2 encoding of the Unicode character set using two bytes per character.
  • utf16: The UTF-16 encoding for the Unicode character set using two or four bytes per character. Like ucs2 but with an extension for supplementary characters.
  • utf16le: The UTF-16LE encoding for the Unicode character set. Like utf16 but little-endian rather than big-endian.
  • utf32: The UTF-32 encoding for the Unicode character set using four bytes per character.

MySQL 8.0 中默认的 utf8mb4_0900_ai_ci 字符集是什么意思?

  • uft8mb4 means that each character is stored as a maximum of 4 bytes in the UTF-8 encoding scheme.
  • 0900 refers to the Unicode Collation Algorithm version. (The Unicode Collation Algorithm is the method used to compare two Unicode strings that conforms to the requirements of the Unicode Standard).
  • ai refers accent insensitivity. That is, there is no difference between e, è, é, ê and ë when sorting.
  • ci refers to case insensitivity. This is, there is no difference between p and P when sorting.

参考:

https://docs.microsoft.com/en-us/windows/desktop/Intl/unicode
http://www.unicode.org/standard/WhatIsUnicode.html
https://en.m.wikipedia.org/wiki/UTF-8
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html
https://www.monolune.com/what-is-the-utf8mb4_0900_ai_ci-collation/

 959 total views,  1 views today

MySQL OR 查询优化

最近在优化 SQL 语句的时候发现一个问题:MySQL 在执行 OR 查询的时候,即使有多个字段能够使用索引,也只会使用其中一个字段的索引。然后在网上找到了如下答案,贴在这里以备参考。

注意:以下内容完全拷贝自 Stack Overflow。


1. The typical way to break up OR predicates is with UNION.

2. UNION is UNION DISTINCT by default. Duplicate rows are removed as part of the UNION construct. If one really to count them twice, one would use ‘UNION ALL’.

mysql> SELECT COUNT(*) FROM table;
+----------+
| COUNT(*) |
+----------+
|  3059139 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM table WHERE columnA = value1;
+----------+
| COUNT(*) |
+----------+
|     1068 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM table WHERE columnB = value2;
+----------+
| COUNT(*) |
+----------+
|      947 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM table WHERE columnA = value1 OR columnB = value2;
+----------+
| COUNT(*) |
+----------+
|     1616 |
+----------+
1 row in set (9.92 sec)

mysql> SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION SELECT * FROM table WHERE columnB = value2) T;
+----------+
| COUNT(*) |
+----------+
|     1616 |
+----------+
1 row in set (0.17 sec)

mysql> SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION ALL SELECT * FROM table WHERE columnB = value2) T;
+----------+
| COUNT(*) |
+----------+
|     2015 |
+----------+
1 row in set (0.12 sec)

参考:

https://stackoverflow.com/questions/2829544/mysql-how-to-index-an-or-clause

https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

 601 total views

MySQL federated storage engine

1. 启动 federated 引擎

show engines;

首先我们查看一下当前已经安装的存储引擎,默认情况为 Engine 列表里面有 FEDERATED,但是Support 为 NO。这时我们需要修改 MySQL 配置文件。

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

在文件末尾添加如下两行。

[mysqld]
federated

然后重启 MySQL。

sudo systemctl restart mysql.service

再查看一下 FEDERATED 引擎的 Support 就变成 YES 了。

如果存储引擎的列表里面没有找到 FEDERATED,那么你可能就需要考虑重新安装 MySQL 了。

2. 创建 federated 表

启用 FEDERATED 引擎之后我们就可以创建 FEDERATED 表了。

show create table lltmdc.edm_readers;
show create table lltmmc.edm_tasks;

首先我们登陆远程数据库查看一下表结构,然后根据表结构创建对应的 FEDERATED 表。

CREATE DATABASE `lltedm`;

CREATE TABLE `edm_readers` (
  `readerid` int(16) NOT NULL AUTO_INCREMENT COMMENT '流水号',
  `reader_email` varchar(64) NOT NULL COMMENT '读者Email',
  `edm_subject` varchar(128) NOT NULL COMMENT '邮件主题',
  `read_times` smallint(8) NOT NULL DEFAULT '1' COMMENT '打开次数',
  `read_ip` varchar(16) DEFAULT NULL COMMENT '初次访问IP',
  `datetime` int(10) NOT NULL COMMENT '首次打开时间',
  PRIMARY KEY (`readerid`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://lltro:dvj7HIa73gw6k@10.8.14.62:3306/lltmdc/edm_readers';

CREATE TABLE `edm_tasks` (
  `taskid` int(16) NOT NULL AUTO_INCREMENT COMMENT '流水号',
  `adminid` int(16) NOT NULL COMMENT '管理员ID',
  `edmsubject` varchar(128) NOT NULL COMMENT '邮件标题',
  `edmfilename` varchar(64) NOT NULL COMMENT '邮件文件名',
  `memberids` longtext,
  `membergroup` varchar(16) NOT NULL COMMENT '接收会员组',
  `taskcomment` varchar(256) NOT NULL COMMENT '任务描述',
  `taskcoupon` varchar(32) NOT NULL COMMENT '邮件中包含的优惠券码',
  `tasklog` varchar(256) NOT NULL COMMENT '任务执行日志',
  `testmemberid` varchar(128) DEFAULT NULL COMMENT '测试邮件用户ID',
  `edmsent` int(16) NOT NULL DEFAULT '0' COMMENT '总发送数',
  `edmrealsent` int(16) NOT NULL COMMENT '发送成功数',
  `taskschedule` int(10) NOT NULL DEFAULT '0' COMMENT '计划执行时间',
  `createdatetime` int(10) NOT NULL COMMENT '任务生成时间',
  `startdatetime` int(10) NOT NULL COMMENT '开始执行时间',
  `finishdatetime` int(10) NOT NULL COMMENT '任务完成时间',
  `taskstatus` tinyint(1) NOT NULL DEFAULT '0' COMMENT '任务状态:0=初始化/1=开始执行/4=执行失败/8=执行完成',
  PRIMARY KEY (`taskid`)
) ENGINE= FEDERATED  DEFAULT CHARSET=utf8 CONNECTION='mysql://lltro:dvj7HIa73gw6k@10.8.75.167:3306/lltmmc/edm_tasks';

3. 联合查询

一切都准备好之后我们就可以开始开始联合查询了。

不过需要注意的是 FEDERATED 引擎不支持索引,每次查询都是全表查询,所以千万不要使用子查询。

create view lltedm.v_edm_reasders as select edm_subject, count(*) as read_count from lltedm.edm_readers group by edm_subject;

SELECT DATE(FROM_UNIXTIME(startdatetime)) as 'Date', edmsent as 'Edm Sent', edmrealsent as 'Edm Real Sent', 
IF(edmrealsent > 0, concat((edmrealsent / edmsent) * 100, '%'), '') as 'Success Rate', 
read_count as 'Edm Read',
IF(edmrealsent > 0, concat(read_count / edmrealsent * 100, '%'), '') as 'Open Rate',
edmsubject as 'Edm Subject', edmfilename as 'Edm File Name'
FROM lltedm.edm_tasks 
join lltedm.v_edm_reasders on (v_edm_reasders.edm_subject = substring_index(substring_index(edmfilename, '/', -1), '.', 1))
WHERE startdatetime >= UNIX_TIMESTAMP('2016-01-01')
ORDER BY startdatetime;

参考:

https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html

https://stackoverflow.com/questions/810349/mysql-cross-server-select-query

 865 total views

MySQL 5.7 JSON 简介

从 MySQL 5.7.8 版本起,MySQL 开始支持原生 JSON 格式。

1. 存储 JSON 数据

首先我们要把字段类型设置为 json,这样存储的时候才会检查插入的 JSON 数据是否符合规范,不符合规范会报错。至于如何生成 JSON 数据,我们可以使用 PHP 的 json_encode() 方法,也可以使用 MySQL 的 JSON_ARRAY() 和 JSON_OBJECT() 方法。

create table json_data(json_data json);
insert into json_data(json_data) values ('{"name":"Victor Tang","phone":"13866001386","email":"tanghengzhi@live.com"}');
insert into json_data(json_data) values (JSON_OBJECT("name", "Victor Tang", "phone", "13866001386", "email", "tanghengzhi@live.com"));

2. 查询 JSON 数据

查询 JSON 数据里面的值不再需要使用 LIKE % % 这种 trick 的解决方法。现在可以使用 JSON_EXTRACT(column, path) 和 从 MySQL 5.7.9 版本开始支持的 column->path 两种查询语法。当然前提是查询的字段类型必须设置为 json。

select JSON_TYPE(json_data) from json_data;

select json_data->"$.email" from json_data;

select JSON_EXTRACT(json_data, "$.email") from json_data;

3. 修改 JSON 数据

修改 JSON 数据也不需要先把数据取出来,使用 PHP 的 json_decode() 方法解码,修改,然后再用 json_decode() 方法编码,最后更新数据库。MySQL 5.7 提供了 JSON_SET(), JSON_INSERT(), JSON_REPLACE(), JSON_REMOVE() 等一系列方法。几个方法的区别是:JSON_SET() 支持插入和更新,JSON_INSERT() 只支持插入,JSON_REPLACE() 只支持更新。JSON_REMOVE() 是删除 JSON 数据中的指定内容,没什么好说的。

select JSON_SET(json_data, "$.country", "China", "$.phone", "138****1386") from json_data;

select JSON_INSERT(json_data, "$.country", "China", "$.phone", "138****1386") from json_data;

select JSON_REPLACE(json_data, "$.country", "China", "$.phone", "138****1386") from json_data;

select JSON_REMOVE(json_data, "$.country", "$.phone") from json_data;

4. 已知的问题

综上所述,MySQL 5.7 对于 JSON 的支持已经很完善了,达到了生产可用的水平。但是有一些已知的问题,使用的时候还是需要注意一下。

SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');

在 MySQL 5.7 中执行以上 SQL 语句,返回结果是 {“key1”: 1, “key2”: “abc”}。

JSON.stringify({'key1': 1, 'key2': 'abc', 'key1': 'def'});

而在你的 Chrome 浏览器中执行以上代码,返回结果是 {“key1″:”def”,”key2″:”abc”}。

MySQL 给出的官方解释是:

This “first key wins” handling of duplicate keys is not consistent with RFC 7159. This is a known issue in MySQL 5.7, which is fixed in MySQL 8.0. (Bug #86866, Bug #26369555)

所以在使用 JSON_OBJECT() 方法之前要保证你的 JSON 对象没有重复的 key,如果有请先进行去重操作。


参考:

https://dev.mysql.com/doc/refman/5.7/en/json.html

 748 total views

MySQL 5.7 group by

从MySQL 5.6 升级到 MySQL 5.7,我相信大家或多或少都遇到了一点 group by 的问题,也许是 `this is incompatible with sql_mode=only_full_group_by` 的报错,也许是 group by 查询出来的结果莫名其妙的和之前版本不一样。

在开始之前我们先来造一些数据:

SET NAMES utf8mb4;
CREATE TABLE `books` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
`press` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `books` (`id`, `name`, `press`) VALUES
(1, '太宰治作品精选集:潘多拉之匣', '上海译文出版社'),
(2, '霜夜', '广东花城出版社');
CREATE TABLE `prices` (
`book_id` int(11) NOT NULL,
`seller` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
`price` decimal(10,2) NOT NULL,
KEY `book_id` (`book_id`),
CONSTRAINT `prices_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `prices` (`book_id`, `seller`, `price`) VALUES
(1, '亚马逊', 28.40),
(1, '新华文轩网络书店', 26.30),
(2, '亚马逊', 26.20),
(2, '新华文轩网络书店', 24.20),
(2, '中关村图书大厦专营店', 26.30),
(2, '王府井书店', 26.80);

现在我们需要查出每本书最便宜的价格和对应的卖家。

MySQL 5.6 之前的版本,我们可以这样写查询语句:

select id, name, press, seller, price from
(select id, name, press, seller, price from books join prices on (prices.book_id = books.id) order by id asc, prices.price asc)
as t group by id

但是 MySQL 5.7 直接报错了:

查询出错 (1055): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.seller' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

因为从 MySQL 5.7.5 开始 sql_mode 默认开启了 ‘ONLY_FULL_GROUP_BY’,我们关掉 sql_model, 然后再执行上面的查询语句。

虽然没有报错,但是结果也和我们预想的不一样。这是为什么呢?我们用 explain 和 show warning 来查一下 MySQL 的执行计划。

/* select#1 */ select `db`.`books`.`id` AS `id`,`db`.`books`.`name` AS `name`,`db`.`books`.`press` AS `press`,`db`.`prices`.`seller` AS `seller`,`db`.`prices`.`price` AS `price` from `db`.`books` join `db`.`prices` where (`db`.`prices`.`book_id` = `db`.`books`.`id`) group by `db`.`books`.`id`

原来 MySQL 在执行的时候会自动忽略子查询里面的 order by 语句。那要怎么写查询语句呢,仔细查询文档发现 group_concat 里面可以写 order by,然后我们再用 substring_index 取出用 , 分割的第一个字符串。

select id, name, press, substring_index(group_concat(seller order by price), ',', 1) as seller, substring_index(group_concat(price order by price), ',', 1) as price
from books join prices on (prices.book_id = books.id) group by id

问题解决了,所以 MySQL 5.7 虽好,还是要谨慎升级,特别要注意检查一下以前代码里面的 group by 语句。


参考:

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

 827 total views,  1 views today

Migrating from MariaDB to MySQL

网上从 MySQL 迁移到 MariaDB 的教程很多,但是从 MariaDB 迁移到 MySQL 的教程却很少。最近刚好花时间把测试服务器上的 MariaDB 迁移到 MySQL 了,所以我打算记录一下操作过程,以供参考。


1. Backup your data

一定要备份数据!不要在生产环境操作!

一定要备份数据!不要在生产环境操作!

一定要备份数据!不要在生产环境操作!

重要的事情说三遍,请一定要再做好准备之后再继续以下操作。

2. Stop MariaDB

首先,停止 MariaDB 服务。

service mysql stop

然后,卸载 MariaDB。

yum remove mariadb mariadb-server

需要注意的是,使用 yum remove 不一定能够完全卸载干净,可以使用 rpm -qa 检查一下是否有遗漏。

rpm -qa | grep -i maria
yum remove MariaDB-common-5.5.57-1.el6.x86_64

3. Install MySQL

MariaDB 卸载干净之后就可以开始安装 MySQL 了。

wget https://dev.mysql.com/get/mysql80-community-release-el6-1.noarch.rpm
rpm -Uvh mysql80-community-release-el6-1.noarch.rpm
yum install mysql-community-server

启动 MySQL 服务。

service mysqld start

尽快修改密码。(注意:新密码一定要不少于 8 位,并且包含大写字母、小写字母、数字和特殊字符。)

sudo grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

最后再重新导入备份的数据,一切就大功告成了!


参考:

https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-in-mysql-or-mariadb
https://stackoverflow.com/questions/33362904/completely-remove-mariadb-or-mysql-from-centos-7-or-rhel-7
https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

 637 total views