MySQL：修訂版本之間的差異

出自福留子孫
跳轉到： 導覽、 搜尋
（→‎utf8mb4）
（→‎在設定嚴格模式下）
 
（未顯示同用戶所作出之7次版本）
第 113 行： 第 113 行：
 
#use 資料庫;
 
#use 資料庫;
 
#load data local infile '/volume1/web/大型資料.csv' into table 資料表名 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
 
#load data local infile '/volume1/web/大型資料.csv' into table 資料表名 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
 +
 +
==匯入含有自動遞增欄位的 csv 檔==
 +
===在過去未設定嚴格模式的時代===
 +
#只要 csv 中該欄的位置沒有任何值，表示為「,,」
 +
#不管是 Adminer 或 phpMyAdmin 都會把 csv 檔匯入解譯為「INSERT INTO `表格名` VALUES (…,'', …);
 +
#非嚴格模式下「,'',」遇到自動遞增欄會視為 NULL ，而讓自動遞增賦值。
 +
 +
===在設定嚴格模式下===
 +
透過「SELECT @@sql_mode;」我們可以知道常見的嚴格模式會設定：
 +
#STRICT_TRANS_TABLES：支持交易型的表(如 InnoDB)數據操作須嚴格符合欄位定義
 +
#ERROR_FOR_DIVISION_BY_ZERO：在 insert 或 update 時，若數據不可被零除
 +
#NO_AUTO_CREATE_USER：不自動創建用戶，必須先建立用戶才能授權
 +
#NO_ENGINE_SUBSTITUTION：create 不可用預設的存儲引擎替代
 +
 +
這導致以下三條路要匯入 csv 均會失敗：
 +
 +
第一：在資料表中直接使用匯入 csv 功能，會被嚴格模式擋住。
 +
 +
第二，在 Adminer 或 phpMyAdmin 下 SQL 指令：
 +
<pre>LOAD DATA INFILE 'file_path.csv'
 +
INTO TABLE your_table_name
 +
FIELDS TERMINATED BY ','
 +
OPTIONALLY ENCLOSED BY '"'
 +
LINES TERMINATED BY '\n';</pre>
 +
又會碰到「Errcode: 13 "Permission denied"」，要設到檔案可讀取，一般沒那麼容易
 +
 +
第三，用「SET @@sql_mode = '…'」抑制嚴格模式，只對某一個會期內有效，命令一執行完會期就結束了。也就是能用於 php 中但無法用於 adminer 或 phpMyAdmin 匯入 csv 檔，因為「SET @@sql_mode = '…'」和「匯入 csv 檔」分屬於兩個不同會期。
 +
 +
'''正確的解法'''是：
 +
#在 csv 檔中剔除自動遞增欄位
 +
#用 phpMyAdmin 不要用 adminer
 +
#phpMyAdmin 中先選用資料表，再匯入 csv 時，指定欄位名稱
  
 
==跨伺服器叫用 MySQL==
 
==跨伺服器叫用 MySQL==
第 149 行： 第 181 行：
 
DB::connect("mysql://帳號:密碼@被叫用伺服器:3306/資料庫名");
 
DB::connect("mysql://帳號:密碼@被叫用伺服器:3306/資料庫名");
  
−
==資料庫設定為「嚴格模式」==
+
==資料庫被設定為「嚴格模式」==
 
「嚴格模式」(Strict Mode)下：
 
「嚴格模式」(Strict Mode)下：
 
#欄位若沒有預設值則不能插入
 
#欄位若沒有預設值則不能插入
 
#BLOB/TEXT 欄不能設預設值
 
#BLOB/TEXT 欄不能設預設值
 
MariaDB 10 或 MySQL 5.7.8 版本裡預設了「sql_mode」幾項功能，可以用 adminer 登入，然後查伺服器的「變數」，其中「sql_mode」的值包含：
 
MariaDB 10 或 MySQL 5.7.8 版本裡預設了「sql_mode」幾項功能，可以用 adminer 登入，然後查伺服器的「變數」，其中「sql_mode」的值包含：
−
#STRICT_TRANS_TABLES：不合法的值會導致整個 SQL 指令出錯中止
+
#STRICT_TRANS_TABLES：遇不合法的值，對支持交易(Transaction)的表(如 InnoDB)報錯並中止執行；對於非交易型的表(如 MyISAM)，報警告並不中止執行：
 
#*不可對 not null 欄位插入 null 值
 
#*不可對 not null 欄位插入 null 值
 
#*不可對自動遞增欄位插入值
 
#*不可對自動遞增欄位插入值
 
#*text 欄位不可以有預設值
 
#*text 欄位不可以有預設值
 +
