Posts tagged with “tips”

MySQL: Delete All Duplicate Rows Except the Earliest One in One SQL

Written by Zhong Wei

You want to add a unique index to a table, and unfortunately, there are already many duplicate rows in it. Manually find and delete these rows is time-wasting and error-prone. So why won't we just write one SQL statement and quickly resolve it?

First try, I wrote the following statement, and it won't work: 

DELETE FROM PromotionSkus A 
WHERE 
	A.SkuId IN (SELECT SkuId FROM PromotionSkus B GROUP BY B.SkuId HAVING COUNT(B.SkuId) > 1) 
	AND 
	A.Id NOT IN (SELECT MIN(Id) FROM PromotionSkus C GROUP BY C.SkuId HAVING COUNT(C.SkuId) > 1);

AND this one below works!

DELETE FROM PromotionSkus A
WHERE 
	A.Id NOT IN (SELECT Id FROM (SELECT MIN(Id) AS Id, COUNT(SkuId) AS Total FROM PromotionSkus GROUP BY SkuId HAVING Total > 1) AS B)
	AND 
	A.SkuId IN (SELECT SkuId FROM (SELECT SkuId FROM PromotionSkus GROUP BY SkuId HAVING COUNT(SkuId) > 1) AS C);

The reason is well explained in this brilliant article.

Git Tips

Written by Zhong Wei

解决git 命令行把中文文件名显示成 \343\200\212类似乱码的问题

git config --global core.quotepath off

将变更加入缓冲区，但不加入空白（空格、制表符或者换行符）的变更

git diff -w | git apply --cached --ignore-whitespace

Reference

git diff 时排除掉某些不想看的文件

git diff -- . ':(exclude)db/irrelevant.php' ':(exclude)db/irrelevant2.php'

删除远端分支

git push origin --delete branchname

嫌打字麻烦的，可以使用以下简写命令

git push origin :branchname

发现文件丢了不知道谁删的，比如 yarn.lock , 用下面这个命令

git log -p -- yarn.lock

更多的技巧请访问我的 GotGit 群组

Using sed to insert a line after several lines after the match line

Written by Zhong Wei

最近一直在写 .net core，它有诸多优点我且不提，我实在头痛于手写注入服务时的模式化代码，因此动了心思要写个小脚本帮我自动化一些事情。我的想法是告诉我要加入的 ServiceName， 指定一个 Controller然后脚本自动在这个 Controller 里声明一个 private readonly 属性，自动修改类的construction 函数，自动注入这个服务，并在construction 函数体写上 _serviceName = serviceName;

在第一步，注入 private readonly 属性那里，我就绊了个跟头。用 sed 匹配到某行，在它之前，或者之后插入数据是简单的。但我匹配到 public class xxxController 这行之后是单独一行 {，而我要加的变量要放到 {这行之后。网上一通搜索，试图找到定位到某行之后N行的命令，然而并没有这样的命令。然而功夫不负有心人，我最后还是找到了一个解决方案，那就是

sed '/pattern/!b;n;n;i\something'

具体解释见 Reference

ubuntu 20.04 手工生成通配符ssl证书

Written by Zhong Wei

其实很简单，但因为网上有流传很广的教程并不适用于最新的 ubuntu 20.04，造成我也走了一点弯路。 certbot-auto 官方已停止维护，但网上广为流传的教程仍然推荐使用 certbot-auto 来生成证书。这是问题的根源。正确的姿势是这样的

sudo apt install certbot
sudo certbot certonly --manual -d *.yourdomain.com -d youdomain.com --preferred-challenges dns-01 --server https://acme-v02.api.letsencrypt.org/directory

照提示一步一步来就好了。是不是超级简单？

Attention: the first definition is used when you have more than one same host label in your .ssh/config file

Written by Zhong Wei

I originally thought the later should be used. I was wrong.