From: 011netservice@gmail.com Date: 2022-12-25 Subject: KeyWord-MySql.txt 歡迎來信交流, 訂購軟體需求. □ download and Installation 下載及安裝 MySQL https://www.mysql.com/ No login account neeeded to download community version from developer's page. mysql-installer-community-8.0.19.0.msi, 20200225 mysql-installer-community-8.0.21.0.msi, 20200813 mysql-installer-community-8.0.26.0.msi, 20210802 mysql-installer-community-8.0.28.0.msi, 20220209 mysql-installer-community-8.0.31.0.msi, 20221225 內含 Server, Shell, Router, Workbench, for excel, for Visual Studio, Connectors, Examples, tutorials, and Document, Notifier Authentication Method: 選擇 Use Strong Password Encryption for Authentication (RECOMMENDED) 相容舊版選這個 Use Legacy Authentication Method (Retain MySQL 5.x Compatibility) □ MySQL Reference Manual ref: https://dev.mysql.com/doc/ \CodeHelper\MySql\ReferenceManual\ 20220516-refman-8.0-en.a4.pdf, MySQL 8.0 Reference Manual 20220516-refman-5.7-en.a4.pdf, MySQL 5.7 Reference Manual □ MySQL Notifier 1.1 source https://github.com/mysql/mysql-notifier □ MySQL使用工具簡介 http://newsletter.ascc.sinica.edu.tw/news/read_news.php?nid=2050 □ Mariadb: https://mariadb.com/ mariadb-10.6.5-winx64.msi, 20220209 ---------- 2022-06-29, #### Set UTC time as default timestamp. 問題: 以下的語法中, 如何將時間設定為 UTC time ? `FUpdateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '異動時間', □ 方法1: 改變伺服器的 default timestamp 為 UTC time 注意: 改變伺服器的 default timestamp 為 UTC time 以後, 會影響伺服器中所有的 DB 運作, 代價不小! ref: https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp select utc_time(), utc_date(), utc_timestamp(), current_time(), current_date(), current_timestamp() # utc_time(), utc_date(), utc_timestamp(), current_time(), current_date(), current_timestamp() '01:19:28', '2022-08-14', '2022-08-14 01:19:28', '09:19:28', '2022-08-14', '2022-08-14 09:19:28' 未修改前的預設值: select @@global.time_zone, @@session.time_zone # @@global.time_zone, @@session.time_zone 'SYSTEM', 'SYSTEM' 修改後: mysql> SET @@session.time_zone='+00:00'; <---修改方法1 mysql> SELECT @@global.time_zone, @@session.time_zone; 'SYSTEM', '+00:00' 或是修改 my.cnf 檔案如下: [mysqld] **other variables** default_time_zone='+00:00' <---修改方法2 再重新啟動 mysql後查詢 select @@global.time_zone, @@session.time_zone # @@global.time_zone, @@session.time_zone '+00:00', '+00:00' □ 方法2: 使用 UTC_TIMESTAMP 這方法必須 MySql 8.0.13版本以上才支援, 並且在 2021-09-13 之前仍無法支援以下的 UPDATE 參數使用. creation_time TIMESTAMP NOT NULL DEFAULT (UTC_TIMESTAMP) ON UPDATE (UTC_TIMESTAMP) CREATE TABLE `blah` ( id int NOT NULL AUTO_INCREMENT, creation_time TIMESTAMP NOT NULL DEFAULT (UTC_TIMESTAMP) ) □ 方法3: 使用 Trigger 控制 這方法較佳, 建議使用. DELIMITER // CREATE TRIGGER `update_to_utc` BEFORE INSERT ON `my_table` FOR EACH ROW BEGIN set new.my_field=utc_timestamp(); END// DELIMITER ; ---------- 2022-06-29, #### MySql mysql binlog日誌自動清理及手動刪除-ghan 原文網址：https://kknews.cc/code/mgrmjz9.html 說明： 當開啟MySQL資料庫主從時，會產生大量如mysql-bin.00000* log的文件，這會大量耗費您的硬碟空間。 mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 … 有三種解決方法：1.關閉mysql主從，關閉binlog；2.開啟mysql主從，設置expire_logs_days；3.手動清除binlog文件，> PURGE MASTER LOGS TO 『MySQL-bin.010′; 實現： 1.關閉mysql主從，關閉binlog # vim /etc/my.cnf //注釋掉log-bin,binlog_format # Replication Master Server (default) # binary logging is required for replication # log-bin=mysql-bin # binary logging format - mixed recommended # binlog_format=mixed 然後重啟資料庫 2.重啟mysql,開啟mysql主從，設置expire_logs_days # vim /etc/my.cnf //修改expire_logs_days,x是自動刪除的天數，一般將x設置為短點，如10 expire_logs_days = x //二進位日誌自動刪除的天數。默認值為0,表示「沒有自動刪除」 此方法需要重啟mysql，附錄有關於expire_logs_days的英文說明 當然也可以不重啟mysql,開啟mysql主從，直接在mysql里設置expire_logs_days > show binary logs; > show variables like '%log%'; > set global expire_logs_days = 10; 3.手動清除binlog文件 # /usr/local/mysql/bin/mysql -u root -p > PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); //刪除10天前的MySQL binlog日誌,附錄2有關於PURGE MASTER LOGS手動刪除用法及示例 > show master logs; 也可以重置master，刪除所有binlog文件： # /usr/local/mysql/bin/mysql -u root -p > reset master; //附錄3有清除binlog時，對從mysql的影響說明 附錄： 1.expire_logs_days英文說明 Where X is the number of days you』d like to keep them around. I would recommend 10, but this depends on how busy your MySQL server is and how fast these log files grow. Just make sure it is longer than the slowest slave takes to replicate the data from your master. Just a side note: You know that you should do this anyway, but make sure you back up your mysql database. The binary log can be used to recover the database in certain situations; so having a backup ensures that if your database server does crash, you will be able to recover the data. 2.PURGE MASTER LOGS手動刪除用法及示例,MASTER和BINARY是同義詞 > PURGE {MASTER | BINARY} LOGS TO 'log_name' > PURGE {MASTER | BINARY} LOGS BEFORE 'date' 刪除指定的日誌或日期之前的日誌索引中的所有二進位日誌。這些日誌也會從記錄在日誌索引文件中的清單中被刪除MySQL BIN-LOG 日誌，這樣被給定的日誌成為第一個。 實例： > PURGE MASTER LOGS TO 'MySQL-bin.010'; //清除MySQL-bin.010日誌 > PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00'; //清除2008-06-22 13:00:00前binlog日誌 > PURGE MASTER LOGS BEFORE DATE_SUB( NOW, INTERVAL 3 DAY); //清除3天前binlog日誌BEFORE，變量的date自變量可以為'YYYY-MM-DD hh:mm:ss'格式。 3.清除binlog時，對從mysql的影響 如果您有一個活性的從屬伺服器，該伺服器當前正在讀取您正在試圖刪除的日誌之一，則本語句不會起作用，而是會失敗，並伴隨一個錯誤。不過，如果從屬伺服器是休止的，並且您碰巧清理了其想要讀取的日誌之一，則從屬伺服器啟動後不能複製。當從屬伺服器正在複製時，本語句可以安全運行。您不需要停止它們。 伺服器磁碟異常爆滿的原因及解決方法 https://kknews.cc/code/626ao6l.html 最近經常收到360網站監控發來的郵件，提示網站打不開，原因則無一例外都是磁碟爆滿，導致mysql停止工作，刪除部分文件後，網站恢復訪問，不過過不多久，網站又再次因為磁碟爆滿而打不開，在伺服器上找了一圈的原因，終於發現了幾個特別大的文件（linux查詢文件夾體積的命令是：du -h）： 輪回轉世書：你前世是什麽身份？ Sponsored by 輪回轉世書 mysql操作日誌占用伺服器資源過多 百度後得知，這些文件都是數據的操作日誌文件，登陸mysql後清理掉就可以了 mysql登陸命令（註：前面的路徑可能會有所不同）：/usr/local/mysql/bin/mysql -u root -p 清理命令：reset master;（注意後面的分號也要帶好） 清理完後，磁碟剩餘空間頓時增大了不少，如果想要關閉資料庫操作日誌的朋友，可以接著往下看： 如果你只有一個mysql伺服器，在/etc/ 下面找到my.cnf文件vim /etc/my.cnf把裡面的： 1 2 #log-bin=mysql-bin #binlog_format=mixed 提前為退休做準備，住進養生捷運宅，享受愜意人生 Sponsored by 森原樹 這兩行注釋掉（在前面加#即可），然後重啟mysql服務可以了（重啟mysql命令，可以用：service mysqld restart）。 但是如果你設置了主從伺服器，那麼就需要做以下操作了。 A：在每個從屬伺服器上，使用SHOW SLAVE STATUS來檢查它正在讀取哪個日誌。 B：使用SHOW MASTER LOGS獲得主伺服器上的一系列日誌。 C：在所有的從屬伺服器中判定最早的日誌，這個是目標日誌，如果所有的從屬伺服器是更新的，就是清單上的最後一個日誌。 D：清理所有的日誌，但是不包括目標日誌，因為從伺服器還要跟它同步。 簡單地說,這些MySQL目錄下的形如mysql-bin.000***的文件時MySQL的事務日誌。 刪除複製伺服器已經拿走的binlog是安全的，一般來說網絡狀況好的時候，保留最新的那一個足以。 ---------, 2022-05-06, Mysql 和 Mariadb 開啟或關閉遠端命令 1、開啟遠端連線命令: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION; # root 為開啟遠端連線的帳號 # “%” 為開啟遠端連線的地址,此處寫“%”代表多有地址,如果要限制特定的IP地址連線,只有寫上特定的地址或網段即可。例如“192.168.1.1” # “123456”為遠端連線的密碼 #*.* 代表所有許可權 2、關閉遠端連線命令 drop user 'root'@'%'; #刪除特定帳號及對應的地址即可。 執行前: CREATE DEFINER = CURRENT_USER TRIGGER `dbpvc`.`ttracelog_AFTER_INSERT` AFTER INSERT ON `ttracelog` FOR EACH ROW BEGIN IF(SELECT EXISTS(SELECT 1 FROM ttrace WHERE FID = NEW.FID AND FType=0 LIMIT 1)) THEN update ttrace set FNo = NEW.FNo WHERE FID = NEW.FID and FType=0; ELSE insert into ttrace (FID, FType, FNo) VALUES (NEW.FID, 0, NEW.FNo); END IF; IF(NEW.FWarn = 1) THEN IF(SELECT EXISTS(SELECT 1 FROM ttrace WHERE FID = NEW.FID AND FType=1 LIMIT 1)) THEN update ttrace set FNo = NEW.FNo WHERE FID = NEW.FID and FType=1; ELSE insert into ttrace (FID, FType, FNo) VALUES (NEW.FID, 1, NEW.FNo); END IF; END IF; END 執行後, 本機 OK: CREATE DEFINER=`root`@`localhost` TRIGGER `ttracelog_AFTER_INSERT` AFTER INSERT ON `ttracelog` FOR EACH ROW BEGIN IF(SELECT EXISTS(SELECT 1 FROM ttrace WHERE FID = NEW.FID AND FType=0 LIMIT 1)) THEN update ttrace set FNo = NEW.FNo WHERE FID = NEW.FID and FType=0; ELSE insert into ttrace (FID, FType, FNo) VALUES (NEW.FID, 0, NEW.FNo); END IF; IF(NEW.FWarn = 1) THEN IF(SELECT EXISTS(SELECT 1 FROM ttrace WHERE FID = NEW.FID AND FType=1 LIMIT 1)) THEN update ttrace set FNo = NEW.FNo WHERE FID = NEW.FID and FType=1; ELSE insert into ttrace (FID, FType, FNo) VALUES (NEW.FID, 1, NEW.FNo); END IF; END IF; END ---------- 20210913 安裝細節測試 mysql-installer-community-8.0.26.0.msi, SetupType: Developer Default. 可忽略, 沒用: Check Requierements: Python 64-bit (3.6, 3.7, 3.8 or 3.9) for Connector/Python 8.0.26 Use Strong Password Encryption for Authentication (RECOMMENDED) 可用如下的連線字串. 2021-09-13-AuthenticationMethod.jpg ConnectionString=server=localhost;port=3306;database=dbtrackerbpa;user=xxxx;password=oooo;SslMode=Required; ---------- 20210913 download and Installation 下載及安裝 MySQL https://www.mysql.com/ No login account neeeded to download community version from developer's page. mysql-installer-community-8.0.19.0.msi, 20200225 mysql-installer-community-8.0.21.0.msi, 20200813 mysql-installer-community-8.0.26.0.msi, 20210802 只有 (x86, 32-bit) 內含 Server, Shell, Router, Workbench, for excel, for Visual Studio, Connectors, Examples, tutorials, and Document, Notifier Server: 8.0 安裝(相容傳統 connector) 確認可連接 5.7 Connector. 待測試 8.0 安裝(Standard connector) mysql-workbench-community-8.0.19-winx64.msi, 20200225 mysql-workbench-community-8.0.21-winx64.msi, 20200813 只需要 workbench 適用. MySQL Notifier 1.1 source https://github.com/mysql/mysql-notifier MySQL使用工具簡介 http://newsletter.ascc.sinica.edu.tw/news/read_news.php?nid=2050 20200814 測試 SSL Enabled: Server 安裝 mysql-installer-community-8.0.21.0 Client 安裝 mysql-workbench-community-8.0.19-winx64.msi 1. 可用 SSL 測試連線成功: 20200814-SSLEnabled.jpg 2. ConnectionString 改為 ";SslMode=Required;" 測試連線成功. 帳戶: 1. root 帳號不要開放 '%' 遠端連線. 2. 另建 User Account, 授權 ('%' 或指定網路位址) 遠端連線. 20200814-UsersAndPrivileges.jpg ---------- 2020814 Connect 測試, 無法連線問題排除順序 Server: 8.0 安裝(相容傳統 connector) 1. localhost 以 root 連線, 測試 OK 2. 遠端連線, 無法連線: 以 root, Workbench 6.3 遠端連線. 檢查如下, 因預設安裝後 root 只允許 localhost 連線, 改用 uadmin 則測試 OK use MySQL; select host, user from user; # host, user '%', 'uadmin' 'localhost', 'mysql.infoschema' 'localhost', 'mysql.session' 'localhost', 'mysql.sys' 'localhost', 'root' 或 開放 root 授權如下擇1: GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100' IDENTIFIED BY '密碼' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密碼' WITH GRANT OPTION; ALTER USER 'user1'@'%' IDENTIFIED WITH mysql_native_password BY 'password1'; flush privileges; 無法連線問題排除順序 1. Check that MySQL is running on address localhost. 2. Check that MySQL is reachable on port 3306(note: 3306 is the default, but this can be changed) 3. Check the user root has rights to connect to localhost from your address. (MySQL rights define what clients can connect to the server and from which machines) 4. Make sure you are both providing a password if needed and using the correct password for localhost connecting from the host address you're connecting from. 以上第3點為: 參考細部說明 (mysql訪問許可權GRANT ALL PRIVILEGES ON，訪問許可權表) MySQL 上有授權給這位user從這台windows server 2012的IP連進來嗎？例如： GRANT ALL ON *.* TO '<帳號>'@'' IDENTIFIED BY '<密碼>'; 授權後有重新刷新權限表嗎？ flush privileges; 檢查: use MySQL; select host, user from user; ---------- 20200814 Connection String: 20200814 測試 SSL Enabled: Server 安裝 mysql-installer-community-8.0.21.0 Client 安裝 mysql-workbench-community-8.0.19-winx64.msi 1. 可用 SSL 測試連線成功: 20200814-SSLEnabled.jpg 2. ConnectionString 改為 ";SslMode=Required;" 測試連線成功. Force encryption Always use SSL. Deny connection if server does not support SSL. Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;SslMode=Required; This option is available from Connector/NET version 6.2.1 ref https://www.connectionstrings.com/mysql-connector-net-mysqlconnection/ ConnectionString-MySql.txt Connection string with v 8.0 up: ref: What is new in Connector/ODBC 8.0 https://insidemysql.com/what-is-new-in-connector-odbc-8-0/ General information about MySQL Connector/ODBC 8.0: MySQL Connector/ODBC 8.0 is a successor of the Connector/ODBC 5.3 line. The Connector/ODBC driver 8.0 becomes available starting from the version 8.0.11. Where is 8.0.10? The family of MySQL products is growing and with so many products and different versions it is easy to get confused about functionality and product compatibility. Therefore, we decided to unify the versioning and synchronize the version numbers across all MySQL products. The current GA version of MySQL Server is 8.0.11 and the family of MySQL Connectors including Connector/ODBC has been aligned with the new versioning model and became 8.0.11 too. This will ensure that Connector/ODBC 8.0.11 can work with MySQL Server 8.0.11. Among bug fixes and internal improvements the Connector/ODBC 8.0.11 received the support for a new authentication methods introduced in MySQL Server 8.0. These new authentication methods require encrypted connection in case password needs to be sent to the server (cache miss). If, for some reason, SSL connection is not an option, passwords still can be sent to the server after encrypting it with server’s public key. A new connection option GET_SERVER_PUBLIC_KEY requests server to send its public key when it is needed. This way clients can connect even if they do not know server’s public key. However, using this option is prone to man-in-the-middle attacks, so it should be used only in situations where you can ensure by other means that your connections are made to trusted servers. The value of GET_SERVER_PUBLIC_KEY is a boolean, and added in 8.0.11.By default the option is not enabled, but setting it is really easy and can be done in two ways: Through the GUI DSN Setup dialog. Click “Details >>” and tick [x] Get Server Public Key as shown here: 新參數 GET_SERVER_PUBLIC_KEY 可請求取得主機的 public key 後, 以加密方式連線. 可由 DSN 設定畫面中, 勾選選 Details.(Get Server Public Key). ODBC GUI Dialog with GET_SERVER_PUBLIC_KEY option By specifying the option in the connection string: “…GET_SERVER_PUBLIC_KEY=1…” This capability requires a MySQL 8 server, and is supported only for Connector/ODBC built using OpenSSL. This option is ignored when used with MySQL Server 5.7. Server V 8.0 以上的版本才支援此功能, 並且只能以 OpenSSL 連接. 此功能 在 v 5.7 版本中, 自動忽略. NOTE: There is a way to specify RSA Public key if it is located on the client host. In GUI DSN Setup dialog. Click “Details >>”, select the “SSL” tab and specify the location of RSA Public Key as shown here: 客戶端的電腦, 可透過 DSN 設定畫面 Details.SSL.(選擇 RSA Public Key 檔案), 指定 public key. ODBC GUI Dialog with RSAKEY option Alternatively, the RSA Public Key can be given through the connection string as “…RSAKEY=D:\\ssl\\mykey.pub;…”. The double back-slashes are there because in languages like C or C++ they must be properly escaped. 在 Connection string 中也可以以參數 “…RSAKEY=D:\\ssl\\mykey.pub;…” 指定 public key. There are a few things to keep in mind when using MySQL Connector/ODBC 8.0: For maximum security the connection to MySQL Server is established using SSL/TLS by default. All communication between ODBC Driver and MySQL Server would be encrypted using a separate OpenSSL library. For previous versions of MySQL ODBC Driver such as 5.7 the SSL code would be embedded into the driver library file. This is changed in Connector/ODBC 8.0 where OpenSSL shared library is required for the driver to work. The reason for doing it is improving of the security: when the new security update for OpenSSL is released the user can update OpenSSL right away without waiting on the new version of Connector/ODBC driver (which would be necessary if SSL library is embedded). However, in order to work the ODBC driver needs to be able to find and load OpenSSL shared library even if SSL/TLS connection is not used. Most packages released for the version 8.0.11 bundle the latest OpenSSL library files (libeay32.dll/ssleay32.dll for Windows platforms and libcrypto.so/libssl.so for Linux/Unix platforms). Such packages are ready for work out-of-box (assuming that UnixODBC (Linux/Unix) or iODBC (OSX) is installed). Packages for the enterprise platforms such as Red Hat Enterprise Linux or Suse Linux Enterprise Server do not have bundled OpenSSL libraries because in most cases OpenSSL is already installed on these platforms. Also, in enterprise platforms the administrators often update OpenSSL as soon as a new security patch is available. 先前 v 5.7 的版本會包含 SSL driver 模組, 但 v 8.0 開始將改為各自獨立. The Windows platform will no longer receive 32-bit builds of MySQL Connector/ODBC 8.0 (Note: there is no 32-bit MySQL Server 8.0 as well). Windows 系統將不再提供 32 位元的 MySQL Connector/ODBC 8.0. Windows 系統也沒有 32-bit MySQL Server 8.0. The ODBC Driver 8.0 for Windows needs the Visual C++ 2015 runtime libraries for its work. These libraries are included in Visual C++ 2015 64-bit redistributable package, which can be downloaded from the Microsoft web site.NOTE: VC++ 2017 redistributable package is not a replacement for VC++ 2015. Therefore, even if VC++ 2017 is installed in the system, the 2015 version is still required. v 8.0 需要 Visual C++ 2015 runtime libraries. VC++ 2017 並未包含 VC++ 2015. 即使安裝了 VC++ 2017, 也仍需要安裝 VC++ 2015. Typical errors that can happen during the installation or setup: Trying to run an ODBC Driver 8.0 MSI installation package without VC++ 2015 redistributable libraries will result in to following error message dialog: VC++ 2015 redistributable not found The solution is to install the 2015 redistributable package and run MSI installer again. Sometimes the driver is installed without MSI package manually. An attempt to load a Setup module (myodbc8S.dll) from ODBC Administrator without VC++ 2015 redistributable will result in the following error: Failure to load myodbc8a.dll Unfortunately, the Windows ODBC Administrator does not give detailed information about the problem why the setup routine could not be loaded. If you see an error dialog like that it is most likely because the VC++ 2015 redistributable package is not installed. MySQL .NET Connection String Options ref: https://mysqlconnector.net/connection-options/ 參數 SSL Mode, SslMode, 預設=Preferred This option has the following values: Preferred - (this is the default). Use SSL if the server supports it. None - Do not use SSL. Required - Always use SSL. Deny connection if server does not support SSL. Does not validate CA or hostname. VerifyCA - Always use SSL. Validates the CA but tolerates hostname mismatch. VerifyFull - Always use SSL. Validates CA and hostname. 參數 AllowPublicKeyRetrieval, Allow Public Key Retrieval, 預設=false If the user account uses sha256_password authentication, the password must be protected during transmission; TLS is the preferred mechanism for this, but if it is not available then RSA public key encryption will be used. To specify the server’s RSA public key, use the ServerRSAPublicKeyFile connection string setting, or set AllowPublicKeyRetrieval=True to allow the client to automatically request the public key from the server. Note that AllowPublicKeyRetrieval=True could allow a malicious proxy to perform a MITM attack to get the plaintext password, so it is False by default and must be explicitly enabled. 若 使用者帳戶使用 sha256_password 授權方式, 則於傳輸過程中必須保護密碼. 保護方式使用 TLS 方法加密. 若無法使用 TLS 保護模式, 則採用 RSA public key 加密方法. 經由參數 ServerRSAPublicKeyFile 可指定 server 的 public key file. 或設定 AllowPublicKeyRetrieval=True, 允許客戶端自動取得 server 的 public key. 注意設定 AllowPublicKeyRetrieval=True時, 應避免被惡意攻擊取得密碼, 應限定允許範圍. 20200226, 測試連接 v 8.0: # 以下兩者連接 v 8.0 相容傳統 5.7 的 MySQL server 均 OK. ConnectionString=server=192.168.0.111;port=3306;database=dbtracker;user=USERID;password=PASSWORD;SslMode=none; ConnectionString=server=192.168.0.111;port=3306;database=dbtracker;user=USERID;password=PASSWORD;SslMode=Required; Retrieval of the RSA public key is not enabled for insecure connections. https://stackoverflow.com/questions/51509639/mysql-error-0-retrieval-of-the-rsa-public-key-is-not-enabled-for-insecure-conne https://parkerro.tw/mysql-8-0-client-problem/ https://mysqlconnector.net/troubleshooting/retrieval-public-key/ Retrieval of the RSA public key is not enabled for insecure connections Problem When connecting to MySQL Server from a C# program, you may receive one of the following errors: MySqlException (0x80004005): Retrieval of the RSA public key is not enabled for insecure connections. (Connector/NET) Authentication method ‘caching_sha2_password’ failed. Either use a secure connection, specify the server’s RSA public key with ServerRSAPublicKeyFile, or set AllowPublicKeyRetrieval=True. (MySqlConnector) Authentication method ‘sha256_password’ failed. Either use a secure connection, specify the server’s RSA public key with ServerRSAPublicKeyFile, or set AllowPublicKeyRetrieval=True. (MySqlConnector) Fix Use one of the following fixes. (Note: if using MySql.Data (Connector/NET), uninstall it first then install MySqlConnector.) 1. (Preferred) Use a secure connection by adding ;SslMode=Required to the connection string. 20200226, (SslMode=Required 跟 SslMode=none)測試結果都OK. 先確認一下 userid 大小寫是否正確!!! 2. Specify the (local) path that contains a copy of the server’s public key by adding ;ServerRSAPublicKeyFile=path/to/file.pem to the connection string. To retrieve the server’s public key, connect securely to the server and execute the following query, saving the results in a file: SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'; 3. (Not recommended) Automatically retrieve the server’s public key by adding ;AllowPublicKeyRetrieval=true to the connection string; this is potentially insecure. Background MySQL Server 5.7 added the sha256_password authentication plugin. MySQL Server 8.0 adds the caching_sha2_password authentication plugin and makes it the default. These plugins use RSA public key encryption to protect the user’s password in transit. As the MySQL Server Team writes: Distributing keys securely can be an operational headache. MySQL Server will supply its own RSA public key upon request from the client, so that the key doesn’t have to be explicitly distributed and configured for each client. But this introduces another security concern – a proxy in the middle may substitute an RSA public key for which it has the private key, decrypt and harvest the plain-text password, then re-encrypting the password with the actual server RSA public key for the connection attempt to continue. For this reason, it’s strongly recommended that clients define a local RSA public key to use instead of request the server RSA key during the handshake. By default, client libraries will not send the password unless a secure connection (using TLS or RSA public key encryption) can be established. To avoid a MITM attack, the RSA public key will not be sent in plain text. For Connector/NET, you can use TLS (SslMode=Required) to protect the RSA public key. With MySqlConnector, you also have the option of specifying the server’s public key directly, using the ServerRSAPublicKeyFile option, or allowing a potentially-insecure connection by using AllowPublicKeyRetrieval=true. Further Reading Protecting MySQL Passwords With the sha256_password Plugin Unsure about this particular scenario, but we encountered the same error message with the .NET Connector. It came down to the MySQL user we were using - we changed the default authentication plugin (default_authentication_plugin=mysql_native_password in my.ini) and then, importantly, recreated the user account. Alternatively the user account can be specifically created with the mysql_native_password plugin via IDENTIFIED WITH if you want to avoid changing the default 在 my.ini 中修改 authentication plugin 後, 然後重建使用者帳戶. 或是 以 IDENTIFIED WITH 語法, 改變使用者帳戶, 可避開變更預設的 authentication plugin 近日有接觸到新的 MySQL 8.0 部屬 部屬後發現一個問題就是，用navicat連不上去，錯誤 1251 似乎是密碼加密方法不同，由原本mysql_native_password 改成8.0的 caching_sha2_password 解決方法就是去mysql裡面修改了 先登入mysql裡面 mysql --host=localhost --user=myname --password=password 接著輸入 ALTER USER 'USERID'@'%' IDENTIFIED BY 'your-password' PASSWORD EXPIRE NEVER; ALTER USER 'USERID'@'%' IDENTIFIED WITH mysql_native_password BY 'PASSWORD'; flush privileges; USERID -> 可以修改你的user 名稱, 注意區分大小寫 % -> 可以改 localhost PASSWORD -> 置換成你的密碼 flush privileges; 別忘了執行 MySql.Data.MySqlClient.MySqlException was unhandled Code=0 ErrorCode=-2147467259 HResult=-2147467259 Message=Retrieval of the RSA public key is not enabled for insecure connections. Number=0 Source=MySql.Data StackTrace: 於 MySql.Data.MySqlClient.Authentication.CachingSha2AuthenticationPlugin.GeneratePassword() 於 MySql.Data.MySqlClient.Authentication.CachingSha2AuthenticationPlugin.MoreData(Byte[] data) 於 MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.ContinueAuthentication(Byte[] data) 於 MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.HandleAuthChange(MySqlPacket packet) 於 MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.Authenticate(Boolean reset) 於 MySql.Data.MySqlClient.NativeDriver.Authenticate(String authMethod, Boolean reset) 於 MySql.Data.MySqlClient.NativeDriver.Open() 於 MySql.Data.MySqlClient.Driver.Open() 於 MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) 於 MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() 於 MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() 於 MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() 於 MySql.Data.MySqlClient.MySqlPool.GetConnection() 於 MySql.Data.MySqlClient.MySqlConnection.Open() 於 ZLib.DAbstract.ZADB.OpenConnection() 於 D:\QuanDic\QuanDic2020\src\QGPS3P\ZLib\DAbstract\ZADB.cs: 行 74 於 ZLib.DAbstract.ZADB.QueryDataSet(String sCmd, Dictionary`2 parameters) 於 D:\QuanDic\QuanDic2020\src\QGPS3P\ZLib\DAbstract\ZADB.cs: 行 158 於 ZLib.DAbstract.ZADB.QueryDataTable(String sCmd, Dictionary`2 parameters) 於 D:\QuanDic\QuanDic2020\src\QGPS3P\ZLib\DAbstract\ZADB.cs: 行 175 於 Set011.CDictBuilding.DBLoad() 於 D:\QuanDic\QuanDic2020\src\QGPS3P\Set011\CDictBuilding.cs: 行 37 於 Set011.CProject.Load() 於 D:\QuanDic\QuanDic2020\src\QGPS3P\Set011\CProject.cs: 行 698 於 Set011.CProject.Create(String[] args) 於 D:\QuanDic\QuanDic2020\src\QGPS3P\Set011\CProject.cs: 行 183 於 Set011.Program.Main(String[] args) 於 D:\QuanDic\QuanDic2020\src\QGPS3P\Set011\Program.cs: 行 27 於 System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) 於 System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) 於 Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() 於 System.Threading.ThreadHelper.ThreadStart_Context(Object state) 於 System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) 於 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) 於 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) 於 System.Threading.ThreadHelper.ThreadStart() ---------- 20200812 安全連線 MySQL Server 5.7 added the sha256_password authentication plugin. 參考如下: https://mysqlconnector.net/troubleshooting/retrieval-public-key/ Problem When connecting to MySQL Server from a C# program, you may receive one of the following errors: MySqlException (0x80004005): Retrieval of the RSA public key is not enabled for insecure connections. (Connector/NET) Authentication method ‘caching_sha2_password’ failed. Either use a secure connection, specify the server’s RSA public key with ServerRSAPublicKeyFile, or set AllowPublicKeyRetrieval=True. (MySqlConnector) Authentication method ‘sha256_password’ failed. Either use a secure connection, specify the server’s RSA public key with ServerRSAPublicKeyFile, or set AllowPublicKeyRetrieval=True. (MySqlConnector) Fix 3種解法, 依照優先順序: Use one of the following fixes. (Note: if using MySql.Data (Connector/NET), uninstall it first then install MySqlConnector.) 1. (Preferred) Use a secure connection by adding ;SslMode=Required to the connection string. 在連線字串中加入 ;SslMode=Required 2. Specify the (local) path that contains a copy of the server’s public key by adding ;ServerRSAPublicKeyFile=path/to/file.pem to the connection string. To retrieve the server’s public key, connect securely to the server and execute the following query, saving the results in a file: SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'; 在連線字串中以參數如(;ServerRSAPublicKeyFile=path/to/file.pem), 指定 (Server 的 RSC public key 檔案). (Server 的 RSC public key 檔案) 可以透過指令 SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'; 取得如下: (例如 192_168_0_111.pem.txt) # Variable_name, Value 'Caching_sha2_password_rsa_public_key', '-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA1YhrcKbjkeRiTKus6qHl\n1znk0oS57Hd2R+zdVQJ9GQEBaK8FO9RrNfjTrZYlrRyBArG4j+mbh8XSZ5VPFI/u\nfW0Ah7JEgVHUvIQnJzn8tPipmXPbx+4sXxz6/K8wIBzCO+dtNAJbKdNvUWmUhoSg\nWf7lM8ioVR8YJpPcsqbM8u/5PESlFsggnlNf7foL9Sw40tg0hs3aqib6FLVJ7mYN\nvdMV4YfTqfq1xulw3XDEopN5NWtmEvUz0MmXh2KmPjlGL/J+3/Y9RCKVu+LCXgHH\nHlKaIpyy5gyr+tBJkJ+dT8PZkjWa6WjP/qBjmhW7A+0v8cmp6XsK25jr07265dA3\nowIDAQAB\n-----END PUBLIC KEY-----\n' 再將 Value 中的 \n 取代成 New Line 如下: (Notepad++ 的取代為: 將 "\\n" 取代成 "\r\n", 並選擇 extension 模式) -----BEGIN PUBLIC KEY----- MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA1YhrcKbjkeRiTKus6qHl 1znk0oS57Hd2R+zdVQJ9GQEBaK8FO9RrNfjTrZYlrRyBArG4j+mbh8XSZ5VPFI/u fW0Ah7JEgVHUvIQnJzn8tPipmXPbx+4sXxz6/K8wIBzCO+dtNAJbKdNvUWmUhoSg Wf7lM8ioVR8YJpPcsqbM8u/5PESlFsggnlNf7foL9Sw40tg0hs3aqib6FLVJ7mYN vdMV4YfTqfq1xulw3XDEopN5NWtmEvUz0MmXh2KmPjlGL/J+3/Y9RCKVu+LCXgHH HlKaIpyy5gyr+tBJkJ+dT8PZkjWa6WjP/qBjmhW7A+0v8cmp6XsK25jr07265dA3 owIDAQAB -----END PUBLIC KEY----- 測試如下: 2020-08-12, ConnectionString=server=192.168.0.111;port=3306;database=dbtrackerc;user=???;password=???;ServerRSAPublicKeyFile=/temp/192_168_0_111.pem ConnectionString=server=192.168.0.111;port=3306;database=dbtrackerc;user=???;password=???;SslMode=Required;ServerRSAPublicKeyFile=/temp/192_168_0_111.pem 可能是 Server 安裝方式為傳統 5.7 的模式, 所以 結果都是 ServerRSAPublicKeyFile Option not supported. 後續再測試確認. 3. (Not recommended) Automatically retrieve the server’s public key by adding ;AllowPublicKeyRetrieval=true to the connection string; this is potentially insecure. Background MySQL Server 5.7 added the sha256_password authentication plugin. MySQL Server 8.0 adds the caching_sha2_password authentication plugin and makes it the default. These plugins use RSA public key encryption to protect the user’s password in transit. As the MySQL Server Team writes: Distributing keys securely can be an operational headache. MySQL Server will supply its own RSA public key upon request from the client, so that the key doesn’t have to be explicitly distributed and configured for each client. But this introduces another security concern – a proxy in the middle may substitute an RSA public key for which it has the private key, decrypt and harvest the plain-text password, then re-encrypting the password with the actual server RSA public key for the connection attempt to continue. For this reason, it’s strongly recommended that clients define a local RSA public key to use instead of request the server RSA key during the handshake. By default, client libraries will not send the password unless a secure connection (using TLS or RSA public key encryption) can be established. To avoid a MITM attack, the RSA public key will not be sent in plain text. For Connector/NET, you can use TLS (SslMode=Required) to protect the RSA public key. With MySqlConnector, you also have the option of specifying the server’s public key directly, using the ServerRSAPublicKeyFile option, or allowing a potentially-insecure connection by using AllowPublicKeyRetrieval=true. ---------- 2018-04-26 建立ERD: 在MySQL Workbench.左欄選單選擇 Model.右欄選單選擇 Create ERD model from database(向右鍵>).依照指示選擇資料庫後建立 存檔案例: C:\Users\uGPTT1\Documents\dumps\Model-Soez-180426.mwb ----------, 2022-05-11, ####Data Type □ 11.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type. Table 11.1 Required Storage and Range for Integer Types Supported by MySQL Type Storage Minimum Minimum Maximum Value Signed Maximum Value Unsigned (Bytes) Value Signed Value Unsigned --------- ------- ------------ -------------- -------------------- ---------------------- TINYINT 1 -128 0 127 255 SMALLINT 2 -32768 0 32767 65535 MEDIUMINT 3 -8388608 0 8388607 16777215 INT 4 -2147483648 0 2147483647 4294967295 BIGINT 8 -2^63 0 2^63-1 2^64-1 ----------, 2022-05-11, ####VARIABLE mysql> SET @sum = 0; mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); mysql> SELECT @sum AS 'Total amount inserted'; +-----------------------+ | Total amount inserted | +-----------------------+ | 1852.48 | +-----------------------+ ----------, 2022-05-11, ####DateTime □ DATE_FORMAT(date, format), 格式化日期時間 DATE_FORMAT() 會返回一個字串，你可以指定不同的 format 來顯示不同輸出格式的 date。 format格式： 格式 顯示 %a 縮寫的星期幾 Sun..Sat %b 縮寫的月份 Jan..Dec %c 月份 0..12 %D 一個月的第幾天，英文前綴表示 0th, 1st, 2nd, 3rd, … %d 一個月的第幾天，數字 00..31 %e 一個月的第幾天，數字 0..31 %f 毫秒 000000..999999 %H 小時 00..23 %h 小時 01..12 %I 小時 01..12 %i 分鐘 00..59 %j 一年中的第幾天 001..366 %k 小時 0..23 %l 小時 1..12 %M 月份名稱 January..December %m 月份 00..12 %p AM 或 PM %r 十二小時制的時間 hh:mm:ss 後面接 AM 或 PM %S 秒數 00..59 %s 秒數 00..59 %T 二十四小時制的時間 hh:mm:ss %U 一年中的第幾週，用星期日當作是一週的第一天 00..53 %u 一年中的第幾週，用星期一當作是一週的第一天 00..53 %V 一年中的第幾週，用星期日當作是一週的第一天 01..53，與 %X 一起使用 %v 一年中的第幾週，用星期一當作是一週的第一天 01..53，與 %x 一起使用 %W 星期幾名稱 Sunday..Saturday %w 一週的第幾天，0=星期日..6=星期六 %X 年，四位數整數，用星期日當作是一週的第一天，與 %V 一起使用 %x 年，四位數整數，用星期一當作是一週的第一天，與 %v 一起使用 %Y 年，四位數整數 %y 年，兩位數整數 %% 跳脫符號，表示百分比 % mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); 'Sunday October 2009' mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); '22:23:00' mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j'); '4th 00 Thu 04 10 Oct 277' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w'); '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); '1998 52' mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); '00' DATE_FORMAT(): select date_format(now(), '%Y-%m-%d %H:%i:%s'), current_time(); # date_format(now(), '%Y-%m-%d %H:%i:%s'), current_time() '2020-06-10 09:59:46', '09:59:46' '2020-06-10 02:47:01', '02:47:01' 每小時統計: SELECT FID, date_format(FTimeReceive, '%Y-%m-%d %H') FHour, count(*) Count, min(FTimeReceive) Begin, max(FTimeReceive) End, max(FPressure) Max, min(FPressure) Min, max(FPressure) - min(FPressure) Offset FROM dbtrackerb.ttracelog WHERE FTimeReceive > '2020-06-09 08' and FID in ('01-100032', '01-100033') group by FID, FHour order by fid, fhour 每10分鐘統計: use dbtrackerb; SELECT FID, left(date_format(FTimeReceive, '%Y-%m-%d %H:%i'), 15) FTime, count(*) Count, min(FTimeReceive) Begin, max(FTimeReceive) End, max(FPressure) Max, min(FPressure) Min, max(FPressure) - min(FPressure) Offset FROM dbtrackerb.ttracelog WHERE FTimeReceive > '2020-06-09 08' and FID in ('01-100032', '01-100033') group by FID, FTime order by fid, FTime 1. now() 取得目前(指令執行前)的日期時間(date + time) 2. sysdate() 取得目前(指令執行到)的日期時間(date + time) now() 跟 sysdate() 差異 如下, 兩個 now() 函數取得相同的時間, 但是 sysdate() 則會在執行到時, 重新取得最新的時間: select now(), sysdate(), sleep(3), now(), sysdate(); '2020-01-15 14:34:39', '2020-01-15 14:34:39', '0', '2020-01-15 14:34:39', '2020-01-15 14:34:42' MySQL NOW() and SYSDATE() functions returns the current timestamp values. But the output of both of them depends upon the execution time. This creates the big difference between them. NOW() function returns a steady time that indicates the time at which the particular statement began to execute. In contrast, SYSDATE() function returns the accurate time at which the statement executes. Following example will show the difference between these functions: 3. curdate(), current_date(), current_date 取得目前日期. select curdate(), current_date(), current_date; '2020-01-15', '2020-01-15', '2020-01-15' 4. curtime(), current_time(), current_time; 取得目前時間 select curtime(), current_time(), current_time; '14:47:47', '14:47:47', '14:47:47' 5. utc_date(), utc_time(), utc_timestamp(); select utc_date(), utc_time(), utc_timestamp(); 6 獲得當前 UTC 日期時間函數：utc_date(), utc_time(), utc_timestamp() '2020-01-15', '06:49:37', '2020-01-15 06:49:37' 台灣時間 = UTC 時間 + 8 小時 mysql> SELECT * FROM table WHERE date BETWEEN '2018-05-10' AND '2018-08-20'; mysql> SELECT * FROM table WHERE date >= '2018-05-10' AND date <= '2018-08-20'; select * from user where birthday>'2009-2-3' and birthday<'2009-4-3' 查到所有包括2009-2-3並且大於2009-2-3的所有的用戶信息。 select * from user where birthday>'2009-2-3' 查到等於2009-2-3並且和大於2009-2-3號的所有信息。 select * from user where birthday<'2009-2-3' 查詢到包括2009-2-3並且小於2009-2-3的所有用戶信息。 mysql不用寫=號，=號也包含在我們寫的sql語句中了。 查詢一天： select * from table where to_days(column_time) = to_days(now()); select * from table where date(column_time) = curdate(); 查詢一周： select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time); 查詢一個月： select * from table where DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column_time); UNIX_TIMESTAMP(date) 如果沒有參數調用，返回一個Unix時間戳記(從’1970-01-01 00:00:00′GMT開始的秒數)。 如果UNIX_TIMESTAMP()用一個date參數被調用，它返回從’1970-01-01 00:00:00′ GMT開始的秒數值。 date可以是一個DATE字符串、一個DATETIME字符串、一個TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地時間的一個數字。 mysql> select UNIX_TIMESTAMP(); -> 882226357 mysql> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′); -> 875996580 當UNIX_TIMESTAMP被用於一個TIMESTAMP列，函數將直接接受值，沒有隱含的“string-to-unix-timestamp”變換。 FROM_UNIXTIME(unix_timestamp) 以’YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回unix_timestamp參數所表示的值，取決於函數是在一個字符串還是或數字上下文中被使用。 mysql> select FROM_UNIXTIME(875996580); -> ‘1997-10-04 22:23:00′ mysql> select FROM_UNIXTIME(875996580) + 0; -> 19971004222300 FROM_UNIXTIME(unix_timestamp,format) 返回表示 Unix 時間標記的一個字符串，根據format字符串格式化。format可以包含與DATE_FORMAT()函數列出的條目同樣的修飾符。 mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(), ‘%Y %D %M %h:%i:%s %x’); -> ‘1997 23rd December 03:43:30 x’ 通過 UNIX_TIMESTAMP 函數把 MySQL 數據庫中的 date 類型數據轉換成 unix timestamp 形式的一個整形數字：select UNIX_TIMESTAMP(’2006-02-28′) testdate; □ TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2), 時間相差 MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR □ DATEDIFF(expr1, expr2), 日期相差 計算兩個日期間相差幾個月： mysql> SELECT TIMESTAMPDIFF(MONTH, '2003-02-01', '2003-05-01'); 3 計算兩個日期間相差幾年： mysql> SELECT TIMESTAMPDIFF(YEAR, '2002-05-01', '2001-01-01'); 1 計算兩個日期間相差幾分鐘，若沒時間的部分預設值為 00:00:00： mysql> SELECT TIMESTAMPDIFF(MINUTE, '2003-02-01', '2003-05-01 12:05:55'); 128885 □ DATE_ADD(datetime, INTERVAL expr unit), 加上時間間隔 unit 可以是下列的值： MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH □ 取得一天後的日期時間： mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY); '2018-05-02' mysql> SELECT DATE_ADD('2018-12-31 23:59:59', INTERVAL 1 DAY); '2019-01-01 23:59:59' □ 取得一年後的日期時間： mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 YEAR); '2019-05-01' □ 也可以用負號 - 表示是減掉時間： mysql> SELECT DATE_ADD('2018-05-01',INTERVAL -1 YEAR); '2017-05-01' □ 取得一個月後的日期時間： mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 MONTH); '2018-06-01' □ 取得一秒鐘後的日期時間： mysql> SELECT DATE_ADD('2020-12-31 23:59:59', INTERVAL 1 SECOND); '2021-01-01 00:00:00' □ 取得一個禮拜後的日期時間： mysql> SELECT DATE_ADD('2018-11-28',INTERVAL 1 WEEK); '2018-12-05' □ 30秒內的資料. CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `dbpvc`.`vttrace` AS SELECT `t2`.`FNo` AS `FNo`, `t2`.`FID` AS `FID`, `t2`.`FKeyGroup` AS `FKeyGroup`, `t2`.`FKeyID` AS `FKeyID`, `t2`.`FTimeReceive` AS `FTimeReceive`, `t2`.`FFirstInsert` AS `FFirstInsert`, `t2`.`FSourceType` AS `FSourceType`, `t2`.`FSourceTime` AS `FSourceTime`, `t2`.`FReaderID` AS `FReaderID`, `t2`.`FRepeaterID` AS `FRepeaterID`, `t2`.`FTagStatus` AS `FTagStatus`, `t2`.`FBeaconID` AS `FBeaconID`, `t2`.`FRssiRola` AS `FRssiRola`, `t2`.`FRssiBeacon` AS `FRssiBeacon`, `t2`.`FGPSData` AS `FGPSData`, `t2`.`FGPSRead` AS `FGPSRead`, `t2`.`FLatitudeReal` AS `FLatitudeReal`, `t2`.`FLongitudeReal` AS `FLongitudeReal`, `t2`.`FLatitudeAdj` AS `FLatitudeAdj`, `t2`.`FLongitudeAdj` AS `FLongitudeAdj`, `t2`.`FGPSRateH` AS `FGPSRateH`, `t2`.`FGPSRateV` AS `FGPSRateV`, `t2`.`F5001FTime` AS `F5001FTime`, `t2`.`F5002FTime` AS `F5002FTime`, `t2`.`F500Only` AS `F500Only`, `t2`.`FPressure` AS `FPressure`, `t2`.`FPressureBase` AS `FPressureBase`, `t2`.`FPressureBaseFloor` AS `FPressureBaseFloor`, `t2`.`FPressureBaseType` AS `FPressureBaseType`, `t2`.`FPressureBaseTime` AS `FPressureBaseTime`, `t2`.`FTemperature1` AS `FTemperature1`, `t2`.`FTemperature2` AS `FTemperature2`, `t2`.`FTemperature3` AS `FTemperature3`, `t2`.`FTemperature4` AS `FTemperature4`, `t2`.`FEnable1` AS `FEnable1`, `t2`.`FEnable2` AS `FEnable2`, `t2`.`FHumidity` AS `FHumidity`, `t2`.`FStatus` AS `FStatus`, `t2`.`FOwner` AS `FOwner`, `t2`.`FIP` AS `FIP`, `t2`.`FPort` AS `FPort`, `t2`.`FDeviceType` AS `FDeviceType`, `t2`.`FDeviceID` AS `FDeviceID`, `t2`.`FBuilding` AS `FBuilding`, `t2`.`FFloor` AS `FFloor`, `t2`.`FFloorPressure` AS `FFloorPressure`, `t2`.`FAreaID` AS `FAreaID`, `t2`.`FWarn` AS `FWarn`, `t2`.`FWarn1` AS `FWarn1`, `t2`.`FWarn2` AS `FWarn2`, `t2`.`FWarn3` AS `FWarn3`, `t2`.`FWarn4` AS `FWarn4`, `t2`.`FWarnLevel2` AS `FWarnLevel2`, `t2`.`FWarnPress` AS `FWarnPress`, `t2`.`FWarnArea` AS `FWarnArea`, `t2`.`FLowBattery` AS `FLowBattery`, `t2`.`FMessage` AS `FMessage`, `t2`.`FError` AS `FError`, `t2`.`FFlag1` AS `FFlag1`, `t2`.`FFlag2` AS `FFlag2`, `t2`.`FVersionTag` AS `FVersionTag`, `t2`.`FCreateTime` AS `FCreateTime`, `t2`.`FUpdateTime` AS `FUpdateTime`, ((`t2`.`FTimeReceive` + INTERVAL 300 SECOND) > NOW()) AS `FOnLine` // 30秒內的資料 FROM (`dbpvc`.`ttrace` `t1` JOIN `dbpvc`.`ttracelog` `t2`) WHERE ((`t1`.`FNo` = `t2`.`FNo`) AND (`t1`.`FType` = 0)) 依據查詢: SELECT t2.*, DATE_ADD(t2.FTimeReceive, INTERVAL 300 SECOND) > now() as FOnLine from dbpvc.ttrace t1, dbpvc.ttracelog t2 where t1.FNo = t2.FNo and FType=0 查詢 view vttrace: SELECT FID, FTimeReceive, FOnLine, FBeaconID, FLatitudeReal, FLongitudeReal FROM vttrace ORDER BY FTimeReceive DESC 最新資料語法: SELECT t2.FTimeReceive, t2.FID, t2.FPressureBaseTime, t2.FBeaconID, t2.FLatitudeReal, t2.FLongitudeReal, NOW()>DATE_ADD(t2.FTimeReceive, INTERVAL 300 SECOND) from dbpvc.ttrace t1, dbpvc.ttracelog t2 where t1.FNo = t2.FNo and FType=0 order by FTimeReceive desc ---------- 20200610 time_zone 時區 查詢( global 或 session) level 時區: mysql> SELECT @@global.time_zone, @@session.time_zone; # @@global.time_zone, @@session.time_zone 'SYSTEM', 'SYSTEM' SELECT @@global.time_zone, TIMEDIFF(NOW(), UTC_TIMESTAMP), CURRENT_TIMESTAMP, utc_timestamp() # @@global.time_zone, TIMEDIFF(NOW(), UTC_TIMESTAMP), CURRENT_TIMESTAMP, utc_timestamp() 'SYSTEM', '08:00:00', '2020-06-11 14:21:16', '2020-06-11 06:21:16' 詳細參考 Time_Zone.txt ---------- 20200601 download MySQL https://www.mysql.com/ No login account neeeded to download community version from developer's page. download community version from Developer zone: https://dev.mysql.com/ mysql-installer-community-8.0.19.0.msi, mysql-installer-community-8.0.20.0.msi 內含 Server, Shell, Router, Workbench, for excel, for Visual Studio, Connectors, Examples, tutorials, and Document, Notifier Server: 8.0 安裝(相容傳統 connector) 確認可連接 5.7 Connector. 待測試 8.0 安裝(Standard connector) mysql-workbench-community-8.0.19-winx64.msi mysql-workbench-community-8.0.20-winx64.msi 只需要 workbench 適用. MySQL Notifier 1.1 source https://github.com/mysql/mysql-notifier MySQL使用工具簡介 http://newsletter.ascc.sinica.edu.tw/news/read_news.php?nid=2050 ---------- 20200527 SELECT EXISTS ref: https://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table I have made some researches on this subject recently. The way to implement it has to be different if the field is a TEXT field, a non unique field. I have made some tests with a TEXT field. Considering the fact that we have a table with 1M entries. 37 entries are equal to 'something': SELECT * FROM test WHERE texte LIKE '%something%' LIMIT 1 with mysql_num_rows() : 0.039061069488525s. (FASTER) SELECT count(*) as count FROM test WHERE text LIKE '%something% : 16.028197050095s. SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%') : 0.87045907974243s. SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%' LIMIT 1) : 0.044898986816406s. But now, with a BIGINT PK field, only one entry is equal to '321321' : SELECT * FROM test2 WHERE id ='321321' LIMIT 1 with mysql_num_rows() : 0.0089840888977051s. SELECT count(*) as count FROM test2 WHERE id ='321321' : 0.00033879280090332s. SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321') : 0.00023889541625977s. SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321' LIMIT 1) : 0.00020313262939453s. (FASTER) 實作於 TRIGGER AFTER INSERT: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `dbtrackerb`.`ttracelog_AFTER_INSERT` AFTER INSERT ON `ttracelog` FOR EACH ROW BEGIN IF(SELECT EXISTS(SELECT 1 FROM ttrace WHERE FID = NEW.FID AND FType=1 LIMIT 1)) THEN update ttrace set FNo = NEW.FNo WHERE FID = NEW.FID and FType=1; ELSE insert into ttrace (FID, FType, FNo) VALUES (NEW.FID, 1, NEW.FNo); END IF; IF(NEW.FWarn = 1) THEN IF(SELECT EXISTS(SELECT 1 FROM ttrace WHERE FID = NEW.FID AND FType=2 LIMIT 1)) THEN update ttrace set FNo = NEW.FNo WHERE FID = NEW.FID and FType=2; ELSE insert into ttrace (FID, FType, FNo) VALUES (NEW.FID, 2, NEW.FNo); END IF; END IF; IF(NEW.FIsPressureBase = 1) THEN IF(SELECT EXISTS(SELECT 1 FROM ttrace WHERE FID = NEW.FID AND FType=3 LIMIT 1)) THEN update ttrace set FNo = NEW.FNo WHERE FID = NEW.FID and FType=3; ELSE insert into ttrace (FID, FType, FNo) VALUES (NEW.FID, 3, NEW.FNo); END IF; END IF; END */;; ---------- 20200303 License GPL ref: https://stackoverflow.com/questions/49888007/understanding-mysql-licensing Some people may think this question is not appropriate for Stack Overflow, because it has to do with software licensing. It's not a code question. But I think it's part of the job of coders to understand software licenses. Perhaps this question should be moved to https://softwareengineering.stackexchange.com Here's what I understand: 1. If you distribute MySQL with a non-open-source project, you would need to pay for the commercial license. 2. If you want Oracle support for MySQL, you would need to pay for the commercial license. 3. If you want to use some MySQL tools that are licensed only to Oracle support customers, such as the MySQL Enterprise Monitor, Enterprise backup, various plugins, etc. then you would need to get an Oracle support contract, and that requires you to pay for commercial licenses. 4. If you want to modify MySQL source code and distribute your modifications as a non-open-source offering, you would need to pay for the commercial license. Note that you can still charge money for open-source modifications. This has nothing to do with being gratis. It has to do with whether you offer your modifications under a GPL-compatible license, which would allow your customers to further modify and redistribute. In most other cases, you can use the Community Edition. For example: 1. You can install MySQL Community Edition at your site and use it, or even modify it, whether your product is open-source or not. Also whether you charge for your product or not. 2. You can make and distribute a non-open-source product that uses MySQL, as long as you don't distribute MySQL Community Edition with it. You would require your customers install MySQL themselves. They could then use your product to connect to the instance of MySQL Community Edition that they installed. 3. You can distribute your product and include MySQL Community Edition with it, if you offer your product under a GPL-compatible license. I do know at least one business that switched from MySQL to PostgreSQL specifically because they wanted to distribute the RDBMS software with their non-open-source product. PostgreSQL uses a non-viral open-source license similar to BSD or MIT licenses. The usual disclaimers apply: 1. I'm not a lawyer and this is not legal advice; 2. I do not claim that the above is accurate; 3. don't make any business decisions based on stuff you read from strangers on the internet, including me. See also: https://www.xaprb.com/blog/2009/02/17/when-are-you-required-to-have-a-commercial-mysql-license/ https://www.gnu.org/licenses/gpl-faq.en.html MySQL, 開源軟體版權, GPL協議, MariaDB ref: https://kknews.cc/zh-tw/code/5rjrbvk.html MySQL為什麼可以同時使用GPL開源協議和商用授權協議？ 我們都知道MySQL是開源資料庫，用的是GPLv2開源許可協議，大家都可以隨便拿來用。當然，很多人也都知道MySQL其實是採用了雙授權模式，社區開源版本用的是GPL，但其實Oracle還給了MySQL一份商用授權協議。這篇文章我們就來討論一下MySQL採用雙授權的合法性和在什麼情況下使用MySQL需要Oracle的商業授權。 為什麼說Oracle擁有MySQL：對開源軟體版權的認識 首先在這裡說明一下開源軟體的版權問題，我們知道只要是軟體肯定都有版權，對於開源軟體也不例外。很多人認為既然作者把軟體開源了，那就是放棄版權了，所以其他人就可以隨便拿來用，這種理解是錯誤的。我們前面的文章說過軟體的許可協議就是軟體作者規定使用者可以怎麼來用這個軟體的，我們之所以可以按GPL的規定來使用開源軟體，其實是軟體作者賦予我們的權利。為什麼作者可以賦予我們這個權利，那是因為開源軟體作者有這個軟體的版權。 這樣也就可以解開一些人的疑問了，為什麼經常聽說MySQL等開源軟體經常被這個收購那個收購的，不是開源嗎，收的啥？其實收的除了那些開發人員外，主要還是版權。具體到MySQL，從MySQL AB，到Sun，到最後被Oracle收購，那Oracle自然就有MySQL的版權，也就是MySQL的主人。 雙許可證模式 MySQL採用的是雙許可證模式，即開源版本用的是GPL開源協議，我們知道GPL協議是很嚴格的，如果你改了GPL軟體用於分發，那必須是開源的，如果做不到，那你就別用。而MySQL採用的是，如果你能遵守GPL協議，那你可以用，但如果你不想遵守GPL協議，而又想用MySQL的代碼，那你可以向MySQL購買商業授權。比如你在MySQL的基礎上進行了改進，覺得很好，可以拿來賣錢，不想再開源了，在GPL下是不行的，但你可以向MySQL購買商業許可，這樣你就可以閉源你的代碼了。 可能這裡就有疑問了，明明已經在GPL協議下發布了，為什麼Oracle公司還可以再賣商業授權？這其實還是因為Oracle擁有版權的原因，不管是GPL還是商業授權，都是版權所有者對其他人設置的使用規則，而自己要不要遵守是不受約束的。版權所有者每放出一個版本，都有權決定該版本採用什麼樣的協議，並不是之前的版本採用了GPL，之後放出的版本都必須採用GPL，這個權利是版權法賦予的。這也就是MySQL被收購之後，人們都擔心會被Oracle閉源，因為Oracle確實有這個權利。 什麼情況下應該購買MySQL的商業授權？ 看下Oracle官網上關於商業授權的描述 Q3: As a commercial OEM, ISV or VAR, when should I purchase a commercial license for MySQL software? A: OEMs, ISVs and VARs that want the benefits of embedding commercial binaries of MySQL software in their commercial applications but do not want to be subject to the GPL and do not want to release the source code for their proprietary applications should purchase a commercial license from Oracle. Purchasing a commercial license means that the GPL does not apply, and a commercial license includes the assurances that distributors typically find in commercial distribution agreements. 翻譯一下，原始設備製造商，獨立軟體供應商和增值經銷商希望將MySQL軟體的商業二進位文件嵌入其商業應用程式中，但不希望受到GPL的限制，並且不想為其專有應用程式發布原始碼，則應購買Oracle的商業許可證。購買商業許可證意味著GPL不適用，商業許可證包括分銷商通常在商業分銷協議中的條款。 也就是說，如果你想把MySQL加入到你的商業軟體中，那就得買Oracle的商業許可，這個許可和普通的商業許可沒有什麼區別。 購買MySQL官網上的企業版就是購買商業許可嗎？ 我們知道MySQL分好多版本，官網上除了社區版外，還有企業版、標準版、經典版等需要付費的版本，甚至有MySQL的業務員說你要想商業應用就必須買付費版本，這個說法其實是錯誤的，要不就是業務員本身不懂，要不就是他們在忽悠你。 GPL協議明確說了開源軟體是可以用於商業目的，只要遵守 GPL 協議就可以了，只要你不是去賣這個軟體，哪怕用在你的生產環境上，也是不用付費的。 而 GPL 規定的開源軟體收費模式, 就是為分發這個軟體或者提供技術服務收費。就是我為了把這個軟體發給你，可能要建個網站，或者可能得讓員工給你發個郵件，為這種勞動可以收費，也可以是我為你提供技術服務，幫你維護軟體而收費。MySQL官網上的那些付費版本，其實就是在賣技術服務，並不是在賣版權。比如 MySQL 企業版提供的服務，高可用、集群、防火牆、審計、備份等等，這些都是維護 MySQL 伺服器的技術，本身並不屬於 MySQL。這些服務，換作其他公司，比如阿里雲，也是可以提供的，也可以向你收費。 所以說 MySQL 的商業許可和 Oracle 官網上的付費版本並不是一回事，即使是在生產環境上我們也可以大膽的用 MySQL，完全合法。但如果你基於MySQL開發了個新軟體出售，又想閉源，那就必須經過 Oracle 同意了。 MariaDB 是怎麼回事？會有被閉源的風險嗎？ 這裡得說一下 MySQL 之父 Monty，他是 MySQL 第一行代碼的作者，後來成立了 MySQL AB 對 MySQL 進行商業化運作，也是他給 MySQL 定的雙許可模式。2008年的時候 MySQL 被 Sun 收購，僅接著2009年又被 Oracle 收購。Monty 沒有加入 Sun，而是又創立了一個新公司 Monty Program AB，在 MySQL 代碼的基礎上發布了新的分支，就稱為 MariaDB。以布 MariaDB 的原因，Monty說是因為想要一個永遠保持開源的 MySQL 替代品。吸取了 MySQL 的教訓，Money 將 MariaDB 的版權交給了 MariaDB 基金會，一個非盈利性組織，以保持 MariaDB 能夠永遠開源。 這裡要說明的是，MariaDB 在 MySQL 的基礎上開發是完全沒問題的，因為 MariaDB 也是開源的，相當於是在遵守 GPL 的基礎上使用 MySQL，即使往後 Oracle 將 MySQL 閉源了，也不會影響之前版本的 GPL 協議。就像你房子已經賣出去了，過一年之後漲價了，你也沒有權利把房子要回來再按新價格賣一遍了。 還有一點是，MariaDB 既然是在 MySQL 基礎上開發的，那就必然是 GPL 開源許可證了，在沒有 Oracle 商業授權的情況下，其實是沒有權利閉源的。 結語 版權所有者擁有完全的權利，使得他們在發布軟體的時候可以按自己意願決定別人怎麼使用，所以 Oracle 可以讓你使用的時候遵守 GPL，也可以同意你不遵守 GPL，甚至可以把 MySQL 閉源。其實商業公司把持開源軟體的版權多少是有些耍流氓的，因為開源軟體的版權是屬於所有代碼貢獻者的，Oracle 為了規避法律風險，在與代碼貢獻者的協議里明確要求 Oracle 是代碼版權的共同所有者，也是盡力了。。。 ---------- 20200302 download MySQL https://www.mysql.com/ No login account neeeded to download community version from developer's page. mysql-installer-community-8.0.19.0.msi, 內含 Server, Shell, Router, Workbench, for excel, for Visual Studio, Connectors, Examples, tutorials, and Document, Notifier Server: 8.0 安裝(相容傳統 connector) 確認可連接 5.7 Connector. 待測試 8.0 安裝(Standard connector) https://www.mysql.com/products/workbench/ mysql-workbench-community-8.0.19-winx64.msi 只需要 workbench 適用. MySQL Notifier 1.1 source https://github.com/mysql/mysql-notifier MySQL使用工具簡介 http://newsletter.ascc.sinica.edu.tw/news/read_news.php?nid=2050 Account/Pswd Root Account Password: Pass1234!! 安裝測試用, 正式環境別再使用. 需大小寫、數字及特殊符號 MySQL User Accounts: user1/Pass1234!! 安裝測試用, 正式環境別再使用. 注意 mysql 使用者帳號區分大小寫 !!! ---------- 20200225 password EXPIRE ALTER USER `root`@`localhost` IDENTIFIED BY 'new_password', `root`@`localhost` PASSWORD EXPIRE NEVER; ---------- 20200225 忘記密碼時如何重設密碼 ref: https://emn178.pixnet.net/blog/post/87659567 How to change / reset MySQL password. 說明MySQL如何修改密碼與忘記密碼時如何重設密碼。 設定root密碼 一開始安裝好mysql時，root可能尚未設定密碼，可以用以下指令設定 ? 1 # mysqladmin -u root password '你的密碼' 或者 ? 1 # mysqladmin -u root password 再輸入密碼 修改使用者密碼 方法一 使用有權限或要修改的使用者本身登入mysql ? 1 2 3 # mysql -u 登入使用者 -p mysql> SET PASSWORD FOR '目標使用者'@'主機' = PASSWORD('密碼'); mysql> flush privileges; 例如 ? 1 2 3 # mysql -u root -p mysql> SET PASSWORD FOR 'emn178'@'localhost' = PASSWORD('password'); mysql> flush privileges; 方法二 使用有權限的使用者登入mysql ? 1 2 3 4 # mysql -u 登入使用者 -p mysql> use mysql; mysql> UPDATE user SET Password=PASSWORD("密碼") WHERE User='目標使用者'; mysql> flush privileges; 例如 ? 1 2 3 4 # mysql -u root -p mysql> use mysql; mysql> UPDATE user SET Password=PASSWORD("password") WHERE User='emn178'; mysql> flush privileges; 上面是不分主機位址的修改，若要像方法一區分主機的話再加上Host條件，例如 ? 1 mysql> UPDATE user SET Password=PASSWORD("password") WHERE User='emn178' AND Host = 'localhost'; 方法三 同樣利用mysqladmin指令可以修改root或其他使用者密碼，但該使用者必須有SUPER權限 ? 1 # mysqladmin -u 使用者 -p'舊密碼' password '新密碼' 或者 ? 1 # mysqladmin -u 使用者 -p password 再輸入密碼 忘記密碼重設 如果忘記root密碼可以用以下方式重設 ? 1 2 # /etc/init.d/mysql stop # mysqld_safe --skip-grant-tables & 用上面方式啟動mysql後可以不用輸入密碼直接連入 ? 1 # mysql -u root 接者使用修改使用者密碼的方法二修改root密碼，例如 ? 1 2 3 4 mysql> use mysql; mysql> UPDATE user SET Password=PASSWORD("password") WHERE User='root'; mysql> flush privileges; mysql> quit 最後重新啟動mysql ? 1 2 # /etc/init.d/mysql stop # /etc/init.d/mysql start ---------- mysql訪問許可權GRANT ALL PRIVILEGES ON，訪問許可權表 mysql> USE mysql; -- 切換到 mysql DB Database changed mysql> SELECT User, Password, Host FROM user; -- 檢視現有使用者,密碼及允許連線的主機 +------+----------+-----------+ | User | Password | Host | +------+----------+-----------+ | root | | localhost | +------+----------+-----------+ 1 row in set (0.00 sec) mysql> -- 只有一個預設的 root 使用者, 密碼為空, 只允許 localhost 連線 12 mysql> -- 下面我們另外新增一個新的 root 使用者, 密碼為空, 只允許 192.168.1.100 連線 mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100' IDENTIFIED BY '' WITH GRANT OPTION; mysql> -- @'192.168.1.100'可以替換為@‘%’就可任意ip訪問，當然我們也可以直接用 UPDATE 更新 root 使用者 Host, 但不推薦, SQL如下: mysql> -- UPDATE user SET Host='192.168.1.100' WHERE User='root' AND Host='localhost' LIMIT 1; mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 如何遠端訪問MySQL資料庫設定許可權方法總結，討論訪問單個數據庫，全部資料庫，指定使用者訪問，設定訪問密碼，指定訪問主機。 1，設定訪問單個數據庫許可權 mysql>grant all privileges on test.* to 'root'@'%'; 說明：設定使用者名稱為root，密碼為空，可訪問資料庫test 2，設定訪問全部資料庫許可權 mysql>grant all privileges on *.* to 'root'@'%'; 說明：設定使用者名稱為root，密碼為空，可訪問所有資料庫* 3，設定指定使用者名稱訪問許可權 mysql>grant all privileges on *.* to 'liuhui'@'%'; 說明：設定指定使用者名稱為liuhui，密碼為空，可訪問所有資料庫* 4，設定密碼訪問許可權 mysql>grant all privileges on *.* to 'liuhui'@'%' IDENTIFIED BY 'liuhui'; 說明：設定指定使用者名稱為liuhui，密碼為liuhui，可訪問所有資料庫* 5，設定指定可訪問主機許可權 mysql>grant all privileges on *.* to 'liuhui'@'10.2.1.11'; 說明：設定指定使用者名稱為liuhui，可訪問所有資料庫*，只有10.2.1.11這臺機器有許可權訪問 還可以設定指定訪問某個資料庫下的某個資料表，請繼續關注MySQL基礎知識系列。 使用root賬戶進入mysql create user 'test'@'localhost' indentified by '123456'; grant all privileges on *.* to 'test'@'localhost' identified by '123456'; grant all privileges on *.* to 'test'@'%' identified by '123456'; ＃在其它任意臺電腦上訪問 flush privileges; /* 使用者和許可權管理 */ ------------------ 使用者資訊表：mysql.user -- 重新整理許可權 flush privileges -- 增加使用者 create user 使用者名稱 identified by [password] 密碼(字串) - 必須擁有mysql資料庫的全域性create user許可權，或擁有insert許可權。 - 只能建立使用者，不能賦予許可權。 - 使用者名稱，注意引號：如 'user_name'@'192.168.1.1' - 密碼也需引號，純數字密碼也要加引號 - 要在純文字中指定密碼，需忽略password關鍵詞。要把密碼指定為由password()函式返回的混編值，需包含關鍵字password -- 重新命名使用者 rename user old_user to new_user -- 設定密碼 set password = password('密碼') -- 為當前使用者設定密碼 set password for 使用者名稱 = password('密碼') -- 為指定使用者設定密碼 -- 刪除使用者 drop user 使用者名稱 -- 分配許可權/新增使用者 grant 許可權列表 on 表名 to 使用者名稱 [identified by [password] 'password'] - all privileges 表示所有許可權 - *.* 表示所有庫的所有表 - 庫名.表名 表示某庫下面的某表 -- 檢視許可權 show grants for 使用者名稱 -- 檢視當前使用者許可權 show grants; 或 show grants for current_user; 或 show grants for current_user(); -- 撤消許可權 revoke 許可權列表 on 表名 from 使用者名稱 revoke all privileges, grant option from 使用者名稱 -- 撤銷所有許可權 -- 許可權層級 -- 要使用grant或revoke，您必須擁有grant option許可權，並且您必須用於您正在授予或撤銷的許可權。 全域性層級：全域性許可權適用於一個給定伺服器中的所有資料庫，mysql.user grant all on *.*和 revoke all on *.*只授予和撤銷全域性許可權。 資料庫層級：資料庫許可權適用於一個給定資料庫中的所有目標，mysql.db, mysql.host grant all on db_name.*和revoke all on db_name.*只授予和撤銷資料庫許可權。 表層級：表許可權適用於一個給定表中的所有列，mysql.talbes_priv grant all on db_name.tbl_name和revoke all on db_name.tbl_name只授予和撤銷表許可權。 列層級：列許可權適用於一個給定表中的單一列，mysql.columns_priv 當使用revoke時，您必須指定與被授權列相同的列。 -- 許可權列表 all [privileges] -- 設定除grant option之外的所有簡單許可權 alter -- 允許使用alter table alter routine -- 更改或取消已儲存的子程式 create -- 允許使用create table create routine -- 建立已儲存的子程式 create temporary tables -- 允許使用create temporary table create user -- 允許使用create user, drop user, rename user和revoke all privileges。 create view -- 允許使用create view delete -- 允許使用delete drop -- 允許使用drop table execute -- 允許使用者執行已儲存的子程式 file -- 允許使用select...into outfile和load data infile index -- 允許使用create index和drop index insert -- 允許使用insert lock tables -- 允許對您擁有select許可權的表使用lock tables process -- 允許使用show full processlist references -- 未被實施 reload -- 允許使用flush replication client -- 允許使用者詢問從屬伺服器或主伺服器的地址 replication slave -- 用於複製型從屬伺服器（從主伺服器中讀取二進位制日誌事件） select -- 允許使用select show databases -- 顯示所有資料庫 show view -- 允許使用show create view shutdown -- 允許使用mysqladmin shutdown super -- 允許使用change master, kill, purge master logs和set global語句，mysqladmin debug命令；允許您連線（一次），即使已達到max_connections。 update -- 允許使用update usage -- “無許可權”的同義詞 grant option -- 允許授予許可權 4.4. MySQL使用者帳號管理 MySQL使用者帳號管理主要用grant(授權)和revoke(撤權)兩個SQL指令來管理。這兩個指令實質是通過操作user(連線許可權和全域性許可權)、db(資料庫級許可權)、tables_priv(資料表級許可權)、columns_priv(資料列級許可權)四個許可權表來分配許可權的。host許可權表不受這兩個指令影響。下面將會詳細介紹使用者許可權管理的內容。 GRANT語法說明： GRANT privileges (columns) #privileges表示授予的許可權，columns表示作用的列(可選) ON what #設定許可權級別，全域性級、資料庫級、資料表級和資料列級 TO account #許可權授予的使用者，用"user_name"@"host_name"這種使用者名稱、主機名格式 IDENTIFIED BY 'password' #設定使用者帳號密碼 REQUIRE encryption requirements #設定經由SSL連線帳號 WITH grant or resource management options; #設定帳號的管理和資源(連線伺服器次數或查詢次數等)選項 示例： mysql>grant all on db.* to 'test'@'localhost' identified by 'test'; 上例執行後的效果是，test使用者只能通過‘test’密碼從本機訪問db資料庫 mysql>grant all on db.* to 'test'@'%' identified by 'test'; 上例執行後的效果是，test使用者可通過‘test’密碼從任意計算機上訪問db資料庫。‘%’代表任意字元，‘_’代表一個任意字元。主機名部份還可以是IP地址。 如果沒有給定主機部份，則預設為任意主機，也就是'test'和'test'@'%'是等價的。 Table 4.1. 訪問許可權表 許可權 許可權說明 CREATE TEMPORARY TABLES 建立臨時資料表 EXECUTE 執行儲存過程(暫不支援) FILE 作業系統檔案 GRANT OPTION 可把本帳號的許可權授予其它使用者 LOCK TABLES 鎖定指定資料表 PROCESS 檢視執行著的執行緒資訊 RELOAD 重新載入許可權表或重新整理日誌及緩衝區 REPLICATION CLIENT 可查詢主/從伺服器主機名 REPLICATION SLAVE 執行一個映象從伺服器 SHOW DATABASES 可執行SHOW DATABASES指令 SHUTDOWN 關閉資料庫伺服器 SUPER 可用kill終止執行緒以及進行超級使用者操作 ALTER 可修改表和索引的結構 CREATE 建立資料庫和資料表 DELETE 刪除資料表中的資料行 DROP 刪除資料表和資料行 INDEX 建立或刪除索引 INSERT 插入資料行 REFERENCES (暫時不支援) SELECT 查詢資料行 UPDATE 更新資料行 ALL 所有許可權，但不包括GRANT。 USAGE 無許可權許可權 Table 4.2. 許可權作用範圍(由ON子句設定) 許可權限定符 作用範圍 ON *.* 全域性級許可權，作用於所有資料庫 ON * 全域性級許可權，若未指定預設資料庫，其作用範圍是所有資料庫，否則，其作用範圍是當前資料庫 ON db_name.* 資料庫級許可權，作用於指定資料庫裡的所有資料表 ON db_name.tbl_name 資料表級許可權，作用於資料表裡的所有資料列 ON tbl_name 資料表級許可權，作用於預設資料庫中指定的資料表裡的所有資料列 USAGE許可權的用法：修改與許可權無關的帳戶項，如： mysql>GRANT USAGE ON *.* TO account IDENTIFIED BY 'new_password'; #修改密碼 mysql>GRANT USAGE ON *.* TO account REQUIRE SSL; #啟用SSL連線 mysql>GRANT USAGE ON *.* TO account WITH MAX_CONNECTIONS_PER_HOUR 10; #設定資源 擁有WITH GRANT OPTION許可權的使用者可把自已所擁用的許可權轉授給其他使用者，如： mysql>GRANT ALL ON db.* TO 'test'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; 這樣test使用者就有權把該許可權授予其他使用者。 限制資源使用，如： mysql>GRANT ALL ON db.* TO account IDENTIFIED BY 'password' WITH MAX_CONNECTIONS_PER_HOUR 10 MAX_QUERIES_PER_HOUR 200 MAX_UPDATES_PER_HOUR 50; 允許account使用者每小時最多連線20次伺服器，每小時最多發出200條查詢命令(其中更新命令最多為50條) 預設都是零值，即沒有限制。FLUSH USER_RESOURCES和FLUSH PRIVILEGES可對資源限制計數器清零。 REVOKE語法說明： mysql>REVOKE privileges (columns) ON what FROM account; 示例： mysql>REVOKE SELECT ON db.* FROM 'test'@'localhost'; 刪除test帳號從本機查詢db資料庫的許可權 REVOKE可刪除許可權，但不能刪除帳號，即使帳號已沒有任何許可權。所以user資料表裡還會有該帳號的記錄，要徹底刪除帳號，需用DELETE命令刪除user資料表的記錄，如： % mysql -u root -p mysql>use mysql mysql>DELETE FROM user where User='test' and Host='localhost'; mysql fulsh privileges; REVOKE不能刪除REQUIRE和資源佔用的配置。他們是要用GRANT來刪除的，如： GRANT USAGE ON *.* TO account REQUIRE NONE; #刪除account帳號的SSL連線選項 GRANT USAGE ON *.* TO account WITH MAX_CONNECTIONS_PER_HOUR 0 MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0; #刪除account帳號的資源限制 ---------- 20200221 清除 log 步驟: 1. 壓縮 mysql bin log #!/bin/bash echo -n "壓縮mysql-binary 從幾開始" read startnum echo -n "到幾結束" read endnum index=0 while [ $startnum -le $endnum ] do file="/var/lib/mysql/mysql-bin.0$startnum" echo -n "$startnum" if [ -f $file ] then # bin log存在就壓縮，這裡將壓縮檔放在mysql/binbackup/裡，可自行選擇 tar zcvPf /var/lib/mysql/binbackup/mysql-bin-"$startnum".tar "$file" echo -n "$file 壓縮完成" ((index++)) else echo "$file is empty" fi (( startnum++ )) done echo "完成 $index 個壓縮" 2. 進入mysql, 清除 bin log mysql -u username -p purge master logs to 'mysql-bin.000403'; //000403之前的都會被清空 ------------------------------ 設定 mysql 只保留 1 天前的 log 只保留 1天前的 mysql log Linux 預設 mysql 的 binary log 檔會保留10天 ( /var/log/mysql/mysql-bin.xxxxxx )， 當硬碟空間(/var分割區)不是很大且mysql的資料量又多時，常常因為保留太多的 binary log 而造成空間不夠 解法如下： (開啟 mysql設定檔) vi /etc/mysql/my.cnf 若為 Windows 版本, 則為 c:\ProgramData\MySQL\MySQL Server 5.7\My.ini 或 直接在 MySQL Workbench UI 中選擇 (找到以下部份) # The following can be used as easy to replay backup logs or for replication. #server-id = 1 log_bin = /var/log/mysql/mysql-bin.log # WARNING: Using expire_logs_days without bin_log crashes the server! expire_logs_days = 100 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name (把預設保留天數改成1天)expire_logs_days = 1 改完之後記得service mysqld restart 就一切搞定了。 (重新啟動時就會自動刪去超過1天的 binary log) ---------- 20200220 Column Data types 整數型別: Type Storage Minimum Value Maximum Value (Bytes) (Signed/Unsigned) (Signed/Unsigned) Digits --------- ------- ----------------- -------------------- ------ TINYINT 1 -128 127 3 0 255 3 SMALLINT 2 -32768 32767 5 0 65535 5 MEDIUMINT 3 -8388608 8388607 7 0 16777215 8 INT 4 -2147483648 2147483647 10 0 4294967295 10 BIGINT 8 -9223372036854775808 9223372036854775807 19 0 18446744073709551615 20 --------- ------- ----------------- -------------------- ------ 欄位 int(11) 並非限制 int 的長度為11位, 而是顯示寬度. 顯示寬度與int 的儲存長度完全無關. 無論設為多少, int 型別儲存長度不變. 顯示寬度可用在: 當int欄位型別設定為無符號且填充零（UNSIGNED ZEROFILL）時，當數值位數未達到設定的顯示寬度時，會在數值前面補充零直到滿足設定的顯示寬度，為什麼會有無符號的限制呢，是因為ZEROFILL屬性會隱式地將數值轉為無符號型，因此不能儲存負的數值。 例如: CREATE TABLE int_demo ( id INT(11) NOT NULL AUTO_INCREMENT, a INT(11) NOT NULL, b INT(11) UNSIGNED ZEROFILL NOT NULL, c INT(5) DEFAULT NULL, d INT(5) UNSIGNED ZEROFILL NOT NULL, e INT(15) DEFAULT NULL, PRIMARY KEY (`id`) ) INSERT INTO int_demo (a, b, c, d, e) VALUES (1, 1, 1, 1, 1); INSERT INTO int_demo (a, b, c, d, e) VALUES (1234567890, 1234567890, 1234567890, 1234567890, 1234567890); select * from int_demo; id a b c d e 1 1 00000000001 1 00001 1 2 1234567890 01234567890 1234567890 1234567890 1234567890 如果一個欄位設定了無符號和填充零屬性，那麼無論這個欄位儲存什麼數值，數值的長度都會與設定的顯示寬度一致，如上述例子中的欄位b，插入數值1顯示為00000000001，左邊補了10個零直至長度達到11位； 設定欄位的顯示寬度並不限制欄位儲存值的範圍，比如欄位d設定為int(5)，但是仍然可以儲存1234567890這個10位數字； 設定的字元寬度只對數值長度不滿足寬度時有效，如d欄位int(5)，插入1時，長度不足5，因此在左邊補充4個零直到5位，但是插入1234567890時超過了5位，這時的顯示寬度就起不了作用了。 signed or unsigned ? If you do not specify unsigned, then bigint will be signed. If you specify an unsigned, then bigint will be unsigned. Let us first create a table − mysql> create table DemoTable ( Number bigint, // signed Number2 bigint unsigned // unsigned ); Query OK, 0 rows affected (1.08 sec) Insert records in the table using insert command − mysql> insert into DemoTable values(18446744073709551615,18446744073709551615); ERROR 1264 (22003): Out of range value for column 'Number' at row 1 mysql> insert into DemoTable values(9223372036854775807,18446744073709551615); Query OK, 1 row affected (0.28 sec) Display all records from the table using select statement − mysql> select * from DemoTable; This will produce the following output − +---------------------+----------------------+ | Number | Number2 | +---------------------+----------------------+ | 9223372036854775807 | 18446744073709551615 | +---------------------+----------------------+ 1 row in set (0.00 sec) Convert Date to Milliseconds in MySQL Use the UNIX_TIMESTAMP function. SELECT (UNIX_TIMESTAMP(mydate)*1000) FROM... UNIX_TIMESTAMP will get you seconds and you need to multiply by 1000 to get milliseconds. To convert back, use FROM_UNIXTIME() function. SELECT FROM_UNIXTIME(date_in_milliseconds/1000) FROM ... Again, you need to divide by 1000 to get it to seconds before using the function. You need to be at MySQL version 5.6.4 or later to declare columns with fractional-second time datatypes. Not sure you have the right version? Try SELECT NOW(3). If you get an error, you don't have the right version. For example, DATETIME(3) will give you millisecond resolution in your timestamps, and TIMESTAMP(6) will give you microsecond resolution on a *nix-style timestamp. Read this: https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html NOW(3) will give you the present time from your MySQL server's operating system with millisecond precision. If you have a number of milliseconds since the Unix epoch, try this to get a DATETIME(3) value FROM_UNIXTIME(ms * 0.001) Javascript timestamps, for example, are represented in milliseconds since the Unix epoch. (Notice that MySQL internal fractional arithmetic, like * 0.001, is always handled as IEEE754 double precision floating point, so it's unlikely you'll lose precision before the Sun becomes a white dwarf star.) If you're using an older version of MySQL and you need subsecond time precision, your best path is to upgrade. Anything else will force you into doing messy workarounds. If, for some reason you can't upgrade, you could consider using BIGINT or DOUBLE columns to store Javascript timestamps as if they were numbers. FROM_UNIXTIME(col * 0.001) will still work OK. If you need the current time to store in such a column, you could use UNIX_TIMESTAMP() * 1000 ---------- 20200214 Delete samples: DELETE FROM tbl WHERE id <> 0 DELETE FROM tbl WHERE id <> 'all' safemode 當 safemode = 1 時, 不能執行(沒有 primary key 查詢條件)的刪除指令. 必須先關閉 safemode, 才能執行: SET SQL_SAFE_UPDATES = 0; DELETE FROM instructor WHERE salary BETWEEN 13000 AND 15000; SET SQL_SAFE_UPDATES = 1; 或是指定 primary key 查詢條件. DELETE FROM tbl WHERE id <> 0 DELETE FROM tbl WHERE id <> 'all' sample error: delete from tarea where fid is not null or fid = 'aaa' Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 原來是發現mysql有個叫SQL_SAFE_UPDATES的變數 開啟的狀態下，在沒有 WHERE 或 LIMIT 條件的 UPDATE 或 DELETE 動作會拒絕執行而即使是有 WHERE 和 LIMIT 條件，但沒有 KEY column 的 WHERE 條件也會拒絕執行。 所以只要在執行下面的指令即可 SET SQL_SAFE_UPDATES=0;//關閉 //You want do something... UPDATE XXXXXX SET XXXXXX WHERE XXXXXXX; DELETE XXXXXX WHERE XXXXXXX; SET SQL_SAFE_UPDATES=1;//結束後再開啟 ---------- 20200214 Insert Samples: 參考: CreateDBTemplate*.sql 單筆資料: INSERT INTO `tfloor` VALUES ('b1',1,'map-b1-1.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'); INSERT INTO `tfloor` VALUES ('b1',1,'map-b1-1.jpg',NULL,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP); insert into treader (FID, FIP, FPort, FCreateTime) values ('ID', 'IP', 1234, CURRENT_TIMESTAMP) 多筆資料: INSERT INTO `tfloor` VALUES ('b1',1,'map-b1-1.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b1',2,'map-b1-2.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b1',3,'map-b1-3.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b1',4,'map-b1-4.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b1',5,'map-b1-5.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b1',6,'map-b1-6.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b1',7,'map-b1-7.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b1',8,'map-b1-8.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b1',9,'map-b1-9.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b1',10,'map-b1-10.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b2',-1,'','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b2',1,'','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b2',2,'','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b2',3,'','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('b2',4,'','','2020-02-13 16:18:01','2020-02-13 16:18:01'),('gps',1,'map-gps-1.jpg','','2020-02-13 16:18:01','2020-02-13 16:18:01'); 多列字串: INSERT INTO `tconfig` VALUES (0,NULL,NULL,'KeyList','代碼清單',NULL,1,'FKey for 程式控制.\r\nFValue for 主要語言.\r\nFValueB for 次要語言或匯入語言.','2020-01-17 13:33:03','2020-01-17 13:33:03'),(1000,0,NULL,'Gender','性別',NULL,NULL,'Male 中文為男,\r\nFemale 中文是女.','2020-01-17 13:33:03','2020-01-17 13:33:03'),(1001,1000,10,'M','男','Male',NULL,NULL,'2020-01-17 13:33:03','2020-01-17 13:33:03'),(1002,1000,20,'F','女','Female',NULL,NULL,'2020-01-17 13:33:03','2020-01-17 13:33:03'),(1003,0,NULL,'Marriage','婚姻',NULL,NULL,'婚姻狀態除了單身和已婚以外,\r\n還可以細分為單身未婚、單身已婚.','2020-01-17 13:33:03','2020-01-17 13:33:03'),(1004,1003,10,'Single','單身',NULL,NULL,NULL,'2020-01-17 13:33:03','2020-01-17 13:33:03'),(1005,1003,20,'Married','已婚',NULL,NULL,NULL,'2020-01-17 13:33:03','2020-01-17 13:33:03'); 複製到不同的資料庫中, 適用於複製資料 或 備份 Log INSERT INTO newDatabase.table1 (Column1, Column2) SELECT SomeOtherColumn, MoreColumns FROM oldDatabase.table1; INSERT INTO newDatabase.users (name, city, email, username, added_by) SELECT CONCAT(first_name, ' ', last_name), 'Asgard', CONCAT(first_name,'@gmail.com'), CONCAT(first_name,last_name), 'Damir' FROM oldDatabase.old_users; ---------- 20200214 AUTO_INCREMENT 自動增值欄位 1. 預設=1. 2. 必須為 Primary Key 或 UNIQUE KEY. 3. 1個table 只能指定一個 column 為 AUTO_INCREMENT 欄位. 4. 預設 若 AUTO_INCREMENT 欄位碰到 0 或 null 值, 則會以下一個序號新增. 5. 若 sql_mode='NO_AUTO_VALUE_ON_ZERO'模式時, 則只會碰到(Null)才以下一個號碼新增, 0則不會AUTO_INCREMENT. auto-increment column https://www.w3schools.com/sql/sql_autoincrement.asp 建立 AUTO_INCREMENT 欄位: CREATE TABLE Persons ( Personid int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (Personid) ); 變更新增值距, 預設=1 MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement: the auto_increment column must be part of the PRIMARY KEY or a UNIQUE KEY (and in almost 100% of the cases, it should be the only column that makes up the PRIMARY KEY): ALTER TABLE Persons AUTO_INCREMENT=100; ALTER TABLE document MODIFY COLUMN document_id INT auto_increment ALTER TABLE document MODIFY document_id INT AUTO_INCREMENT PRIMARY KEY 重設 ref: https://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql ALTER TABLE tablename AUTO_INCREMENT = 1; 例如: delete from ttracelog; ALTER TABLE ttracelog AUTO_INCREMENT = 1; 重設數字應等於(目前 Table 中的最大數 + 1) ??? 注意重設數字必須大於等於(目前TABLE 中的最大數) ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED; COMMIT; ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED AUTO_INCREMENT; COMMIT; NO_AUTO_VALUE_ON_ZERO SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO' 預設(AUTO_INCREMENT)在 insert 時, 碰到(0 或 Null) 會以下一個號碼新增. 若 sql_mode='NO_AUTO_VALUE_ON_ZERO'模式時, 則只會碰到(Null)才以下一個號碼新增, 0則不會AUTO_INCREMENT 例如: 開啟 NO_AUTO_VALUE_ON_ZERO: SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO'; 關閉 NO_AUTO_VALUE_ON_ZERO: SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'NO_AUTO_VALUE_ON_ZERO','')); NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) ----------, 2022-05-06, ####Trigger mysql> delimiter // mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account FOR EACH ROW BEGIN IF NEW.amount < 0 THEN SET NEW.amount = 0; ELSEIF NEW.amount > 100 THEN SET NEW.amount = 100; END IF; END;// mysql> delimiter ; Trigger samples: sample 1: Trigger AFTER INSERT /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `dbtrackerb`.`ttracelog_AFTER_INSERT` AFTER INSERT ON `ttracelog` FOR EACH ROW BEGIN IF(SELECT EXISTS(SELECT 1 FROM ttrace WHERE FID = NEW.FID AND FType=1 LIMIT 1)) THEN update ttrace set FNo = NEW.FNo WHERE FID = NEW.FID and FType=1; ELSE insert into ttrace (FID, FType, FNo) VALUES (NEW.FID, 1, NEW.FNo); END IF; IF(NEW.FWarn = 1) THEN IF(SELECT EXISTS(SELECT 1 FROM ttrace WHERE FID = NEW.FID AND FType=2 LIMIT 1)) THEN update ttrace set FNo = NEW.FNo WHERE FID = NEW.FID and FType=2; ELSE insert into ttrace (FID, FType, FNo) VALUES (NEW.FID, 2, NEW.FNo); END IF; END IF; IF(NEW.FIsPressureBase = 1) THEN IF(SELECT EXISTS(SELECT 1 FROM ttrace WHERE FID = NEW.FID AND FType=3 LIMIT 1)) THEN update ttrace set FNo = NEW.FNo WHERE FID = NEW.FID and FType=3; ELSE insert into ttrace (FID, FType, FNo) VALUES (NEW.FID, 3, NEW.FNo); END IF; END IF; END */;; sample 2: Trigger AFTER INSERT 參考: CreateDBTemplate*.sql CREATE DEFINER=`root`@`localhost` TRIGGER `dbtracker`.`ttracelog_AFTER_INSERT` AFTER INSERT ON `ttracelog` FOR EACH ROW BEGIN declare iTrace INT default 0; declare iWarn INT default 0; SET iTrace = (SELECT COUNT(*) FROM ttrace WHERE FID = NEW.FID); IF(iTrace > 0) THEN update ttrace set FNo = NEW.FNo ,FKeyGroup = NEW.FKeyGroup ,FKeyID = NEW.FKeyID ,FTimeReceive = NEW.FTimeReceive ,FSourceType = NEW.FSourceType ,FSourceTime = NEW.FSourceTime ,FReaderID = NEW.FReaderID ,FRepeaterID = NEW.FRepeaterID ,FTagStatus = NEW.FTagStatus ,FBeaconID = NEW.FBeaconID ,FRssiRola = NEW.FRssiRola ,FRssiBeacon = NEW.FRssiBeacon ,FRssiRolaMin = NEW.FRssiRolaMin ,FRssiBeaconMin = NEW.FRssiBeaconMin ,FGPSData = NEW.FGPSData ,FGPSRead = NEW.FGPSRead ,FLatitude = NEW.FLatitude ,FLongitude = NEW.FLongitude ,FPressure = NEW.FPressure ,FTemperatureP = NEW.FTemperatureP ,FTemperatureT = NEW .FTemperatureT ,FHumidity = NEW.FHumidity ,FStatus = NEW.FStatus ,FOwner = NEW.FOwner ,FIP = NEW.FIP ,FPort = NEW.FPort ,FDeviceType = NEW.FDeviceType ,FDeviceID = NEW.FDeviceID ,FBuilding = NEW.FBuilding ,FFloor = NEW.FFloor ,FMessage = NEW.FMessage ,FError = NEW.FError ,FWarnType = NEW.FWarnType ,FVersionTag = NEW.FVersionTag ,FCreateTime = NEW.FCreateTime ,FUpdateTime = NEW.FUpdateTime WHERE FID = NEW.FID; ELSE insert into ttrace ( FID ,FKeyGroup ,FKeyID ,FTimeReceive ,FSourceType ,FSourceTime ,FReaderID ,FRepeaterID ,FTagStatus ,FBeaconID ,FRssiRola ,FRssiBeacon ,FRssiRolaMin ,FRssiBeaconMin ,FGPSRead ,FLatitude ,FLongitude ,FPressure ,FTemperatureP ,FTemperatureT ,FHumidity ,FStatus ,FOwner ,FIP ,FPort ,FDeviceType ,FDeviceID ,FBuilding ,FFloor ,FMessage ,FError ,FWarnType ,FVersionTag ,FCreateTime ,FUpdateTime ) VALUES ( NEW.FID ,NEW.FKeyGroup ,NEW.FKeyID ,NEW.FTimeReceive ,NEW.FSourceType ,NEW.FSourceTime ,NEW.FReaderID ,NEW.FRepeaterID ,NEW.FTagStatus ,NEW.FBeaconID ,NEW.FRssiRola ,NEW.FRssiBeacon ,NEW.FRssiRolaMin ,NEW.FRssiBeaconMin ,NEW.FGPSRead ,NEW.FLatitude ,NEW.FLongitude ,NEW.FPressure ,NEW.FTemperatureP ,NEW.FTemperatureT ,NEW.FHumidity ,NEW.FStatus ,NEW.FOwner ,NEW.FIP ,NEW.FPort ,NEW.FDeviceType ,NEW.FDeviceID ,NEW.FBuilding ,NEW.FFloor ,NEW.FMessage ,NEW.FError ,NEW.FWarnType ,NEW.FVersionTag ,NEW.FCreateTime ,NEW.FUpdateTime ); END IF; IF(NEW.FWarnType > 0) THEN SET iWarn = (SELECT COUNT(*) FROM twarn WHERE FID = NEW.FID); IF(iWarn > 0) THEN update twarn set FNo = NEW.FNo ,FKeyGroup = NEW.FKeyGroup ,FKeyID = NEW.FKeyID ,FTimeReceive = NEW.FTimeReceive ,FSourceType = NEW.FSourceType ,FSourceTime = NEW.FSourceTime ,FReaderID = NEW.FReaderID ,FRepeaterID = NEW.FRepeaterID ,FTagStatus = NEW.FTagStatus ,FBeaconID = NEW.FBeaconID ,FRssiRola = NEW.FRssiRola ,FRssiBeacon = NEW.FRssiBeacon ,FRssiRolaMin = NEW.FRssiRolaMin ,FRssiBeaconMin = NEW.FRssiBeaconMin ,FGPSData = NEW.FGPSData ,FGPSRead = NEW.FGPSRead ,FLatitude = NEW.FLatitude ,FLongitude = NEW.FLongitude ,FPressure = NEW.FPressure ,FTemperatureP = NEW.FTemperatureP ,FTemperatureT = NEW .FTemperatureT ,FHumidity = NEW.FHumidity ,FStatus = NEW.FStatus ,FOwner = NEW.FOwner ,FIP = NEW.FIP ,FPort = NEW.FPort ,FDeviceType = NEW.FDeviceType ,FDeviceID = NEW.FDeviceID ,FBuilding = NEW.FBuilding ,FFloor = NEW.FFloor ,FMessage = NEW.FMessage ,FError = NEW.FError ,FWarnType = NEW.FWarnType ,FVersionTag = NEW.FVersionTag ,FCreateTime = NEW.FCreateTime ,FUpdateTime = NEW.FUpdateTime WHERE FID = NEW.FID; ELSE insert into twarn ( FID ,FKeyGroup ,FKeyID ,FTimeReceive ,FSourceType ,FSourceTime ,FReaderID ,FRepeaterID ,FTagStatus ,FBeaconID ,FRssiRola ,FRssiBeacon ,FRssiRolaMin ,FRssiBeaconMin ,FGPSRead ,FLatitude ,FLongitude ,FPressure ,FTemperatureP ,FTemperatureT ,FHumidity ,FStatus ,FOwner ,FIP ,FPort ,FDeviceType ,FDeviceID ,FBuilding ,FFloor ,FMessage ,FError ,FWarnType ,FVersionTag ,FCreateTime ,FUpdateTime ) VALUES ( NEW.FID ,NEW.FKeyGroup ,NEW.FKeyID ,NEW.FTimeReceive ,NEW.FSourceType ,NEW.FSourceTime ,NEW.FReaderID ,NEW.FRepeaterID ,NEW.FTagStatus ,NEW.FBeaconID ,NEW.FRssiRola ,NEW.FRssiBeacon ,NEW.FRssiRolaMin ,NEW.FRssiBeaconMin ,NEW.FGPSRead ,NEW.FLatitude ,NEW.FLongitude ,NEW.FPressure ,NEW.FTemperatureP ,NEW.FTemperatureT ,NEW.FHumidity ,NEW.FStatus ,NEW.FOwner ,NEW.FIP ,NEW.FPort ,NEW.FDeviceType ,NEW.FDeviceID ,NEW.FBuilding ,NEW.FFloor ,NEW.FMessage ,NEW.FError ,NEW.FWarnType ,NEW.FVersionTag ,NEW.FCreateTime ,NEW.FUpdateTime ); END IF; END IF; END 以下參考 https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html 25.3.1 Trigger Syntax and Examples To create a trigger or drop a trigger, use the CREATE TRIGGER or DROP TRIGGER statement, described in Section 13.1.22, “CREATE TRIGGER Statement”, and Section 13.1.34, “DROP TRIGGER Statement”. Here is a simple example that associates a trigger with a table, to activate for INSERT operations. The trigger acts as an accumulator, summing the values inserted into one of the columns of the table. mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount; Query OK, 0 rows affected (0.01 sec) The CREATE TRIGGER statement creates a trigger named ins_sum that is associated with the account table. It also includes clauses that specify the trigger action time, the triggering event, and what to do when the trigger activates: The keyword BEFORE indicates the trigger action time. In this case, the trigger activates before each row inserted into the table. The other permitted keyword here is AFTER. The keyword INSERT indicates the trigger event; that is, the type of operation that activates the trigger. In the example, INSERT operations cause trigger activation. You can also create triggers for DELETE and UPDATE operations. The statement following FOR EACH ROW defines the trigger body; that is, the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering event. In the example, the trigger body is a simple SET that accumulates into a user variable the values inserted into the amount column. The statement refers to the column as NEW.amount which means “the value of the amount column to be inserted into the new row.” To use the trigger, set the accumulator variable to zero, execute an INSERT statement, and then see what value the variable has afterward: mysql> SET @sum = 0; mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); mysql> SELECT @sum AS 'Total amount inserted'; +-----------------------+ | Total amount inserted | +-----------------------+ | 1852.48 | +-----------------------+ In this case, the value of @sum after the INSERT statement has executed is 14.98 + 1937.50 - 100, or 1852.48. To destroy the trigger, use a DROP TRIGGER statement. You must specify the schema name if the trigger is not in the default schema: mysql> DROP TRIGGER test.ins_sum; If you drop a table, any triggers for the table are also dropped. Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name. It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a clause after FOR EACH ROW that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing trigger. With PRECEDES, the new trigger activates before the existing trigger. For example, the following trigger definition defines another BEFORE INSERT trigger for the account table: mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account FOR EACH ROW PRECEDES ins_sum SET @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0), @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0); Query OK, 0 rows affected (0.01 sec) This trigger, ins_transaction, is similar to ins_sum but accumulates deposits and withdrawals separately. It has a PRECEDES clause that causes it to activate before ins_sum; without that clause, it would activate after ins_sum because it is created after ins_sum. Trigger 內含 (NEW, OLD 不分大小寫) 2組資料: Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive. INSERT trigger 只有 NEW 資料, 沒有 OLD 資料. DELETE trigger 只有 OLD 資料, 沒有 NEW 資料. UPDATE trigger 中 OLD 資料代表更新前的舊資料, NEW 代表更新後的新資料. In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated. OLD 資料只能讀, 不可寫. A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change has already occurred.) 在 BEFORE trigger 中, AUTO_INCREMENT 的欄位值為 0, 而不是預定新增的值. In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the sequence number that is generated automatically when the new row actually is inserted. 經由 BEGIN ... END 區塊指令, 可在 trigger 執行多行指令. 經由 'delimiter' 關鍵字定義 指令行分隔符號, 才能在 trigger 中以 ';' 作為指令行的分隔符號. By using the BEGIN ... END construct, you can define a trigger that executes multiple statements. Within the BEGIN block, you also can use other syntax that is permitted within stored routines such as conditionals and loops. However, just as for stored routines, if you use the mysql program to define a trigger that executes multiple statements, it is necessary to redefine the mysql statement delimiter so that you can use the ; statement delimiter within the trigger definition. The following example illustrates these points. It defines an UPDATE trigger that checks the new value to be used for updating each row, and modifies the value to be within the range from 0 to 100. This must be a BEFORE trigger because the value must be checked before it is used to update the row: mysql> delimiter // mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account FOR EACH ROW BEGIN IF NEW.amount < 0 THEN SET NEW.amount = 0; ELSEIF NEW.amount > 100 THEN SET NEW.amount = 100; END IF; END;// mysql> delimiter ; It can be easier to define a stored procedure separately and then invoke it from the trigger using a simple CALL statement. This is also advantageous if you want to execute the same code from within several triggers. There are limitations on what can appear in statements that a trigger executes when activated: The trigger cannot use the CALL statement to invoke stored procedures that return data to the client or that use dynamic SQL. (Stored procedures are permitted to return data to the trigger through OUT or INOUT parameters.) The trigger cannot use statements that explicitly or implicitly begin or end a transaction, such as START TRANSACTION, COMMIT, or ROLLBACK. (ROLLBACK to SAVEPOINT is permitted because it does not end a transaction.). See also Section 25.8, “Restrictions on Stored Programs”. MySQL handles errors during trigger execution as follows: If a BEFORE trigger fails, the operation on the corresponding row is not performed. A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds. An AFTER trigger is executed only if any BEFORE triggers and the row operation execute successfully. An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation. For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For nontransactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect. Triggers can contain direct references to tables by name, such as the trigger named testref shown in this example: CREATE TABLE test1(a1 INT); CREATE TABLE test2(a2 INT); CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 ); delimiter | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; | delimiter ; INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0); Suppose that you insert the following values into table test1 as shown here: mysql> INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 As a result, the four tables contain the following data: mysql> SELECT * FROM test1; +------+ | a1 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 8 | | 4 | | 4 | +------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM test2; +------+ | a2 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 8 | | 4 | | 4 | +------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM test3; +----+ | a3 | +----+ | 2 | | 5 | | 6 | | 9 | | 10 | +----+ 5 rows in set (0.00 sec) mysql> SELECT * FROM test4; +----+------+ | a4 | b4 | +----+------+ | 1 | 3 | | 2 | 0 | | 3 | 1 | | 4 | 2 | | 5 | 0 | | 6 | 0 | | 7 | 1 | | 8 | 1 | | 9 | 0 | | 10 | 0 | +----+------+ 10 rows in set (0.00 sec) ---------- 20200212 Update samples: update tarea set FLeft=579, FTop=579, FWidth=579, FHeight=579, FMapped=1 where FID = 'r-00001' ---------- 20200120 Windows 版本的 MySQL, table 的名稱不會區分大小寫. 雖然在 linux 上的 MySQL 可以區分大小寫, 但是建議最好不要使用. Column 名稱會區分大小寫. ref: https://stackoverflow.com/questions/28540573/lower-case-table-names-set-to-2-workbench-still-does-not-allow-lowercase-databa In Windows, table naming is case-insensitive. That is, your Customer table and your customer table will always be the same on Windows. That's a limitation of the NT File System. This applies when your MySQL server is running on a Windows platform. It doesn't matter where your workbench client is running. (You can use mixed-case table names for different tables on Linux, BSD, and the like, but it's considered very bad practice: only do that if you want to drive your colleagues crazy. So be careful.) If you leave this lower_case_table_names setting alone, you can use mixed case in your table names without problems. http://cosminswiki.info/index.php/MySQL_case_insensitive_table_and_column_names Column, index, and stored routine names are not case sensitive on any platform, nor are column aliases. Trigger names are case sensitive, which differs from standard SQL. Well, this is good news. At least with column names or aliases we are good to go. Same for stored routines. ---------- 20200116 MySQL 編碼 UTF-8 選擇 utf8mb4_unicode_ci = utf-8 編碼不區分大小寫. (預設) utf8mb4_unicode_cs = utf-8 編碼 區分大小寫. (沒有 cs 這種. 應該是 utf8_bin is case-sensitive) utf8_bin = utf-8 編碼 區分大小寫. ref: https://khiav223577.github.io/blog/2019/06/30/MySQL-編碼挑選與差異比較/ 先說結論 若只存英文、數字…等鍵盤上會出現的字的話，可以用 utf8_general_ci（但不推廌） 若會存中文、德文…等非英文的字、或存 Emoji 的話，建議用 utf8mb4_unicode_ci utf8mb4 vs. utf8 在 MySQL 5.5.3 以後，一般來說會建議用 utf8mb4。因為它才是真正的 UTF-8 編碼，且完全兼容 utf8。 utf8 速度比較快但只支援三個字元長度的字，這代表部份中文字以及 Emoji 要存入的時候會噴錯。因為 utf8 這個名字實在是太令人混淆了，很多人會誤以為它就是 UTF-8 編碼，因此後來官方也做了一些調整，將它改名為 utf8mb3，mb3 指的是用三個位元組儲存，mp4 則是用四個位元組儲存的意思。 到了 MySQL 8.0 時， Oracle 將 utf8mb4 設為了預設編碼，也做了大幅的效能優化，變得比 utf8mb3 快很多，因此再也沒有使用舊編碼的理由了，舊編碼也被官方標為 deprecated，在未來會被移除掉。效能優化如下圖所示：（紅線為 mb4，Y 軸為每秒 transactions 數量 ） ci vs. bin vs. cs 這三者就要看需求而定 ci 是指 case-insensitive，例如 utf8mb4_unicode_ci 是不分大小寫的 cs 是指 case-sensitive，例如 utf8mb4_unicode_cs 是會區分大小寫的 bin 會用 binary value 比對，例如 utf8mb4_bin 會區分大小寫的且也會區分 Ä 和 A 的不同 通常預設會用 _ci 結尾、不分大小寫的編碼 ，如果有需求的話，再看要改用其它哪個編碼。 general vs. unicode 一般會建議用 unicode，不要用 general 的版本。 general 版本（例如 utf8mb4_general_ci），在排序時比較快，但在某些特殊情況會排錯。 unicode 版本（例如 utf8mb4_unicode_ci），實作了完整的 Unicode 標準。 例如在德文的 ß 在排序時應該看作是 ss，但在 _general_ci 內會被當成 s。或是有些字元是隱藏字元，排序時不應該被拿來做計算之類的。中文字之間的排序可能也會不一樣。詳細差別可以參考「官方文件」中的說明。 benchmark 效能上的差異大概是： where column = ? 時，unicode 慢 3-10% where column like ? 時，unicode 慢 4-12% order by column 時，unicode 慢 8% (reference) 效能差距不大，為了保險起見用 unicode 比較不會遇到問題。除非有特殊需求或效能上的考量，再來考慮 general 的版本。但也有可能會因為 MySQL 新版本的優化，導致速度差異有變化，建議還是自己做一下 benchmark 評估看看要不要用 general 比較好。 ---------- 20200114 sha256_password 使用者帳號若設為sha256_password, 則 https://mysqlconnector.net/connection-options/ AllowPublicKeyRetrieval, Allow Public Key Retrieval If the user account uses sha256_password authentication, the password must be protected during transmission; TLS is the preferred mechanism for this, but if it is not available then RSA public key encryption will be used. To specify the server’s RSA public key, use the ServerRSAPublicKeyFile connection string setting, or set AllowPublicKeyRetrieval=True to allow the client to automatically request the public key from the server. Note that AllowPublicKeyRetrieval=True could allow a malicious proxy to perform a MITM attack to get the plaintext password, so it is False by default and must be explicitly enabled. ServerRSAPublicKeyFile, Server RSA Public Key File For sha256_password authentication. See comments under AllowPublicKeyRetrieval. ---------- 20200114 sql_mode 查看當前配置 SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode; SELECT @@sql_mode; SHOW VARIABLES LIKE "sql_mode"; 修改配置 服務運行中 # 一般設定 SET GLOBAL sql_mode = 'modes...'; SET SESSION sql_mode = 'modes...'; 新增設定的整合方法 # 新增 SET GLOBAL sql_mode=(SELECT CONCAT(@@sql_mode,',')); SET SESSION sql_mode=(SELECT CONCAT(@@sql_mode,',')); # 取代 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'','')); SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'','')); SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 設定檔 [mysqld] #set the SQL mode to strict #sql-mode="modes..." sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 例如: 開啟 NO_AUTO_VALUE_ON_ZERO: SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO'; 關閉 NO_AUTO_VALUE_ON_ZERO: SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'NO_AUTO_VALUE_ON_ZERO','')); ---------- 20200113 SQL_SAFE_UPDATES 在Safe Update Mode開啟的狀態下，在沒有 WHERE 或 LIMIT 條件的 UPDATE 或 DELETE 動作會拒絕執行，而即使是有 WHERE 和 LIMIT 條件，但沒有 KEY column 的 WHERE 條件也會拒絕執行 要關閉Safe Update Mode 可執行 "SET SQL_SAFE_UPDATES=0" 指令 要再開啟Safe Update Mode，就執行 "SET SQL_SAFE_UPDATES=1" 即可開啟 ---------- 20200513 DateTime 欄位預設值 11.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html 1. (預設與修改)自動更新為現在時間 (DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, the column has the current timestamp for its default value and is automatically updated to the current timestamp. CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); 2. (預設)自動更新為現在時間 (DEFAULT CURRENT_TIMESTAMP) With a DEFAULT clause but no ON UPDATE CURRENT_TIMESTAMP clause, the column has the given default value and is not automatically updated to the current timestamp. The default depends on whether the DEFAULT clause specifies CURRENT_TIMESTAMP or a constant value. With CURRENT_TIMESTAMP, the default is the current timestamp. CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ); (預設)自動更新為常數時間 (DEFAULT 0) With a constant, the default is the given value. In this case, the column has no automatic properties at all. CREATE TABLE t1 ( ts TIMESTAMP DEFAULT 0, dt DATETIME DEFAULT 0 ); 3. (預設)自動更新為常數時間 (DEFAULT 0), (修改)自動更新為現在時間 (ON UPDATE CURRENT_TIMESTAMP) With an ON UPDATE CURRENT_TIMESTAMP clause and a constant DEFAULT clause, the column is automatically updated to the current timestamp and has the given constant default value. CREATE TABLE t1 ( ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP ); 4. (修改)自動更新為現在時間 (ON UPDATE CURRENT_TIMESTAMP) With an ON UPDATE CURRENT_TIMESTAMP clause but no DEFAULT clause, the column is automatically updated to the current timestamp but does not have the current timestamp for its default value. 預設值取決於 Null 與否. 若允許 Null, 則為 Null, 否則為 0. The default in this case is type dependent. TIMESTAMP has a default of 0 unless defined with the NULL attribute, in which case the default is NULL. CREATE TABLE t1 ( ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0 ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL ); DATETIME 欄位預設值為 NULL, 若不允許 null, 則為 0. DATETIME has a default of NULL unless defined with the NOT NULL attribute, in which case the default is 0. CREATE TABLE t1 ( dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0 ); ---------- 舊版才有這問題, 新版增加了 ON UPDATE CURRENT_TIMESTAMP 以解決此問題 https://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP); **CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I hope this helps. I am using 5.5.56-MariaDB ** 注意 加了預設值 CURRENT_TIMESTAMP 以後, 執行 update 就一定會更新為最新時間. 若要保留欄位原值, 就要在更新時, 加入更新 "[欄位] = [欄位]" 才不會更新為最新時間. ---------- 20200113 備份還原 參考: 備份還原.txt 以 MySQL Workbench 6.3 CE Community 備份: 1. 選擇 Server.DataExport 2. 選擇 Schema (在Tables to Export中勾選) 3. 選擇 (Dump Structure and Data + Include Create Schema), 選擇Export to Self-Contained File(例如: C:\Users\uGPTT1\Documents\dumps\Dump20180524.sql) 4. 按 Start Export 按鍵. 執行結果 Export Progress: 11:20:24 Dumping morebuy (all tables) Running: mysqldump.exe --defaults-file="c:\users\ugptt1\appdata\local\temp\tmp7npyn0.cnf" --user=morebuy --host=223.27.34.248 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "morebuy" 11:20:28 Export of C:\Users\uGPTT1\Documents\dumps\Dump20180524.sql has finished 還原到本機: 1. 如果有相同的 schema 名稱的資料庫, 先刪除. 2. 選擇 Server.Data Import 3. 選擇 Import from Self-Contained File: 例如: C:\Users\uGPTT1\Documents\dumps\Dump20180524.sql 4. 輸入: MoreBuy, 於 Default Schema to be Imported To中. 還原的資料庫名稱仍以(備份的schema中資料為準) 5. 4. 按 Start Import 按鍵. 執行結果: Creating schema MoreBuy 11:42:24 Restoring C:\Users\uGPTT1\Documents\dumps\Dump20180524.sql Running: mysql.exe --defaults-file="c:\users\ugptt1\appdata\local\temp\tmpluygln.cnf" --protocol=tcp --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\uGPTT1\\Documents\\dumps\\Dump20180524.sql" 11:42:44 Import of C:\Users\uGPTT1\Documents\dumps\Dump20180524.sql has finished 使用Workbench.Server.DataExport, 選擇(Dump Structure and Data + Include Create Schema), 執行結果如下: 選擇(Dump Structure and Data + Include Create Schema)執行結果如下 10:32:35 Dumping morebuy (all tables) Running: mysqldump.exe --defaults-file="c:\users\ugptt1\appdata\local\temp\tmplir1gv.cnf" --user=morebuy --host=223.27.34.248 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "morebuy" 10:32:38 Export of C:\Users\uGPTT1\Documents\dumps\Dump20180426-2.sql has finished 選擇(Dump Structure and Data + Include Create Schema)執行結果如下 10:04:50 Dumping morebuy (all tables) Running: mysqldump.exe --defaults-file="c:\users\ugptt1\appdata\local\temp\tmpxl8aug.cnf" --user=morebuy --host=223.27.34.248 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events --no-create-info=TRUE "morebuy" 10:04:54 Export of C:\Users\uGPTT1\Documents\dumps\Dump20180426.sql has finished 還原執行結果如下: 10:36:31 Restoring C:\Users\uGPTT1\Documents\dumps\Dump20180426-MoreBuy223.27.34.248-2.sql Running: mysql.exe --defaults-file="c:\users\ugptt1\appdata\local\temp\tmplxiebe.cnf" --protocol=tcp --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments < "C:\\Users\\uGPTT1\\Documents\\dumps\\Dump20180426-MoreBuy223.27.34.248-2.sql" 10:36:48 Import of C:\Users\uGPTT1\Documents\dumps\Dump20180426-MoreBuy223.27.34.248-2.sql has finished ---------- 20190311 -- CreateDBAspNet1-MySQL.sql -- 20190311, 011netservice@gmail.com USE `dbaspnet1`; DROP TABLE IF EXISTS `AspNetUserRoles` ; DROP TABLE IF EXISTS `AspNetUserLogins` ; DROP TABLE IF EXISTS `AspNetUserClaims` ; DROP TABLE IF EXISTS `AspNetUsers` ; DROP TABLE IF EXISTS `AspNetRoles` ; CREATE TABLE `AspNetRoles` ( `Id` varchar(128) NOT NULL, `Name` varchar(256) NOT NULL, PRIMARY KEY (`Id`) ); CREATE TABLE `AspNetUsers` ( `Id` varchar(128) NOT NULL, `Email` varchar(256) DEFAULT NULL, `EmailConfirmed` tinyint(1) NOT NULL, `PasswordHash` longtext, `SecurityStamp` longtext, `PhoneNumber` longtext, `PhoneNumberConfirmed` tinyint(1) NOT NULL, `TwoFactorEnabled` tinyint(1) NOT NULL, `LockoutEndDateUtc` datetime DEFAULT NULL, `LockoutEnabled` tinyint(1) NOT NULL, `AccessFailedCount` int(11) NOT NULL, `UserName` varchar(256) NOT NULL, PRIMARY KEY (`Id`) ); CREATE TABLE `AspNetUserClaims` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `UserId` varchar(128) NOT NULL, `ClaimType` longtext, `ClaimValue` longtext, PRIMARY KEY (`Id`), UNIQUE KEY `Id` (`Id`), KEY `UserId` (`UserId`), CONSTRAINT `ApplicationUser_Claims` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION ); CREATE TABLE `AspNetUserLogins` ( `LoginProvider` varchar(128) NOT NULL, `ProviderKey` varchar(128) NOT NULL, `UserId` varchar(128) NOT NULL, PRIMARY KEY (`LoginProvider`,`ProviderKey`,`UserId`), KEY `ApplicationUser_Logins` (`UserId`), CONSTRAINT `ApplicationUser_Logins` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION ); CREATE TABLE `AspNetUserRoles` ( `UserId` varchar(128) NOT NULL, `RoleId` varchar(128) NOT NULL, PRIMARY KEY (`UserId`,`RoleId`), KEY `IdentityRole_Users` (`RoleId`), CONSTRAINT `ApplicationUser_Roles` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `IdentityRole_Users` FOREIGN KEY (`RoleId`) REFERENCES `AspNetRoles` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ; -- Test data INSERT INTO `AspNetUsers` (`Id`, `Email`, `EmailConfirmed`, `PasswordHash`, `SecurityStamp`, `PhoneNumber`, `PhoneNumberConfirmed`, `TwoFactorEnabled`, `LockoutEndDateUtc`, `LockoutEnabled`, `AccessFailedCount`, `UserName`) VALUES ('b709c15b-f805-44af-8237-1be89b4222a3', 'test1@some.com', 0, 'AJXCcIMNdksqvJMyLPleatRpak91jG4CW7sWYp46n7nUmAYIq053HblHTsuKWf+dgQ==', 'b190cd2c-54f9-49f1-9e05-5dcd6d3e5f0a', NULL, 0, 0, NULL, 1, 0, 'test1@some.com')