#*VARCHAR 欄位不可以插入過長的字串
 +
#STRICT_ALL_TABLES：嚴格管制所有的表。數據操作若不符合欄位定義，報錯並中止執行。
 
#ERROR_FOR_DIVISION_BY_ZERO：在 insert 或 update 時，若數據被零除，則產生錯誤而非警告。若未設此值，則數據被零除時 MySQL 返回 NULL
 
#ERROR_FOR_DIVISION_BY_ZERO：在 insert 或 update 時，若數據被零除，則產生錯誤而非警告。若未設此值，則數據被零除時 MySQL 返回 NULL
 
#NO_AUTO_CREATE_USER：不自動創建用戶，必須先建立用戶才能授權
 
#NO_AUTO_CREATE_USER：不自動創建用戶，必須先建立用戶才能授權
第 217 行： 第 251 行：
 
  SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
 
  SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
 
*「INSERT INTO」及「update」即可送入含 utf8mb4 的字。
 
*「INSERT INTO」及「update」即可送入含 utf8mb4 的字。
 +
 +
==待加入的指令==
 +
* SELECT * FROM JJline ORDER BY BINARY lineName
 +
* SELECT id,sum(UNIX_TIMESTAMP(`end`)-UNIX_TIMESTAMP(start))/3600 FROM calendar WHERE user_name LIKE '%采邑%' AND `type` = '實習'

2024年5月18日 (六) 11:37的最新修訂版本

設置語法

如 my.cnf 中的基本語法：

  1. 註解：
    • 「#」該行後面的所有文字都是註解。
    • 「;」該行後面的所有文字都是註解。
    • 「/* … */」，「/*」表示註解開始，「*/」表示註解結束，之間的所有文字都是註解。
  2. 行首的「!」代表「否定」或「關閉」的意思。當在設定檔中加入 ! 符號時，代表該設定項目會被關閉或禁用。
  3. 左右中括號 [ ] 包裹英文字串代表該字串是一個節點 (Section) 的名稱，可以用來將相關的設定項目分組。使用節點名稱可以使設定檔更具有組織性，也方便閱讀和管理。
    • 有些節點名稱是預設的，例如 [mysqld] 和 [mysqld_safe] 。
    • 節點名稱也可以自定義。

常見的設定項：

[mysqld]之下：
  1. port=3306
  2. datadir=/var/lib/mysql
[mysqld_safe]之下：
  1. log-error=/var/log/mysqld.log
  2. pid-file=/var/run/mysqld/mysqld.pid
其他：
  1. plugin-load=plugin_name.so：載入名為 plugin_name.so 的插件。
    載入之後要不要用分：不用 OFF、使用 ON(預設)、強制使用 FORCE、永遠強制使用 FORCE_PLUS_PERMANENT (即使是資料庫 root 帳號也無法刪除或禁用它) 四種情形，設定如：
    validate-password=FORCE_PLUS_PERMANENT
  2. plugin_load_add = synology_password_check：加載名為 synology_password_check 的插件。

新舊版更迭

一、MySQL Improved extension(MySQLi)

本來 PHP 只提供 MySQL Extension 來連接 MySQL 資料庫，PHP 5.0.0 引入了 mysqli，也在 PHP 5.3 時將 MySQL 原生啟動器(Native Driver) 包含進 PHP，到了 PHP 5.5 棄用 mysql extension，PHP 7.0 就將 mysql extension 移除了，mysql extension 是提供函數 API 支持程序式開發，而 mysqli 是有函數 API 也有提供「物件導向」的介面支持「物件導向」程式開發

mysqli函式

二、mysql 庫 user資料表

MariaDB10 相較於 MariaDB5 與 MariaDB：

  1. 三者的密碼 Hash 算法一樣，得出的字串一樣，資料表存的是 Hashed 之後的字串
  2. MariaDB 無 authentication_string 欄，MariaDB5 與 MariaDB10 的 plugin,authentication_string 兩欄的規則如下：
    • plugin 欄為空字串，則 authentication_string 欄內容亦為空字串，Hashed 後的密碼放 Password 欄
    • plugin 欄值為「mysql_native_password」，則 Hashed 後的密碼放 authentication_string 欄， Password 欄可為空字串。

三、密碼複雜度策略設定

非原生支援，須使用外掛：

一般DSM
外掛名稱validate_passwordsynology_password_check
adminer檢查找「MySQL / 伺服器 / 變數」
檢查命令show variables like 'validate%';show variables like 'synology%';
檢查資料表information_schema.PLUGINS表的PLUGIN_NAME欄
同庫ALL_PLUGINS表是放所有可安裝的外掛
安裝命令INSTALL PLUGIN validate_password SONAME 'validate_password.so';或
INSTALL PLUGIN validate_password SONAME 'synology_password_check.so';

