MySQL 相关问题

MySql 报 could not be resolved: Temporary failure in name resolution

原因：MYSQL Server在本地内存中维护了一个非本地的Client TCP cache, 这个cache中包含了远程Client的登录信息, 比如IP地址、hostname等信息
如果Client连接到服务器后, MySQL首先会在本地TCP池中根据IP地址解析客户端的hostname或者反解析, 如果解析不到, 就会去DNS中进行解析, 如果还是解析失败, 就是在error log中写入这样的警告信息

#修改mysql配置文件
[mysqld]
skip-host-cache
skip-name-resolve

MySql5.7 子查询 order by 失效

#获取用户最新的30条评论
SELECT * FROM (SELECT * FROM comment ORDER BY id DESC) c GROUP BY c.user_id ORDER BY c.id DESC LIMIT 30

#问题：MySql5.5 与 MySql5.7 的执行结果不一样, 因为 5.7 会把order by优化掉

#解决：使用DISTINCT关键字
SELECT * FROM (SELECT DISTINCT * FROM comment ORDER BY id DESC) c GROUP BY c.user_id ORDER BY c.id DESC LIMIT 30

MySql 多个联表查询统计时, 数据统计不准确

-- 测试表及数据 
CREATE TABLE `vote` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '投票活动ID',
  `title` varchar(128) NOT NULL COMMENT '投票活动标题',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票活动表';

