二月读书计划:
1.弃猫 当我谈起父亲时,村上春树,花城出版社,2021
2.当我们谈论爱情时我们在谈论什么,雷蒙德•卡佛,南海出版公司,2020
3.大教堂,雷蒙德•卡佛,南海出版公司,2020
97 total views, 2 views today
一个程序员的日常:读书,旅行,写代码。
二月读书计划:
1.弃猫 当我谈起父亲时,村上春树,花城出版社,2021
2.当我们谈论爱情时我们在谈论什么,雷蒙德•卡佛,南海出版公司,2020
3.大教堂,雷蒙德•卡佛,南海出版公司,2020
97 total views, 2 views today
今天在 Stack Overflow 上看到这样一个问题:
How SUM the result of an array returned by jsn_extract?
参考:
https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
90 total views, 3 views today
PHP 8.0 发布了,官网甚至还上线了新的宣传页面。作为一个 PHP 程序员,还是要学习一下的。
htmlspecialchars($string, ENT_COMPAT | ENT_HTML401, 'UTF-8', false);
htmlspecialchars($string, double_encode: false);
现在可以用 PHP 原生语法来使用结构化的元数据,而非 PHPDoc 声明。
class PostsController
{
/**
* @Route("/api/posts/{id}", methods={"GET"})
*/
public function get($id) { /* ... */ }
}
class PostsController
{
#[Route("/api/posts/{id}", methods: ["GET"])]
public function get($id) { /* ... */ }
}
更少的样板代码来定义并初始化属性。
class Point {
public float $x;
public float $y;
public float $z;
public function __construct(
float $x = 0.0,
float $y = 0.0,
float $z = 0.0
) {
$this->x = $x;
$this->y = $y;
$this->z = $z;
}
}
class Point {
public function __construct(
public float $x = 0.0,
public float $y = 0.0,
public float $z = 0.0,
) {}
}
相较于以前的 PHPDoc 声明类型的组合, 现在可以用原生支持的联合类型声明取而代之,并在运行时得到校验。
class Number {
/** @var int|float */
private $number;
/**
* @param float|int $number
*/
public function __construct($number) {
$this->number = $number;
}
}
new Number('NaN'); // Ok
class Number {
public function __construct(
private int|float $number
) {}
}
new Number('NaN'); // TypeError
新的 match 类似于 switch,并具有以下功能:
switch (8.0) {
case '8.0':
$result = "Oh no!";
break;
case 8.0:
$result = "This is what I expected";
break;
}
echo $result;
//> Oh no!
echo match (8.0) {
'8.0' => "Oh no!",
8.0 => "This is what I expected",
};
//> This is what I expected
现在可以用新的 nullsafe 运算符链式调用,而不需要条件检查 null。 如果链条中的一个元素失败了,整个链条会中止并认定为 Null。
$country = null;
if ($session !== null) {
$user = $session->user;
if ($user !== null) {
$address = $user->getAddress();
if ($address !== null) {
$country = $address->country;
}
}
}
$country = $session?->user?->getAddress()?->country;
PHP 8 比较数字字符串(numeric string)时,会按数字进行比较。 不是数字字符串时,将数字转化为字符串,按字符串比较。
0 == 'foobar' // true
0 == 'foobar' // false
现在大多数内部函数在参数验证失败时抛出 Error 级异常。
strlen([]); // Warning: strlen() expects parameter 1 to be string, array given
array_chunk([], -1); // Warning: array_chunk(): Size parameter expected to be greater than 0
strlen([]); // TypeError: strlen(): Argument #1 ($str) must be of type string, array given
array_chunk([], -1); // ValueError: array_chunk(): Argument #2 ($length) must be greater than 0
PHP 8 引入了两个即时编译引擎。 Tracing JIT 在两个中更有潜力,它在综合基准测试中显示了三倍的性能, 并在某些长时间运行的程序中显示了 1.5-2 倍的性能改进。 典型的应用性能则和 PHP 7.4 不相上下。
参考:
https://www.php.net/releases/8.0/index.php
185 total views, 1 views today
一月读书计划:
乌鸦:爱伦·坡短篇小说精选,爱伦坡,江西人民出版社,2017
161 total views, 1 views today
十二月读书计划:
1.Domain-Driven Design in PHP,Carlos Buenosvinos, Christian Soronellas,Keyvan Akbary,Packt Publishing,2017
677 total views, 1 views today
MySQL 8.0 引入了 窗口函数(window functions) ,我们看一下都有哪些功能。
Name | Description |
---|---|
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
497 total views, 1 views today
十一月读书计划:
1.房思琪的初恋乐园,林奕含,北京联合出版公司,2018
1,968 total views, 1 views today
Nginx 日志切割,在网上查了很多教程。还不如直接看 Ubuntu / CentOS 打包的 Nginx 日志切割配置靠谱。
sudo dnf install nginx
cat /etc/logrotate.d/nginx
/var/log/nginx/*log {
create 0664 nginx root
daily
rotate 10
missingok
notifempty
compress
sharedscripts
postrotate
/bin/kill -USR1 cat /run/nginx.pid 2>/dev/null
2>/dev/null || true
endscript
}
sudo apt install nginx
cat /etc/logrotate.d/nginx
/var/log/nginx/*.log {
daily
missingok
rotate 14
compress
delaycompress
notifempty
create 0640 www-data adm
sharedscripts
prerotate
if [ -d /etc/logrotate.d/httpd-prerotate ]; then \
run-parts /etc/logrotate.d/httpd-prerotate; \
fi \
endscript
postrotate
invoke-rc.d nginx rotate >/dev/null 2>&1
endscript
}
参考:
https://www.nginx.com/resources/wiki/start/topics/examples/logrotation/
112 total views, 2 views today
十月读书计划:
1.看见,柴静,广西师范大学出版社,2013
2,911 total views, 9 views today
九月读书计划:
1.多余的人,莱蒙托夫,云南美术出版社,2019
148 total views, 1 views today