(一)validate_password 的密碼強度相關引數：

  1. validate_password_length：密碼的最小長度，預設值是 8 。
  2. validate_password_policy：密碼策略，預設是 MEDIUM (1)，代表除了需符合密碼長度，還要至少有1個數字、小寫字母、大寫字母和特殊字元。若配置為 LOW (0) ，代表僅需符合密碼長度。若配置為 STRONG ，除了滿足 MEDIUM 策略，同時密碼不能存在字典檔案（dictionary file）中。
  3. validate_password_dictionary_file：字典檔案中存在的密碼不得使用。
  4. validate_password_mixed_case_count：密碼策略設為中或強時，密碼中至少同時擁有小寫字母和大寫字母的數量，預設是1最小是0；預設是至少擁有一個小寫和一個大寫字母。
  5. validate_password_number_count：密碼策略設為中或強時，密碼中至少擁有的數字的個數，預設1最小是0。
  6. validate_password_special_char_count：密碼策略設為中或強時，密碼中至少擁有的特殊字元的個數，預設1最小是0。

啟用方法1，在 my.cnf 中加入設置項目，範例如下： 

plugin-load = validate_password.so
validate_password_length = 10
validate_password_policy = 1
validate-password = FORCE_PLUS_PERMANENT

啟用方法2：用 SQL 命令安裝 

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

啟用方法3：啟動 MySQL 時使用参數 

/usr/local/mariadb10/bin/mysqld start --plugin-load='validate_password.so'

(二)synology_password_check 的密碼強度要求：

  1. 所有密碼符合最短密碼長度 10
  2. 包含混合大小寫字母、數字以及特殊符號
  3. 避免使用常用密碼
  4. 不准將使用者帳號作為密碼

synology_password_check 的設定與使用比較簡單，沒有相關引數。

四、設定密碼自動過期

本段是闡述密碼過期，不是帳號過期。

(一)針對單一帳號設定密碼過期

mysql.user 表中 password_expired 欄：欄位值為 Y 時，代表此密碼已過期，使用過期密碼仍可以登入，但不能進行任何操作。

SQL 設置密碼過期命令如下：

  1. ALTER USER 'expuser'@'%' PASSWORD EXPIRE;：設定 Host 為 % ，帳號為 expuser 的密碼過期
  2. ALTER USER 'expuser'@'%' PASSWORD EXPIRE NEVER;：此帳號密碼永不過期
  3. ALTER USER 'expuser'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;：此帳號密碼90天過期
  4. ALTER USER 'expuser'@'%' PASSWORD EXPIRE DEFAULT;：此帳號使用預設的密碼過期全域性策略

(二)設定全域性過期策略

SQL 設置命令如下：

SET GLOBAL default_password_lifetime = 90;：所有帳號密碼90天過期， 90 的單位是天。

加入配置檔 my.cnf ： 

[mysqld]
default_password_lifetime = 90

匯入大型的 csv 檔

當 csv 檔筆數極大時，便無法用 adminer 匯入，原因始下：

  1. 用光了伺服器允許的最大記憶體內存，此時伺服器會回應瀏覽「Allowed memory size of xxxxxxxxx bytes exhausted (tried to allocate xxxxxxx bytes) in /volume1/web/adminer-4.8.1-mysql.php on line xxx」
  2. 此時改用「load data local infile '/volume1/web/大型資料.csv' into table 資料表」，又會被伺服器回應「LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile」
    此時即使從「DSM / Web Station / 指令碼語言設定 / PHP / 自訂設定檔 / 選用某一組使用者定義 / 編輯 / 核心設定 / mysqli.allow_local_infile / 將值由 0 改 1 / 儲存」，並重啟網頁伺服器回應也一樣。

真正的匯入辦法：

  1. 以 putty 登入伺服器，並取得 root 權限。
  2. cd /volume1/@appstore/MariaDB10/usr/local/mariadb10/bin
  3. mysql -u 帳號 -p密碼：進入 mysql 的命令介面。-p 之後沒有空白，直接接密碼。
  4. use 資料庫;
  5. load data local infile '/volume1/web/大型資料.csv' into table 資料表名 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

匯入含有自動遞增欄位的 csv 檔

在過去未設定嚴格模式的時代

  1. 只要 csv 中該欄的位置沒有任何值，表示為「,,」
  2. 不管是 Adminer 或 phpMyAdmin 都會把 csv 檔匯入解譯為「INSERT INTO `表格名` VALUES (…,, …);
  3. 非嚴格模式下「,,」遇到自動遞增欄會視為 NULL ，而讓自動遞增賦值。