CREATE TABLE `vote_option_record` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `vote_id` int(11) NOT NULL COMMENT '投票活动ID',
  `option_id` int(11) NOT NULL COMMENT '投票选项ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `vote_time` datetime NOT NULL COMMENT '投票时间',
  PRIMARY KEY (`id`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户投票记录表';

CREATE TABLE `vote_browse` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '浏览ID',
  `vote_id` int(11) NOT NULL COMMENT '投票活动ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `count` int(11) unsigned NOT NULL COMMENT '浏览次数',
  `generate_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票活动浏览统计';

INSERT INTO `test`.`vote`(`id`, `title`) VALUES (1, '投票活动标题');
INSERT INTO `test`.`vote_option_record`(`id`, `vote_id`, `option_id`, `user_id`, `vote_time`) VALUES (1, 1, 1, 1, '2019-07-01 15:39:07');
INSERT INTO `test`.`vote_option_record`(`id`, `vote_id`, `option_id`, `user_id`, `vote_time`) VALUES (2, 1, 1, 2, '2019-07-01 16:03:43');
INSERT INTO `test`.`vote_option_record`(`id`, `vote_id`, `option_id`, `user_id`, `vote_time`) VALUES (3, 1, 2, 1, '2019-07-02 15:39:25');
INSERT INTO `test`.`vote_option_record`(`id`, `vote_id`, `option_id`, `user_id`, `vote_time`) VALUES (4, 1, 2, 2, '2019-07-02 15:39:33');
INSERT INTO `test`.`vote_browse`(`id`, `vote_id`, `user_id`, `count`, `generate_time`) VALUES (1, 1, 1, 1, '2019-07-01 00:00:00');
INSERT INTO `test`.`vote_browse`(`id`, `vote_id`, `user_id`, `count`, `generate_time`) VALUES (2, 1, 2, 1, '2019-07-01 00:00:00');
INSERT INTO `test`.`vote_browse`(`id`, `vote_id`, `user_id`, `count`, `generate_time`) VALUES (3, 1, 1, 3, '2019-07-02 00:00:00');
INSERT INTO `test`.`vote_browse`(`id`, `vote_id`, `user_id`, `count`, `generate_time`) VALUES (4, 1, 2, 4, '2019-07-02 00:00:00');
INSERT INTO `test`.`vote_browse`(`id`, `vote_id`, `user_id`, `count`, `generate_time`) VALUES (5, 1, 3, 5, '2019-07-02 20:28:25');

-- 统计投票活动的总投票数和总访问次数
SELECT
	v.id voteId,
	v.title AS voteTitle,
	-- 统计总票数
	COUNT(vor.id) AS votes,
	-- 统计总访问次数
	SUM(vb.count) AS views
FROM
	vote v
	LEFT JOIN vote_option_record vor ON v.id = vor.vote_id
	LEFT JOIN vote_browse vb ON v.id = vb.vote_id
GROUP BY
	v.id

-- 查询结果
+--------+--------------+-------+-------+
| voteId | voteTitle    | votes | views |
+--------+--------------+-------+-------+
|      1 | 投票活动标题 |    20  | 56    |
+--------+--------------+-------+-------+


-- 问题：投票活动的总票数应该为4票, 总访问次数为14次, 然后统计后的结果并不准确, 这是因为多表查询时产生了笛卡尔积, 
-- 实际统计时的记录条数有 vote_option_record (4条) * vote_browse(5条) 共20条记录, 
-- 所以 votes = vote_option_record(4条) * vote_browse(5条) = 20 票, views = vote_browse(14次) * vote_option_record(4条) = 56 次

-- 解决方案
-- 1. COUNT() 通过使用 DISTINCT 关键字
COUNT(DISTINCT vor.id) AS votes
-- 2. SUM() 则不适用 DISTINCT, 因为会过滤掉字段值一样的数据, 可以通过运算计算出正确的数据
SUM(vb.count) / COUNT(DISTINCT vor.id) AS views
-- mysql 除数为 0 时会返回 NULL
IF(COUNT(vor.id), SUM(vb.count) / COUNT(DISTINCT vor.id), 0) AS views

-- 完整SQL
SELECT
	v.id voteId,
	v.title AS voteTitle,
	COUNT(DISTINCT vor.id) AS votes,
	FLOOR(IF(COUNT(vor.id), SUM(vb.count) / COUNT(DISTINCT vor.id), 0)) AS views
FROM
	vote v
	LEFT JOIN vote_option_record vor ON v.id = vor.vote_id
	LEFT JOIN vote_browse vb ON v.id = vb.vote_id
GROUP BY
	v.id

MySql server has gone away 问题重现及断线重连

经常出现在长连接项目或者cli模式运行时间过长, 如果没处理好断线重连, 则会出现此问题

// test.php
/**
 * @param PDO $dbh
 *
 * @return string
 */
function ping(PDO $dbh)
{
    $message = '';

    try {
        $dbh->getAttribute(PDO::ATTR_SERVER_INFO);
    } catch (\PDOException $e) {
        $message = $e->getMessage();
    }

    return $message;
}

$config = [
    'host' => '127.0.0.1',
    'dbname' => 'test',
    'user' => 'root',
    'password' => 'root',
];
$options = [
    // 主动抛出 PDOException 异常
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];
$sql = 'SELECT * FROM test';

$dbh = new PDO("mysql:host={$config['host']};dbname={$config['dbname']}", $config['user'], $config['password'], $options);
$result = $dbh->query($sql);
var_dump($result, ping($dbh));

sleep(30);

$message = ping($dbh);
if (!empty($message)) {
    echo "MySql错误：{$message}, 正在重连..." . PHP_EOL;
    $dbh = new PDO("mysql:host={$config['host']};dbname={$config['dbname']}", $config['user'], $config['password'], $options);
}

$result = $dbh->query($sql);
var_dump($result, ping($dbh));

测试方式

#1. 运行 test.php
php test.php

#2. 运行后在 mysql 命令行模式下, 查看当前有哪些连接
mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
| 95 | root | localhost:58021 | test | Query   |    0 | starting | show processlist |
| 96 | root | localhost:58024 | test | Sleep   |    4 |          | NULL             |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.02 sec)

#3. 杀死指定连接, ID = 96, 此连接是 test.php 内的 mysql 连接

#4. 等 test.php 的休眠时间过后, 即可看到测试结果