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

460 total views, no 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/

335 total views, no 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

222 total views, no views today

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

295 total views, no views today

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

261 total views, no views today

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

356 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/

236 total views, no views today

mysql explain 实例分析

今天有同学在群里面问了这样一个问题:

select t1.*,t2.* from T1 t1 ,T2 t2 where t1.a = t2.a;

select t1.*,t2.* from T1 t1 join T2 t2 on t1.a = t2.a;
是一样的吗?

通过伟大的搜索引擎,我找到这样一个回答:

FROM T1,T2是ANSI SQL-89的旧语法,用逗号分隔FROM子句出现的表名,没有JOIN关键字,也没有ON子句,它只支持交叉联接和内联接,不支持外联接;如果没有指定联接条件就是一个交叉联接。
新的ANSI SQL-92去掉了逗号,引入了JOIN和ON,支持交叉联接、内联接和外联接。
优化器为使用上面两种语法的语句生成了相同的执行计划,性能是一样的。

虽然提问的同学对这个答案很满意,但是还是有同学对这个答案提出了自己的怀疑。于是我试图去证明这个答案的正确性。

由于已经找不到 SQL-89 的语法标准,所以关于 SQL-89 和 SQL-92 的比较只好暂时搁置。

我们来看一下 MySQL 优化器为上面两种语法的语句的执行计划是不是一样的:

mysql> explain extended select wp_users.display_name, wp_posts.post_title from wp_users, wp_posts where wp_users.ID = wp_posts.post_author;

B17B541C-A33A-419B-ACF2-5FF2118EEA35

mysql> show warnings;

B073A197-A1C3-4705-B2CF-D75CAE050539

mysql> explain extended select wp_users.display_name, wp_posts.post_title from wp_users join wp_posts on wp_users.ID = wp_posts.post_author;

3E6D5496-AAD3-4532-8FD3-913316BB3860

mysql> show warnings;

7551B96F-193D-4D48-A5D5-E75C8B64C008

我直接使用 wordpress 数据库,分别使用两种语法写了两条查询语句:

select wp_users.display_name, wp_posts.post_title from wp_users, wp_posts where wp_users.ID = wp_posts.post_author

select wp_users.display_name, wp_posts.post_title from wp_users join wp_posts on wp_users.ID = wp_posts.post_author

使用 mysql explain extended 和 show warnings 命令得到 mysql 优化器优化后的查询语句是一模一样的。

select `blog`.`wp_users`.`display_name` AS `display_name`,`blog`.`wp_posts`.`post_title` AS `post_title` from `blog`.`wp_users` join `blog`.`wp_posts` where (`blog`.`wp_posts`.`post_author` = `blog`.`wp_users`.`ID`)

参考


T-SQL查询笔记4: FROM T1,T2与联接的区别
http://www.cnblogs.com/gdjlc/archive/2012/03/06/2382820.html

MySQL执行计划解读
http://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html

546 total views, no views today