nginx配置：http { limit_req_zone $binary_remote_addr zone=limit:50m rate=10r/m; server { server_name xxx; error_page 503 @503; location @503 { rewrite ^(.*)$ /503.html break; } } }这种情况下，如果是GET请求触发限流，会返回自定义的503.html页面内容，但是如果是POST请求触发限流，则会返回405 Method Not Allowed。查看一下405产生的原因：http://nginx.org/en/CHANGES可以看到，自0.7.11版本开始，POST请求静态文件内容会返回405，但我们这请求的POST接口并不是静态文件内容，为何会触发呢？猜想是因为我们写了rewrite ^(.*)$ /503.html break;这条规则，即POST请求的不再是之前的接口而是这个503.html静态页面，因此，需要针对这个503自定义内容做一下修改：http { limit_req_zone $binary_remote_addr zone=limit:50m rate=10r/m; server { server_name xxx; error_page 503 @503; location @503 { if ( $request_method = POST ) { return 503 'json内容或者文字内容比如 {"code":503,"msg":"xxx"}'; } rewrite ^(.*)$ /503.html break; } } }这样修改后POST请求触发限流后不会返回405了
当你不再依赖从别人身上获得快乐时，才是一段美好生活的开始
准备数据mysql> create table product_orders(id int,product varchar(255),amount int); mysql> insert into product_orders(id, product, amount) values(1,'A',250),(2,'B',150),(3,'C',200), (4,'A',250),(5,'B',210),(6,'C',125), (7,'A',350),(8,'B',225),(9,'C',150); mysql> select * from product_orders; +------+---------+--------+ | id | product | amount | +------+---------+--------+ | 1 | A | 250 | | 2 | B | 150 | | 3 | C | 200 | | 4 | A | 250 | | 5 | B | 210 | | 6 | C | 125 | | 7 | A | 350 | | 8 | B | 225 | | 9 | C | 150 | +------+---------+--------+ 首先，我们将使用以下SQL查询对组（product列）中的每一行进行排名。mysql> SELECT id, product, amount, @product_rank := IF(@current_product = product, @product_rank + 1, 1) AS product_rank, @current_product := product FROM product_orders ORDER BY product, amount desc; +------+---------+--------+--------------+-----------------------------+ | id | product | amount | product_rank | @current_product := product | +------+---------+--------+--------------+-----------------------------+ | 7 | A | 350 | 1 | A | | 1 | A | 250 | 2 | A | | 4 | A | 250 | 3 | A | | 8 | B | 225 | 1 | B | | 5 | B | 210 | 2 | B | | 2 | B | 150 | 3 | B | | 3 | C | 200 | 1 | C | | 9 | C | 150 | 2 | C | | 6 | C | 125 | 3 | C | +------+---------+--------+--------------+-----------------------------+在上述查询中，我们首先按amount列将其组中的每条记录按降序排序，然后对其进行排名。 如果要按amount的升序对其进行排序，可以通过更改ORDER by子句来实现。SELECT id, product, amount, @product_rank := IF(@current_product = product, @product_rank + 1, 1) AS product_rank, @current_product := product FROM product_orders ORDER BY product, amount asc;接下来，我们将上述查询用作子查询，以选择每个组的前N行（例如，每个类别的前2行）。如何选择每个组的前2行这是使用上述方法为每个组选择前2行的SQL查询。 我们将上面的查询用作子查询，并选择等级小于或等于2的行。mysql> select id, product, amount from ( SELECT id, product, amount, @product_rank := IF(@current_product = product, @product_rank + 1, 1) AS product_rank, @current_product := product FROM product_orders ORDER BY product, amount desc) ranked_rows where product_rank<=2; +------+---------+--------+ | id | product | amount | +------+---------+--------+ | 7 | A | 350 | | 1 | A | 250 | | 8 | B | 225 | | 5 | B | 210 | | 3 | C | 200 | | 9 | C | 150 | +------+---------+--------+如何选择每个组的前10行同样，你可以使用以下查询从每个组中选择前10行。mysql> select id, product, amount from ( SELECT id, product, amount, @product_rank := IF(@current_product = product, @product_rank + 1, 1) AS product_rank, @current_product := product FROM product_orders ORDER BY product, amount desc) ranked_rows where product_rank<=10;