Posts tagged with “tips”

Keep the the same order as input data when using `WHERE fieldName IN(fieldVal1, fieldVal2)`

Don't waste time; here's the sample code with the Oracle database.

SELECT * FROM SampleTable WHERE sample_field in (7,3,4,6) ORDER BY INSTR(',7,3,4,6,',  ',' || sample_field || ',');

MSYS2 TIPS

You might be surprised why I am using MSYS2 so much these days. In short, my new job doesn't allow me to use Linux at work. I cannot bear the CMD.EXE and PWSH.EXE, they might be great tools for someone else, but definitely not for me.

  1. If you visit a shared folder like /c/vagrant, you will get the infamous Too many levels of symbolic links error message. Fortunately, we have a solution: add a new user Environment variable MSYS=nonativeinnerlinks. I assume you know how to add a user environment to the windows system. If it doesn't take effect, save your current work and reboot. I didn't reboot my Windows 11 VM, but I did have restarted the Windows terminal application to ensure the new MSYS2 terminal will work with the links!
  2. If you prefer using Git for windows in MSYS2 but don't want to install GitBash because GitBash is also built on MSYS2, Install Git for windows inside MSYS2 will help you! BTW, git_bash_for_windows_is_based_on_msys2_why_not is another very good reference for this topic. I actually got the previous link from the latter article.
  3. Change the home directory to /c/Users/your-name. If you copy from the following, don't forget to change david.wei to your Windows username.
    $ cat /etc/nsswitch.conf
# Begin /etc/nsswitch.conf
passwd: db
group: db
db_enum: cache builtin
#db_home: cygwin desc
db_home: env windows /c/Users/david.wei
db_shell: cygwin desc
db_gecos: cygwin desc
# End /etc/nsswitch.conf
  4. If you run cmd.exe in a msys2 terminal, that %PATH% environment will inherit from the PATH environment in the current bash session.
  5. put export MSYS="winsymlinks:lnk" into your .bashrc to get a similar behaviour when you do ln -s Reference

Git Tips: Remove tracking branches no longer on remote

My colleage Joe asked me tonight, "how to remove those branches that no longer exist on remote?"

In short, you have two options

  1. run git remote prune origin at times
  2. run git config --global fetch.prune true command to config your git to delete those branches every time when you run git fetch

I prefer the second option, how about you?

Reference

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

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.

Another mysql tip: using mysqldump export a table with one line one row. 

mysqldump --databases YourDataBaseName --tables YourTableName --skip-extended-insert

Why do we need that? It is much easier to compare !

Git Tips

解决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

设置总是push到远端的同名分支，从此再也不用如此麻烦的敲 git push -u origin theSameBranchName，你只管 git push 就好！

git config --global push.default current

解决 git diff 时出现讨厌的 ^M

git config --global core.whitespace cr-at-eol

Reference

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