在設定嚴格模式下

透過「SELECT @@sql_mode;」我們可以知道常見的嚴格模式會設定：

  1. STRICT_TRANS_TABLES：支持交易型的表(如 InnoDB)數據操作須嚴格符合欄位定義
  2. ERROR_FOR_DIVISION_BY_ZERO：在 insert 或 update 時，若數據不可被零除
  3. NO_AUTO_CREATE_USER：不自動創建用戶，必須先建立用戶才能授權
  4. NO_ENGINE_SUBSTITUTION：create 不可用預設的存儲引擎替代

這導致以下三條路要匯入 csv 均會失敗：

第一：在資料表中直接使用匯入 csv 功能，會被嚴格模式擋住。

第二，在 Adminer 或 phpMyAdmin 下 SQL 指令： 

LOAD DATA INFILE 'file_path.csv'
INTO TABLE your_table_name
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

又會碰到「Errcode: 13 "Permission denied"」，要設到檔案可讀取，一般沒那麼容易

第三，用「SET @@sql_mode = '…'」抑制嚴格模式，只對某一個會期內有效，命令一執行完會期就結束了。也就是能用於 php 中但無法用於 adminer 或 phpMyAdmin 匯入 csv 檔，因為「SET @@sql_mode = '…'」和「匯入 csv 檔」分屬於兩個不同會期。

正確的解法是：

  1. 在 csv 檔中剔除自動遞增欄位
  2. 用 phpMyAdmin 不要用 adminer
  3. phpMyAdmin 中先選用資料表，再匯入 csv 時，指定欄位名稱

跨伺服器叫用 MySQL

被叫用端設定

  1. 設定一個帳號：
    • HOST 為允許叫用的 ip ，不能用域名或 server 網址。所有 ip 登入，Host設置為 '%' 。
    • 設定帳號密碼，並以最保守的立場設定對諸資料表的權限。
    • flush privileges;
  2. /etc/my.cnf中的相關設定：
    • [mysqld]內新增一行:skip-name-resolve，關閉 MySQL DNS 反向解析。但伺服器會把在本機登入的使用者自動解析為'root'@'127.0.0.1';而不是'root'@'localhost';，如果權限表不合就會出問題。
    • [mysqld]內新增一行:lower_case_table_names=1，使MySQL忽略資料庫表名大小寫
  3. 打開 3306 port

戰國策租賃主機不允許外部叫用內部的 MySQL 。？但資料庫伺服器明明和網頁伺服器分開啊？

檢測被叫用的伺服器

  1. 網路檢測：
    • ping主機可以；
    • telnet 主機3306端口不可以；
    • telnet 主機22端口可以；
    跟伺服器沒關係
  2. 端口檢測：
    • netstat -ntpl |grep 3306 得到 「tcp 0 0 :::3306 :::* LISTEN -」
    • netstat -ntpl |grep 22 得到 「tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -」
    22端口監聽所有地址，而3306只監聽本機地址(綁定了到了本地)，所以遠程無法訪問。修改my.cnf 中bind-address=0.0.0.0
    對於端口只允許本機訪問，有兩個相關設定，一個是防火牆擋3306，一個就是mysql配置綁定本機地址。
  3. 防火牆檢測：
    • iptables --list查看；
    • 開啟防火牆3306端口：在 iptables 中加「-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT」，防火牆允許3306端口通過。
    • 或者直接關閉防火牆；
  4. mysql配置文件檢查：
    • my.cnf的配置，bind-address=addr可以配置綁定ip地址。不配置或者IP配置為0.0.0.0，表示監聽所有客戶端連接。
      1. ps -aux | grep mysql 查看進程ID是3340
      2. ll /proc/3340 查看進程程序情況，找配置文件
    • 或以 which mysql 找程序路徑

叫用端設定

DB::connect("mysql://帳號:密碼@被叫用伺服器:3306/資料庫名");

資料庫被設定為「嚴格模式」

「嚴格模式」(Strict Mode)下：

  1. 欄位若沒有預設值則不能插入
  2. BLOB/TEXT 欄不能設預設值

