利用MySQL全文搜尋的BOOLEAN MODE排除某些字元

目的:
搜尋java字元時，須排除JavaScript和Javanese

參考以下這邊文章
https://medium.com/@changhengliou/%E7%94%A8mysql-fulltext-search%E5%BB%BA%E7%AB%8B%E7%B0%A1%E6%98%93%E6%90%9C%E5%B0%8B%E5%BC%95%E6%93%8E-80659c28ec19
先到https://dumps.wikimedia.org/enwiki/latest/下載enwiki-latest-pages-articles.xml.bz2
(繁中 https://dumps.wikimedia.org/zhwiki/latest/ )
使用bzip2 -d enwiki-latest-pages-articles.xml.bz2指令解壓縮後
再用wikiextractor匯出成json格式(需要執行很久)
指令wikiextractor --json /wikidump/rawdata/enwiki-latest-pages-articles.xml
再用原作者的工具sqlgen匯入MySQL資料庫(需要執行很久)

SELECT `url` ,`title` , `text`

FROM `PagesArticles20210302`

WHERE MATCH(`title`, `text`) AGAINST ('Java' IN NATURAL LANGUAGE MODE)

AND

MATCH(`title`, `text`) AGAINST ('>eclipse >netbean -javaese -javanese -JavaScript'  IN BOOLEAN MODE);

-- 說明: -javanese:表示排除這個字元；>eclipse表示增加這個字元權重；< xxyy表示減少這個字元權重

ref:
https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

MySQL 8.0預設密碼加密方式由原本mysql_native_password 改成 caching_sha2_password

# MySQL 8.0預設密碼加密方式由原本mysql_native_password 改成 caching_sha2_password

# 怕Client端連線有問題，先設定為IDENTIFIED WITH mysql_native_password

ALTER USER 'mysqluser1'@'%' IDENTIFIED WITH mysql_native_password BY '自訂密碼';

GRANT ALL ON *.* TO 'mysqluser1'@'%';

CREATE USER 'mysqluser2'@'%' IDENTIFIED WITH mysql_native_password BY '自訂密碼';

GRANT ALL ON *.* TO 'mysqluser2'@'%';

FLUSH PRIVILEGES;


調整前Client端連線的錯誤訊息：

Retrieval of the RSA public key is not enabled for insecure connections.

使用podman指令建立Persistent Storage的MySQL容器資料庫，並藉由systemctl設為Host重開機自動啟動

環境：RHEL 8.3
目的：透過podman建立一個Persistent Storage的MySQL 8.0資料庫，並藉由systemctl設為Host重開機自動啟動
備註1：podman run指令不支援--restart=always(因為podman有別於Docker架構，不使用Daemon機制(daemonless Container)，無法透過Daemon機制去自動重啟)
參考資料 https://igene.tw/podman-intro
備註2：如果rm刪除容器重新建立，需要重新產生 podman generate systemd --name mysql-80-persist > mysql80-container.service

AD server和DB透過DNS SRV達成高可用性

※本文章只是網路找到的解決方案，尚未實測或導入 ※

https://serverfault.com/questions/374154/how-to-make-ad-highly-available-for-applications-that-use-it-as-an-ldap-service

The correct way to do this is to use the DNS SRV records to look up the domain controller names and ports, as well as ascertain which servers to use in what order. Unfortunately, not many LDAP applications seem to support SRV record lookups.

The SRV record for Active Directory domain controllers is _ldap._tcp.domain.tld. This will return a list of hosts and ports, as well as a priority and weight for each (these values can be set using Group Policy) that together indicate which server to use.

也意外發現資料庫也開始支援DNS SRV的方式達成高可用性
MySQL 8.0.19
https://mp.weixin.qq.com/s/G3dW_GPDd1l1MJ86c7iFTA
我还在想这事儿就应该由 MySQL Connector 来实现访问链路的 Failover 和 Loadbalance， 现在有了 DNS SRV 的支持，router 不必和应用端绑定部署，也可以省了 VIP 和负载均衡，MySQL InnoDB Cluster 方案更加完善，配合 consul 等服务发现组件，更容易适配 service mesh 架构。

PostgreSQL 12.0
https://mp.weixin.qq.com/s/hGeGiqm3D52aQJsS61QoGg

不同資料庫SQL Script語法轉換器

線上版
http://www.sqlines.com/online

軟體版(免安裝)
http://www.sqlines.com/download
SQLines SQL Converter is an open source tool (Apache License 2.0) that helps you convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers.

原始碼
https://github.com/dmtolpeko/sqlines

SQL Fiddle線上執行SQL語法的網站

http://sqlfiddle.com

自己做的DEMO:
http://sqlfiddle.com/#!6/8ac02/1/0

另外作者有把程式碼Open Source
程式碼
https://github.com/jakefeasel/sqlfiddle2

程式碼(舊版)
https://github.com/jakefeasel/sqlfiddle

各種資料庫Select first rows的方式

SQL - Select first 10 rows only? - Stack Overflow
http://stackoverflow.com/questions/1891789/sql-select-first-10-rows-only

各種資料庫List All Tables

How to List All Tables and Describe Tables in Oracle, MySQL, DB2 and PostgreSQL
http://onewebsql.com/blog/list-all-tables

使用PIVOT函數建立各月份銷售統計報表

2014-08故意沒把NULL改成0
2014-09使用ISNULL函數，其他月份使用COALESCE函數
COALESCE函數是ANSI SQL-92標準，ISNULL函數不是標準
另外SQL Server 2005之後才支援PIVOT函數
Oracle 11g之後也支援函數
MySQL沒有PIVOT函數，要使用GROUP_CONCAT

ref:
比較 ISNULL() 函數以及 COALESCE() 函數
SQL server : Replacing NULL with 0 in a query - Stack Overflow
Databases that support PIVOT/UNPIVOT syntax:

下載Adventure Works 2014 Sample Databases:
https://msftdbprodsamples.codeplex.com/releases/view/125550

Creating UFT-8 database

http://www.manydesigns.com/en/portofino/portofino3/3_1_x/installation-guide/creating-the-database

MySQL Documentation: MySQL Reference Manuals

http://dev.mysql.com/doc/

(MySQL)group by

加上having
找出銷售額大於300000店鋪(sid)

(MySQL) AVG

(MySQL)修改Decimal

Decimal to Decimal:

Varchar to Decimal:

p.s.
DB2指令(好像只能9.1之後版本):
alter table alter column set data type decimal(x,y).