MySQL：修訂版本之間的差異

出自福留子孫
跳轉到： 導覽、 搜尋
（→‎utf8mb4）
（→‎utf8mb4）
第 185 行： 第 185 行：
 
  collation-server = utf8mb4_unicode_ci
 
  collation-server = utf8mb4_unicode_ci
 
就是預設使用 utf8mb4 字集，不然就預設使用 utf8mb3 字集。
 
就是預設使用 utf8mb4 字集，不然就預設使用 utf8mb3 字集。
 +
 +
 +
將整個資料庫預設為 utf8mb4 字集：
 +
ALTER DATABASE 資料庫名 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
 +
將整張資料表預設為 utf8mb4 字集：
 +
ALTER TABLE 資料表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 +
將某欄改為 utf8mb4 字集：
 +
ALTER TABLE 資料表名 CHANGE 原欄名 新欄名 column_type CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2024年1月23日 (二) 15:12的修訂版本

設置語法

如 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';

跨伺服器叫用 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：不合法的值會導致整個 SQL 指令出錯中止
    • 不可對 not null 欄位插入 null 值
    • 不可對自動遞增欄位插入值
    • text 欄位不可以有預設值
  2. ERROR_FOR_DIVISION_BY_ZERO：在 insert 或 update 時，若數據被零除，則產生錯誤而非警告。若未設此值，則數據被零除時 MySQL 返回 NULL
  3. NO_AUTO_CREATE_USER：不自動創建用戶，必須先建立用戶才能授權
  4. NO_ENGINE_SUBSTITUTION：若存儲引擎被禁用或未編譯，此項功能會直接拋出錯誤；若未設此值， create 用預設的存儲引擎替代， alter 不進行更改，並拋出一個 warning。
    -----更多的嚴格模式值-----
  5. NO_ZER0_DATE：日期「0000-00-00」的值不合法
  6. NO_ZERO_IN_DATE：日期「2010-01-00」的值不合法，因為日期不可為 0
  7. 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

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;

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

ALTER TABLE 資料表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

將某欄改為 utf8mb4 字集： 

ALTER TABLE 資料表名 CHANGE 原欄名 新欄名 column_type CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
取自「http://well-being-ng.net/wellBeingNg/index.php?title=MySQL&oldid=17657」