MariaDB 10 或 MySQL 5.7.8 版本裡預設了「sql_mode」幾項功能，可以用 adminer 登入，然後查伺服器的「變數」，其中「sql_mode」的值包含：

  1. STRICT_TRANS_TABLES：遇不合法的值，對支持交易(Transaction)的表(如 InnoDB)報錯並中止執行；對於非交易型的表(如 MyISAM)，報警告並不中止執行：
    • 不可對 not null 欄位插入 null 值
    • 不可對自動遞增欄位插入值
    • text 欄位不可以有預設值
    • VARCHAR 欄位不可以插入過長的字串
  2. STRICT_ALL_TABLES：嚴格管制所有的表。數據操作若不符合欄位定義，報錯並中止執行。
  3. ERROR_FOR_DIVISION_BY_ZERO：在 insert 或 update 時，若數據被零除，則產生錯誤而非警告。若未設此值，則數據被零除時 MySQL 返回 NULL
  4. NO_AUTO_CREATE_USER：不自動創建用戶，必須先建立用戶才能授權
  5. NO_ENGINE_SUBSTITUTION：若存儲引擎被禁用或未編譯，此項功能會直接拋出錯誤；若未設此值， create 用預設的存儲引擎替代， alter 不進行更改，並拋出一個 warning。
    -----更多的嚴格模式值-----
  6. NO_ZER0_DATE：日期「0000-00-00」的值不合法
  7. NO_ZERO_IN_DATE：日期「2010-01-00」的值不合法，因為日期不可為 0
  8. ONLY_FULL_GROUP_BY：若 select 中的欄、HAVING 或者 ORDER BY 中的欄，沒有在 group by 中出現，則此 SQL 不合法

解決方法：

  1. PHP 連結資料庫一成功，馬上送入「SET SESSION sql_mode=''」，在本次會期中取消嚴格模式
  2. 或將語法寫成：「SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'」保留部分嚴格模式

資料來源：

  1. DB_MySQL:SQL-Mode的設定
  2. MySQL 5.7：聊聊sql_mode
  3. sql-mode
  4. Mysql ERROR 1067: Invalid default value for 'date' 解決

utf8mb4

在 MySQL 中，常見的字符集包括 utf8, utf8mb4, latin1 等。例如，utf8mb4 可以存儲任何 Unicode 字符，而 latin1 則主要用於西歐語言。

utf8mb3 是 MySQL 中 utf8 的舊稱，這種格式的字符集在 MySQL 中只能存儲最多 3 個 byte 的 UTF-8 字符。

utf8mb4 字符集則能存儲最多 4 個 byte 的 UTF-8 字符，包括一些 emoji (繪文字) 表情符號。

在 my.cnf 中有設定： 

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

就是預設使用 utf8mb4 字集，不然就預設使用 utf8mb3 字集。


將整個資料庫預設為 utf8mb4 字集： 

ALTER DATABASE 資料庫名 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
或
CREATE DATABASE 資料庫名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

將整張資料表預設為 utf8mb4 字集： 

ALTER TABLE 資料表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
或
CREATE TABLE 資料表名 (…欄名 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,…)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

將某欄改為 utf8mb4 字集： 

ALTER TABLE 資料表名 MODIFY COLUMN 欄名 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

PS. VARCHAR(255) 的欄若用作索引，請注意 utf8mb4 字符需要更多的空間，這可能會超出索引大小的限制。在這種情況下，你可能需要調整欄的大小或索引的大小。

校對方式(Collation)

校對方式是字符集的一種設定，它定義了如何比較和排序字符。例如，utf8mb4_unicode_ci 是一種校對方式，其中 ci 代表 "case insensitive"(不區分大小寫)，這意味著在排序和比較時，字母 'A' 和 'a' 被視為相等。

校對方式影響著 SQL 查詢中的 ORDER BY 和 WHERE 等子句的行為。

在 MySQL 的校對方式中，unicode_ci 和 general_ci 都是不區分大小寫的校對方式，但：

  1. unicode_ci：依據 Unicode 標準來進行字符的比較和排序。能準確地處理國際語言中的特殊字符。
  2. general_ci：是一種更簡單、更快速的校對方式，在處理某些國際字符時可能不如 unicode_ci 那麼精確，但由於其比較算法的簡單性，general_ci 在性能上通常比 unicode_ci 更快。

將 utf8mb4 字元存入資料表中

  • 先確認你要存入欄位是否支持援 utf8mb4 。 
SHOW CREATE TABLE 資料表名
  • 確保你的 MySQL 客戶端和連線設定支援 UTF8MB4 
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
  • 「INSERT INTO」及「update」即可送入含 utf8mb4 的字。

待加入的指令

  • SELECT * FROM JJline ORDER BY BINARY lineName
  • SELECT id,sum(UNIX_TIMESTAMP(`end`)-UNIX_TIMESTAMP(start))/3600 FROM calendar WHERE user_name LIKE '%采邑%' AND `type` = '實習'
取自「http://jendo.org/wellBeingNg/index.php?title=MySQL&oldid=18962」