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中写入这样的警告信息

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

MySql5.7 子查询 order by 失效

1
2
3
4
5
6
7
#获取用户最新的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 多个联表查询统计时, 数据统计不准确

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- 测试表及数据 
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 排名、并列排名、查询指定用户名次

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- 用户表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- 测试数据
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (1, 'A', 7);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (2, 'B', 3);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (3, 'C', 4);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (4, 'D', 0);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (5, 'E', 4);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (6, 'F', 9);
INSERT INTO `test`.`user`(`id`, `name`, `score`) VALUES (7, 'G', 2);

-- 说明
@rank := @rank + 1 中, := 是赋值的作用, 意思是先执行 @rank + 1, 然后把值赋给 @rank
(SELECT @rank := 0) r 意思是设置 rank 字段的初始值为0

-- 1. 查询结果后排序
SELECT
u.*,
@rank := @rank + 1 AS rank
FROM
(SELECT @rank := 0) r,
`user` u
ORDER BY u.score DESC;

-- 2. 并列排名
SELECT
u.*,
(CASE
WHEN @prev = u.score THEN @rank
WHEN @prev := u.score THEN @rank := @rank + 1
WHEN @prev = 0 THEN @rank := @rank + 1
END) AS rank
FROM
(SELECT @rank := 0, @prev := NULL) r,
`user` u
ORDER BY
u.score DESC;

-- 3. 指定用户排名
SELECT
*
FROM
(
SELECT
u.*,
(CASE
WHEN @prev = u.score THEN @rank
WHEN @prev := u.score THEN @rank := @rank + 1
WHEN @prev = 0 THEN @rank := @rank + 1
END) AS rank
FROM
(SELECT @rank := 0, @prev := NULL) r,
`user` u
ORDER BY
u.score DESC
) ur
WHERE
ur.id = 3;

MySql COUNT 加 IF 条件

1
2
3
#以下两条SQL查询结果一致
SELECT COUNT(1) FROM user WHERE account_status = 0;
SELECT COUNT(IF(account_status = 0, true, null)) FROM user;

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
// 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#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 的休眠时间过后, 即可看到测试结果

0%